Docstoc

fm

Document Sample
fm Powered By Docstoc
					                    Sheet Tab                          Description
     1   Table of Contents
     2   Gemeral Structure
     3   TOC Notes
     4   Shortcuts List           Excel list of short cuts by function
     5   CNTL Shortcuts           Short-cut keys sorted by CNTL function
     6   Shortcut Table           Short-cut keys that work together
     7   Colour Inputs            Macro for creating input colours and finding cells
     8   Create Linked Colours    Macro for colouring sheets from the source sheet
     9   Colour List              List of colours and numbers
    10   Limit Sheet Size         Macro that limits the size of the sheet and comments
    11   Delete Sheets            Macro that deletes sheets from given names
    12   Print Macro              Macro to print parts of a workbook
    13   Remove Bad Range Names   Macro to remove REF or linked range names
    14   Make Disclaimer Page     Macro to add disclaimer page to model
    15   Alt Disclaimer Page      Alternative way to make disclaimer page
    16   Size Delete Charts       Macro to re-size and/or to delete graphs
    17   Format Download          Macro that formats downloads from web
    18   Delete Blank Rows        Macro that deletes blank rows or columns
    19   Chart4                   Chart used for example
    20   Show Graph               Macro to expand the size of graph
    21   Show Notes               Macro to add documentation to model
    22   Move Sheets              Move Sheets into different file (stock prices)
    23   Misc Data                Misc data for macros
    24   Payback Function         Fucntion to compute payback and discouted payback
    25   Error Function           Function that shows error as blank or zero etc.
    26   Interpolate              Function that interpolates numbers
    27   Column Functions         Functions that define the name of a column
    28   IRR Fix                  Function that fixes IRR problems
    29   Show Formulas            Function that shows formulas (also macro)
    30   Volatility               Fucntion that computes volatility
    31   Subtotal                 Macro that fixes problems with subtotal
    32   Two Way Data Table       Macro that fixes two-way data table
    33   One Way Data Table       Macro that fixes one-way data table
    34   Goal Seek                Macro that fixes goal seek
    35   Find Strings             Excel to find strings
    36   Sheet Name               Excel to put sheet name in the sheet
    37   Dates and Indirect       Create Annual Statements from Periodic Analysis
    38   Sheet10
    38   range_names

File Name:                        C:\A Files\Courses\Course Materials\22 Excel Utilities (fm)\fm.xls

Colour Codes

Date                                                                           21-Feb-09
                            Go to these spreadsheets to see how to use various short-cuts. There are various different
                            ways to read the short-cut keys in the different pages.


                            These sheets contain various macros that may be useful in your spreadsheet. To operate the
                            macros, you can use the ALT-F8.

                            You can also go the the sheets and find the macros, right click on the button and go to the edit
                            box. Then, you go to the code and copy the code into a new macro.

                            Each sheet has a description of how to copy the macros.

                            When the macro uses a form, you should go to the project manager in VBA (don't worry).
                            Then, just like you would copy a file from one folder to anouter in windows, you must copy the
                            form to the sheet where you will put the form.




                            This set of sheets has various functions that you can add to your sheets.

                            Each sheet has examples of how to use the functions.

                            To copy the functions, open the functions program and copy all of the functions to the sheet.



                            This set of sheets has macros that fix problems in Excel. These problems included data tables
                            that read from another sheet and the goal seek that needs an input variable.


                            This set of sheets has miscellaneous sheets that have various excel functions that are not
                            often used for working with text.




cel Utilities (fm)\fm.xls
rious different



  To operate the


and go to the edit




on't worry).
u must copy the




s to the sheet.



uded data tables



 that are not
Table of Contents
                                       General Discussion of File
                                              The general way to use this file is to go to the sheet which may be interesting to y
                                              or function is interesting to you, you can look at the macro, and try to implement th




   Objectives of Workbook
                     The general objective of this workbook is to provide you with various marcros and functions
                     you to put a table of contents in any file and the data table macros allow you to add much more flexible da
                     different sheets and work with macros. The functions in the file such as the payback function allow you to
                     function. A second objective of the file is to give you the code to macros and functions
                     lists and a few general functions in excel such as the CELL function that are documented.



   Ways to Use the File
                    There are three general ways to use the file and operate the various macros in your worksheets. The first
                    the macros in this file and allow to run then from another sheet. This does not work with functions unfortun
                    To do this, make sure that you have at least one macro in your file. The select the functions or macros tha
                    this file is to copy the file into your personal folder. If you do this, then all of the macros will be available to
                    to other people. Refer to the excel background file on macros to see how to create a personal file.



   Potential Problems with the File
                    Sometimes when you try to run a macro or even open the file, there a debug error occurs. To fix this error,
                    file. If there is any box clicked that has an error such as MISSING… uncheck this error box. The file also
eet which may be interesting to you and then try out the function of the macro or the other instructions. If the macro
he macro, and try to implement the macro or function in your file.




marcros and functions that may help you with the file. For example, the create table of contents function allows
 you to add much more flexible data tables than are available in excel -- these data tables can accept inputs from
 he payback function allow you to perform functions that are not available in excel such as the payback period
os and functions so that you can try to program some of the various functions yourself. Finally, there are short-cut
 are documented.




 os in your worksheets. The first is simply to have this file open and then press the ALT-F8 key. This will list all of
s not work with functions unfortunately. To put the functions into your file, you can copy the functions into your file.
 elect the functions or macros that you want to copy and copy the functions into your file. The final method to use
of the macros will be available to any file you use. The problem with this method is that you cannot give your files
 to create a personal file.




 ug error occurs. To fix this error, exit all of the macros if you can. Then go to the TOOLS and REFERENCES
  eck this error box. The file also has problems if it is opened in early versions of excel such as excel 2000.
Table of Contents

To Create Table of Contents                              Results of pressing the Alt-F8 key
                                                         are shown on the diagram
   Press ALT F8 to List Macros
   Run the Create Table of Contents Macro

To Delete Table of Contents

   Shade the entire sheet with CNTL A
   Clear all cells with CNTL -


To Create a cell that shows the location of the file

   The Sheet Name is created with the Cell("filename")

    =CELL("filename")-->       C:\Docstoc\Working\pdf\[e6793508-28c6-4686-aa5e-37f7156a459f.xls]Make Disclaimer Page
xls]Make Disclaimer Page

                           range_names
Table of Contents

    Keyboard shortcuts
                                                                                                   It is good to try to pr
                                                                                                   -

Work with worksheets
    SHIFT+F11 or ALT+SHIFT+F1                                    Better than using Insert sheet and then F4. Use AL
         Insert a new worksheet.
    CTRL+PAGE DOWN                                               This is good for moving between pages
         Move to the next sheet in the workbook.
    CTRL+PAGE UP
         Move to the previous sheet in the workbook.
    SHIFT+CTRL+PAGE DOWN
         Select the current and next sheet. To cancel selection of multiple sheets, press CTRL+PAGE DOWN or,
    SHIFT+CTRL+PAGE UP
         Select the current and previous sheet.
    ALT+O H R
         Rename the current sheet (Format menu, Sheet submenu, Rename command).
    ALT+E M
         Move or copy the current sheet (Edit menu, Move or Copy Sheet command).
    ALT+E L
         Delete the current sheet (Edit menu, Delete Sheet command).

Move and scroll within worksheets
    Arrow keys
          Move one cell up, down, left, or right.
    CTRL+arrow key                                                         This is good with the F8 key and long lis
          Move to the edge of the current data region (data region: A range of cells that contains data and that is bo
    HOME
          Move to the beginning of the row.
    CTRL+HOME
          Move to the beginning of the worksheet.
    CTRL+END
          Move to the last cell on the worksheet, in the bottom-most used row of the rightmost used column.
    PAGE DOWN
          Move down one screen.
    PAGE UP
          Move up one screen.
    ALT+PAGE DOWN
          Move one screen to the right.
  ALT+PAGE UP
       Move one screen to the left.
  F6
       Switch to the next pane in a worksheet that has been split (Window menu, Split command).
  SHIFT+F6
       Switch to the previous pane in a worksheet that has been split.
  CTRL+BACKSPACE
       Scroll to display the active cell.
  F5                                                          This can be used to mark all of the inputs when usi
       Display the Go To dialog box.
  SHIFT+F5
       Display the Find dialog box.
  SHIFT+F4
       Repeat the last Find action (same as Find Next).
  TAB
       Move between unlocked cells on a protected worksheet.
Select cells, rows and columns, and objects (from anywhere in the sheet)
  CTRL+SPACEBAR                                                  This is good for inserting and deleting rows
       Select the entire column.
  SHIFT+SPACEBAR
       Select the entire row.
  CTRL+A                                                         Press a couple of times to get the entire sheet
       Select the entire worksheet.
  SHIFT+BACKSPACE
       With multiple cells selected, select only the active cell.
  CTRL+SHIFT+SPACEBAR                                            Can delete internet stuff
       With an object selected, select all objects on a sheet.
  CTRL+6
       Alternate between hiding objects, displaying objects, and displaying placeholders for objects.

Select cells with specific characteristics
  CTRL+SHIFT+* (asterisk)
      Select the current region around the active cell (the data area enclosed by blank rows and blank columns)
  CTRL+/
      Select the array (array: Used to build single formulas that produce multiple results or that operate on a gro
  CTRL+SHIFT+O (the letter O)
      Select all cells that contain comments.
  CTRL+\
      In a selected row, select the cells that don't match the value in the active cell.
  CTRL+SHIFT+|
       In a selected column, select the cells that don't match the value in the active cell.
  CTRL+[ (opening bracket)
       Select all cells directly referenced by formulas in the selection.
  CTRL+SHIFT+{ (opening brace)
       Select all cells directly or indirectly referenced by formulas in the selection.
  CTRL+] (closing bracket)
       Select cells that contain formulas that directly reference the active cell.
  CTRL+SHIFT+} (closing brace)
       Select cells that contain formulas that directly or indirectly reference the active cell.
  ALT+; (semicolon)
       Select the visible cells in the current selection.

Extend a selection
  F8
       Turn extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend th
  SHIFT+F8
       Add another range of cells to the selection; or use the arrow keys to move to the start of the range you wa
  SHIFT+arrow key
       Extend the selection by one cell.
  CTRL+SHIFT+arrow key                                           Important - Use together with hiding remaining row
       Extend the selection to the last nonblank cell in the same column or row as the active cell.
  SHIFT+HOME
       Extend the selection to the beginning of the row.
  CTRL+SHIFT+HOME
       Extend the selection to the beginning of the worksheet.
  CTRL+SHIFT+END
       Extend the selection to the last used cell on the worksheet (lower-right corner).
  SHIFT+PAGE DOWN
       Extend the selection down one screen.
  SHIFT+PAGE UP
       Extend the selection up one screen.
  END+SHIFT+arrow key
       Extend the selection to the last nonblank cell in the same column or row as the active cell.
  END+SHIFT+HOME
       Extend the selection to the last used cell on the worksheet (lower-right corner).
  END+SHIFT+ENTER
       Extend the selection to the last cell in the current row. This key sequence does not work if you have turne
  SCROLL LOCK+SHIFT+HOME
       Extend the selection to the cell in the upper-left corner of the window.
SCROLL LOCK+SHIFT+END
    Extend the selection to the cell in the lower-right corner of the window.

Keys for entering, editing, formatting, and calculating data

Enter data
ENTER
       Complete a cell entry and select the cell below.
ALT+ENTER
       Start a new line in the same cell.
CTRL+ENTER
       Fill the selected cell range with the current entry.
SHIFT+ENTER
       Complete a cell entry and select the previous cell above.
TAB
       Complete a cell entry and select the next cell to the right.
SHIFT+TAB
       Complete a cell entry and select the previous cell to the left.
ESC
       Cancel a cell entry.
Arrow keys
       Move one character up, down, left, or right.
HOME
       Move to the beginning of the line.
F4 or CTRL+Y
       Repeat the last action.
CTRL+SHIFT+F3
       Create names (name: A word or string of characters that represents a cell, range of cells, formula, or cons
CTRL+D
       Fill down.
CTRL+R                                                          Use with SHIFT, CNTL, --->
       Fill to the right.
CTRL+F3                                                         Same as Insert, Name, Define
       Define a name.
CTRL+K
       Insert a hyperlink (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a loca
CTRL+; (semicolon)                                              Cool -- today's date
       Enter the date.
CTRL+SHIFT+: (colon)
       Enter the time.
ALT+DOWN ARROW
    Display a drop-down list of the values in the current column of a range.
CTRL+Z
    Undo the last action.


Enter special characters
Press F2 to edit the cell, turn on NUM LOCK, and then press the following keys by using the numeric key pad:

ALT+0162
     Enters the cent character ¢.
ALT+0163
     Enters the pound sterling character £.
ALT+0165
     Enters the yen symbol ¥.
ALT+0128
     Enters the euro symbol €.                              1           I cannot get this to work


Enter and calculate formulas

      Start a formula.
F2
     Move the insertion point into the Formula Bar when editing in a cell is turned off.
BACKSPACE
     In the Formula Bar, delete one character to the left.
ENTER
     Complete a cell entry from the cell or Formula Bar.
CTRL+SHIFT+ENTER
     Enter a formula as an array formula (array formula: A formula that performs multiple calculations on one
ESC
     Cancel an entry in the cell or Formula Bar.
SHIFT+F3                                                                This is cool; you must first use the F2 ke
     In a formula, display the Insert Function dialog box.                       0 test_range
CTRL+A                                      $0.00
     When the insertion point is to the right of a function name in a formula, display the Function Argument
CTRL+SHIFT+A
     When the insertion point is to the right of a function name in a formula, insert the argument names and pa
F3
     Paste a defined name (name: A word or string of characters that represents a cell, range of cells, formula,
ALT+= (equal sign)
     Insert an AutoSum formula with the SUM function.
CTRL+SHIFT+" (quotation mark)
     Copy the value from the cell above the active cell into the cell or the Formula Bar.
CTRL+' (apostrophe)
     Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+` (single left quotation mark)
     Alternate between displaying cell values and displaying formulas.
F9
     Calculate all worksheets in all open workbooks.
     When a portion of a formula is selected, calculate the selected portion. You can then press ENTER or CT
SHIFT+F9
     Calculate the active worksheet.
CTRL+ALT+F9
     Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last ca
CTRL+ALT+SHIFT+F9
     Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not mar


Edit data
F2
     Edit the active cell and position the insertion point at the end of the cell contents.
ALT+ENTER
     Start a new line in the same cell.
BACKSPACE
     Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit cell con
DELETE
     Delete the character to the right of the insertion point, or delete the selection.
CTRL+DELETE
     Delete text to the end of the line.
F7
     Display the Spelling dialog box.
SHIFT+F2
     Edit and/or create a cell comment.
ENTER
     Complete a cell entry and select the next cell below.
CTRL+Z
     Undo the last action.
ESC
     Cancel a cell entry.
CTRL+SHIFT+Z
     When the AutoCorrect Smart Tags is displayed, undo or redo the last automatic correction.
Insert, delete, and copy cells
CTRL+C
    Copy the selected cells.
CTRL+C, immediately followed by another CTRL+C
    Display the Microsoft Office Clipboard (multiple copy and paste).
CTRL+X
    Cut the selected cells.
CTRL+V
    Paste copied cells.
DELETE
    Clear the contents of the selected cells.
CTRL+HYPHEN
    Delete the selected cells.
CTRL+SHIFT+PLUS SIGN
    Insert blank cells.


Format data
ALT+' (apostrophe)
     Display the Style dialog box.                                  2,000.00      2000
CTRL+1
     Display the Format Cells dialog box.
CTRL+SHIFT+~                                    Good for Dates
     Apply the General number format.
CTRL+SHIFT+$
     Apply the Currency format with two decimal places (negative numbers in parentheses).
CTRL+SHIFT+%
     Apply the Percentage format with no decimal places.
CTRL+SHIFT+^
     Apply the Exponential number format with two decimal places.
CTRL+SHIFT+#
     Apply the Date format with the day, month, and year.
CTRL+SHIFT+@
     Apply the Time format with the hour and minute, and AM or PM.
CTRL+SHIFT+!
     Apply the Number format with two decimal places, thousands separator, and minus sign (–) for negative
CTRL+B
     Apply or remove bold formatting.
CTRL+I
    Apply or remove italic formatting.
CTRL+U
    Apply or remove underlining.
CTRL+5
    Apply or remove strikethrough.
CTRL+9
    Hide the selected rows.
CTRL+SHIFT+( (opening parenthesis)
    Unhide any hidden rows within the selection.
CTRL+0 (zero)
    Hide the selected columns.
CTRL+SHIFT+) (closing parenthesis)
    Unhide any hidden columns within the selection.
CTRL+SHIFT+&
    Apply the outline border to the selected cells.
CTRL+SHIFT+_
    Remove the outline border from the selected cells.


Use the Border tab in the Format Cells dialog box
Press CTRL+1 to display this dialog box.

ALT+T
    Apply or remove the top border.
ALT+B
    Apply or remove the bottom border.
ALT+L
    Apply or remove the left border.
ALT+R
    Apply or remove the right border.
ALT+H
    If cells in multiple rows are selected, apply or remove the horizontal divider.
ALT+V
    If cells in multiple columns are selected, apply or remove the vertical divider.
ALT+D
    Apply or remove the downward diagonal border.
ALT+U
    Apply or remove the upward diagonal border.

Keys for filtering, outlining, and managing ranges
Use data forms (Data menu, Form command)
DOWN ARROW
     Move to the same field in the next record.
UP ARROW
     Move to the same field in the previous record.
TAB and SHIFT+TAB
     Move to each field in the record, then to each command button.
ENTER
     Move to the first field in the next record.
SHIFT+ENTER
     Move to the first field in the previous record.
PAGE DOWN
     Move to the same field 10 records forward.
CTRL+PAGE DOWN
     Start a new, blank record.
PAGE UP
     Move to the same field 10 records back.
CTRL+PAGE UP
     Move to the first record.
HOME or END
     Move to the beginning or end of a field.
SHIFT+END
     Extend selection to the end of a field.
SHIFT+HOME
     Extend selection to the beginning of a field.
LEFT ARROW or RIGHT ARROW
     Move one character left or right within a field.
SHIFT+LEFT ARROW
     Select the character to the left within a field.
SHIFT+RIGHT ARROW
     Select the character to the right within a field.


Filter ranges (Data menu, AutoFilter command)
ALT+DOWN ARROW
     In the cell that contains the drop-down arrow, displays the AutoFilter list for the current column.
DOWN ARROW
     Selects the next item in the AutoFilter list.
UP ARROW
     Selects the previous item in the AutoFilter list.
ALT+UP ARROW
    Closes the AutoFilter list for the current column.
HOME
    Selects the first item (All) in the AutoFilter list.
END
    Selects the last item in the AutoFilter list.
ENTER
    Filters the range based on the item selected from the AutoFilter list.


Show, hide, and outline data
ALT+SHIFT+RIGHT ARROW                           This is very neat -- for moving stuff to powerpoint
     Groups rows or columns.
ALT+SHIFT+LEFT ARROW
     Ungroups rows or columns.
CTRL+8                                          This is good for quickly removing outlines
     Displays or hides the outline symbols.
CTRL+9                                          Like CNTL 0 for Columns
     Hides the selected rows.
CTRL+SHIFT+( (opening parenthesis)
     Unhides any hidden rows within the selection.
CTRL+0 (zero)                                   Repeat -- But very good for setting up model
     Hides the selected columns.
CTRL+SHIFT+) (closing parenthesis)
     Unhides any hidden columns within the selection.

Keys for PivotTable and PivotChart reports

Lay out a report onscreen
      1. Press F10 to make the menu bar active.

      2. Press CTRL+TAB or CTRL+SHIFT+TAB to make the PivotTable Field List active.

      3. Press the DOWN ARROW or UP ARROW key to select the field you want. Press RIGHT ARROW

      4. Press TAB to select the Add To list, and then press DOWN ARROW to open the list.

      5. Press DOWN ARROW or UP ARROW to select the area where you want to move the field, and then

      6. Press TAB to select the Add To button, and then press ENTER.
Use the PivotTable and PivotChart Wizard – Layout dialo
To display this dialog box, press TAB until Layout is selected in Step 3 of the PivotTable and PivotChart Wizar

UP ARROW or DOWN ARROW
     Selects the previous or next field button in the list on the right.
LEFT ARROW or RIGHT ARROW
     With two or more columns of field buttons, selects the button to the left or right.
ALT+R
     Moves the selected field into the Row area.
ALT+C
     Moves the selected field into the Column area.
ALT+D
     Moves the selected field into the Data area.
ALT+P
     Moves the selected field into the Page area.
ALT+L
     Displays the PivotTable Field dialog box for the selected field.


Display and hide items in a field
ALT+DOWN ARROW
     Displays the drop-down list for a field in a PivotTable or PivotChart report. Use the arrow keys to select t
UP ARROW
     Selects the previous item in the range.
DOWN ARROW
     Selects the next item in the range.
RIGHT ARROW
     For an item that has lower-level items available, displays the lower-level items.
LEFT ARROW
     For an item that has lower-level items displayed, hides the lower-level items.
HOME
     Selects the first visible item in the list.
END
     Selects the last visible item in the list.
ENTER
     Closes the list and displays the selected items.
SPACEBAR
     Checks, double-checks, or clears a check box in the list. Double-check selects both an item and all of its l
TAB
     Switches between the list, the OK button, and the Cancel button.
Change the layout of a report
CTRL+SHIFT+* (asterisk)
     Selects an entire PivotTable report.
ALT+SHIFT+RIGHT ARROW
     Groups the selected items in a PivotTable field.
ALT+SHIFT+LEFT ARROW
     Ungroups grouped items in a PivotTable field.

Keys for charts

Create charts and select chart elements
F11 or ALT+F1                                                   Alt+F1 is great if you want lables in the X-axis
      Creates a chart of the data in the current range.
CTRL+PAGE DOWN
      Selects a chart sheet: selects the next sheet in the workbook, until the chart sheet you want is selected.
CTRL+PAGE UP
      Selects a chart sheet: selects the previous sheet in the workbook, until the chart sheet you want is selected
DOWN ARROW
      Select the previous group of elements in a chart.
UP ARROW
      Selects the next group of elements in a chart.
RIGHT ARROW
      Selects the next element within a group.
LEFT ARROW
      Selects the previous element within a group.


Select an embedded chart
 1. Display the Drawing toolbar: Press ALT+V, press T, press DOWN ARROW until Drawing is selected, and
 2. Press F10 to make the menu bar active.
 3. Press CTRL+TAB or CTRL+SHIFT+TAB to select the Drawing toolbar.
 4. Press the RIGHT ARROW key to select the Select Objects
 button on the Drawing toolbar.
 5. Press CTRL+ENTER to select the first object.
 6. Press the TAB key to cycle forward (or SHIFT+TAB to cycle backward) through the objects until round sizi
 7. Press CTRL+ENTER to make the chart active so that you can select elements within it.

Keys for drawing objects and other objects
When both the Reviewing and Drawing toolbars are onscreen, ALT+U switches between the Review command


Select a drawing object
When you're editing text in a drawing object, you can select the next or previous object by pressing TAB or SHI

 1. Press F10, press CTRL+TAB to select the Drawing toolbar, and then press RIGHT ARROW to select the S
 button.
 2. Press CTRL+ENTER to select the first drawing object.
 3. Press the TAB key to cycle forward (or SHIFT+TAB to cycle backward) through the objects until sizing han

      If an object is grouped, TAB selects the group, then each object within the group, and then the next objec

 4. To switch back to the worksheet when an object is selected, press ESC.


Insert an AutoShape
 1. Press ALT+U to select the AutoShapes menu on the Drawing toolbar.
 2. Use the arrow keys to move to the category of AutoShapes you want, and then press the RIGHT ARROW k
 3. Use the arrow keys to select the AutoShape you want.
 4. Press CTRL+ENTER.
 5. To format the AutoShape, press CTRL+1 to display the Format AutoShape dialog box.


Insert a text box
 1. Press F10, press CTRL+TAB to select the Drawing toolbar, and then press RIGHT ARROW to select the T
 button.
 2. Press CTRL+ENTER.
 3. Type the text you want in the text box.
 4. Do one of the following:

      To return to the worksheet when you are finished typing, press ESC twice.

      To format the text box, press ESC, and then press CTRL+1 to display the Format Text Box dialog box. W


Insert WordArt
 1. Press ALT+I, then press P, then press W (Insert menu, Picture submenu, WordArt command).
 2. Use the arrow keys to select the WordArt style you want, and then press ENTER.
 3. Type the text you want, and then use the TAB key to select other options in the dialog box.
 4. Press ENTER to insert the WordArt object.
 5. To format the WordArt object, use the tools on the Word Art toolbar, or press CTRL+1 to display the Form


Rotate a drawing object
 1. Select the drawing object you want to rotate.
 2. Press CTRL+1 to display the Format menu for the object, and then press CTRL+TAB to select the Size tab
 3. Press ALT+T to select the Rotation box.
 4. Use the arrow keys to select the amount of rotation you want.


Change the size of a drawing object
 1. Select the drawing object you want to resize.
 2. Press CTRL+1 to display the Format menu for the object, and then press CTRL+TAB to select the Size tab
 3. Select the options you want to change the size.


Move a drawing object
 1. Select the drawing object you want to move.
 2. Press the arrow keys to move the object.
 3. To position the object precisely, press CTRL+ an arrow key to move the object in one-pixel increments.


Copy drawing objects and their attributes
To make a copy of a drawing object, select the object and press CTRL+D. To copy attributes such as fill color an

 1. Select the drawing object with the attributes you want to copy.

       For AutoShapes with text, the text format is copied along with the other attributes.

 2. Press CTRL+SHIFT+C to copy the object attributes.
 3. Press TAB or SHIFT+TAB to select the object you want to copy the attributes to.
 4. Press CTRL+SHIFT+V to copy the attributes to the object.

Keys for use with speech, e-mail, macros, and other languages

Use speech recognition and text-to-speech
CTRL
       Switches between command mode and dictation mode.
ESC
      Stops reading when text is being read aloud.


Send e-mail messages
To use keys to send e-mail messages, you must configure Microsoft Outlook as your default e-mail program. Mo

SHIFT+TAB
     When cell A1 is selected, moves to the Introduction box in the e-mail message header. In the message h
ALT+S
     Sends the e-mail message.
CTRL+SHIFT+B
     Opens the Address Book.
ALT+O
     Opens the Options menu for access to the Options, Bcc Field, and From Field commands.
ALT+P
     Opens the Outlook Message Options dialog box (Options menu, Options command).
ALT+K
     Checks the names in the To, Cc, and Bcc boxes against the Address Book.
ALT+PERIOD
     Opens the Address Book for the To box.
ALT+C
     Opens the Address Book for the Cc box.
ALT+B
     If the Bcc box is displayed, opens the Address Book for the Bcc box.
ALT+J
     Goes to the Subject box.
CTRL+SHIFT+G
     Creates a message flag.
ALT+A
     Adds interactivity to the range or sheet being sent.


Work with macros
ALT+F8
    Displays the Macro dialog box.
ALT+F11
    Displays the Visual Basic Editor.
CTRL+F11
    Inserts a Microsoft Excel 4.0 macro sheet.
Work with multiple national languages
CTRL+RIGHT SHIFT
      Switches to right-to-left paragraph direction (the text must contain only neutral characters (neutral characters: Char
CTRL+LEFT SHIFT
     Switches to left-to-right paragraph direction (the text must contain only neutral characters).
ALT+SHIFT+UP ARROW
     In Japanese text for which you've displayed phonetic guides, moves the pointer into the phonetic guides.
ALT+SHIFT+DOWN ARROW
     Moves the pointer from the phonetic guides back to the parent string of characters.
NUM LOCK, ALT+numeric pad numbers
     Enter a unicode character.
ALT+X
     Pressed immediately after typing the hexadecimal code for a unicode character, converts the numbers to t
     Pressed immediately following a unicode character, converts the character to its hexadecimal code.



Move within a selected range -- (Must be in a hilighted ra
ENTER
     Move from top to bottom within the selected range.
SHIFT+ENTER
     Move from bottom to top within the selected range.
TAB
     Move from left to right within the selected range. If cells in a single column are selected, move down.
SHIFT+TAB
     Move from right to left within the selected range. If cells in a single column are selected, move up.
CTRL+PERIOD
     Move clockwise to the next corner of the selected range.
CTRL+ALT+RIGHT ARROW
     In nonadjacent selections, switch to the next selection to the right.
CTRL+ALT+LEFT ARROW
     Switch to the next nonadjacent selection to the left.

Note You can change the direction of movement after pressing ENTER or SHIFT+ENTER: press ALT+T and t


Move and scroll in End mode
END appears in the status bar when End mode is selected.

END key
    Turn End mode on or off.
END+arrow key
    Move by one block of data within a row or column.
END+HOME
    Move to the last cell on the worksheet, in the bottom-most used row of the rightmost used column.
END+ENTER
    Move to the rightmost nonblank cell in the current row. This key sequence does not work if you have turn


Move and scroll with SCROLL LOCK on
When you use scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK off, cell selection m

SCROLL LOCK
     Turn SCROLL LOCK on or off.
HOME
     Move to the cell in the upper-left corner of the window.
END
     Move to the cell in the lower-right corner of the window.
UP ARROW or DOWN ARROW
     Scroll one row up or down.
LEFT ARROW or RIGHT ARROW
     Scroll one column left or right.

Keys for selecting data and cells




In the Help task pane
F1
      Display the Help task pane.
F6
      Switch between the Help task pane and the active application.
TAB
      Select the next item in the Help task pane.
SHIFT+TAB
     Select the previous item in the Help task pane.
ENTER
     Perform the action for the selected item.
DOWN ARROW and UP ARROW
     In a Table of Contents, select the next and previous item, respectively.
RIGHT ARROW and LEFT ARROW
     In a Table of Contents, expand and collapse the selected item, respectively.
ALT+LEFT ARROW
     Move back to the previous task Pane.
ALT+RIGHT ARROW
     Move forward to the next task Pane.
CTRL+SPACEBAR
     Open the menu of Pane options.
CTRL+F1
     Close and reopen the current task pane.
RIGHT ARROW
     Expand a +/- list.
LEFT ARROW
     Collapse a +/- list.

In the Help window
TAB
     Select the next hidden text or hyperlink, or Show All or Hide All at the top of a topic
SHIFT+TAB
     Select the previous hidden text or hyperlink, or the Browser View button at the top of a Microsoft Office
ENTER
     Perform the action for the selected Show All, Hide All, hidden text, or hyperlink
ALT+LEFT ARROW
     Move back to the previous Help topic.
ALT+RIGHT ARROW
     Move forward to the next Help topic.
CTRL+P
     Print the current Help topic.
UP ARROW AND DOWN ARROW
     Scroll small amounts up and down, respectively, within the currently-displayed Help topic.
PAGE UP AND PAGE DOWN
     Scroll larger amounts up and down, respectively, within the currently-displayed Help topic.
ALT+U
     Change whether the Help window appears connected to (tiled) or separate from (untiled) the active applic
SHIFT+F10
      Display a menu of commands for the Help window; requires that the Help window have active focus (clic

Keys for the Office interface

Display and use windows

ALT+TAB
     Switch to the next program.
ALT+SHIFT+TAB
     Switch to the previous program.
CTRL+ESC
     Display the Windows Start menu.
CTRL+W or CTRL+F4
     Close the selected workbook window.
CTRL+F5
     Restore the window size of the selected workbook window.
F6
     Switch to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6
     Switch to the previous pane in a worksheet that has been split.
CTRL+F6
     When more than one workbook window is open, switch to the next workbook window.
CTRL+SHIFT+F6
     Switch to the previous workbook window.
CTRL+F7
     When a workbook window is not maximized, perform the Move command (on the Control menu for the
CTRL+F8
     When a workbook window is not maximized, perform the Size command (on the Control menu for the w
CTRL+F9
     Minimize a workbook window to an icon.
CTRL+F10
     Maximize or restore the selected workbook window.
PRTSCR
     Copy a picture of the screen to the Clipboard.
ALT+PRINT SCREEN
     Copy a picture of the selected window to the Clipboard.

Access and use smart tags

ALT+SHIFT+F10
     Display the menu or message for a smart tag. If more than one smart tag is present, switch to the next sma
DOWN ARROW
     Select the next item in a smart tag menu.
UP ARROW
     Select the previous item in a smart tag menu.
ENTER
     Perform the action for the selected item in a smart tag menu.
ESC
     Close the smart tag menu or message.

Tip

You can ask to be notified by a sound whenever a smart tag appears. To hear audio cues, you must have a sound

If you have access to the World Wide Web, you can download Microsoft Office Sounds from the Microsoft Offi

Access and use task panes

F6
      Move to a task pane (task pane: A window within an Office application that provides commonly used commands. It

      Note If pressing F6 doesn't display the task pane you want, try pressing ALT to place focus on the menu

CTRL+TAB
      When a menu or toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a
TAB or SHIFT+TAB
     When a task pane is active, select the next or previous option in the task pane
CTRL+SPACEBAR
     Display the full set of commands on the task pane menu
DOWN ARROW or UP ARROW
     Move among choices in a selected submenu; move among certain options in a group of options
SPACEBAR or ENTER
     Open the selected menu, or perform the action assigned to the selected button
SHIFT+F10
      Open a shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To disp
HOME or END
     When a menu or submenu is visible, select the first or last command on the menu or submenu
PAGE UP or PAGE DOWN
     Scroll up or down in the selected gallery list
CTRL+HOME or CTRL+END
     Move to the top or bottom of the selected gallery list

Access and use menus and toolbars

F10 or ALT
      Select the menu bar (menu bar: The horizontal bar below the title bar that contains the names of menus. A menu b
TAB or SHIFT+TAB
     When a toolbar is selected, select the next or previous button or menu on the toolbar.
CTRL+TAB or CTRL+SHIFT+TAB
     When a toolbar is selected, select the next or previous toolbar.
ENTER
     Open the selected menu, or perform the action for the selected button or command.
SHIFT+F10
      Display the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To
ALT+SPACEBAR
     Display the Control menu for the Excel window.
DOWN ARROW or UP ARROW
     When a menu or submenu is open, select the next or previous command.
LEFT ARROW or RIGHT ARROW
     Select the menu to the left or right. When a submenu is open, switch between the main menu and the subm
HOME or END
     Select the first or last command on the menu or submenu.
ESC
     Close an open menu. When a submenu is open, close only the submenu.
CTRL+DOWN ARROW
     Display the full set of commands on a menu.
CTRL+7
     Show or hide the Standard toolbar.

Note You can select any menu command on the menu bar or on a displayed toolbar with the keyboard. To selec

Resize and move toolbars and task panes

      1.    Press ALT to select the menu bar (menu bar: The horizontal bar below the title bar that contains the names o

      2. Press CTRL+TAB repeatedly to select the toolbar (toolbar: A bar with buttons and options that you u

      3. Do one of the following:

      Resize a toolbar

             1. In the toolbar, press CTRL+SPACE to display the Toolbar Options menu.

             2. Select the Size command, and then press ENTER.

             3. Use the arrow keys to resize the toolbar.

      Move a toolbar

             4. In the toolbar, press CTRL+SPACE to display the Toolbar Options menu.

             5. Select the Move command, and then press ENTER.
              6. Use the arrow keys to position the toolbar. Press CTRL+ the arrow keys to move one pixel at a

      Resize a task pane

              7. In the task pane, press CTRL+SPACE to display a menu of additional commands.

              8. Use the DOWN ARROW key to select the Size command, and then press ENTER.

              9. Use the arrow keys to resize the task pane. Use CTRL+ the arrow keys to resize by one pixel a

      Move a task pane

              10. In the task pane, press CTRL+SPACE to display a menu of additional commands.

              11. Use the DOWN ARROW key to select the Move command, and then press ENTER.

              12. Use the arrow keys to position the task pane. Use CTRL+ the arrow keys to move one pixel at

       4. When you are finished moving or resizing, press ESC.

Use dialog boxes

TAB
        Move to the next option or option group.
SHIFT+TAB
        Move to the previous option or option group.
CTRL+TAB or CTRL+PAGE DOWN
        Switch to the next tab in a dialog box.
CTRL+SHIFT+TAB or CTRL+PAGE UP
        Switch to the previous tab in a dialog box.
Arrow keys
        Move between options in an open drop-down list, or between options in a group of options.
SPACEBAR
        Perform the action for the selected button, or select or clear the selected check box.
First letter of an option in a drop-down list
        Open the list if it is closed and move to that option in the list.
ALT+ the underlined letter in an option
        Select an option, or select or clear a check box.
ALT+DOWN ARROW
        Open the selected drop-down list.
ENTER
        Perform the action for the default command button in the dialog box (the button with the bold outline, oft
ESC
       Cancel the command and close the dialog box.

Use edit boxes within dialog boxes

An edit box is a blank in which you type or paste an entry, such as your user name or the path (path: The route that the ope

HOME
     Move to the beginning of the entry.
END
     Move to the end of the entry.
LEFT ARROW or RIGHT ARROW
     Move one character to the left or right.
CTRL+LEFT ARROW
     Move one word to the left.
CTRL+RIGHT ARROW
     Move one word to the right.
SHIFT+LEFT ARROW
     Select or unselect one character to the left.
SHIFT+RIGHT ARROW
     Select or unselect one character to the right.
CTRL+SHIFT+LEFT ARROW
     Select or unselect one word to the left.
CTRL+SHIFT+RIGHT ARROW
     Select or unselect one word to the right.
SHIFT+HOME
     Select from the insertion point to the beginning of the entry.
SHIFT+END
     Select from the insertion point to the end of the entry.

Use the Open, Save As, and Insert Picture dialog boxes

The Open, Insert Picture, and Save As dialog boxes support standard dialog box keyboard shortcuts. (To view

ALT+1
     Go to the previous folder

ALT+2
     Up One Level
     button: open the folder up one level above the open folder
ALT+3
     Search the Web
       button: close the dialog box and open your Web search page (search page: A page from which you can find and go
ALT+4
     Delete
     button: delete the selected folder or file
ALT+5
     Create New Folder
     button: create a new folder
ALT+6
     Views
     button: switch among available folder views
ALT+7 or ALT+L
     Tools button: show the Tools menu
SHIFT+F10
       Display a shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To di
TAB
       Move between options or areas in the dialog box
F4 or ALT+I
       Open the Look in list
F5
       Refresh the file list

Keys for workbooks and worksheets

Preview and print

CTRL+P or CTRL+SHIFT+F12
    Display the Print dialog box.

Use the following keys in print preview (to get to print preview, press ALT+F, then press V):

Arrow keys
      Move around the page when zoomed in.
PAGE UP or PAGE DOWN
      Move by one page when zoomed out.
CTRL+UP ARROW or CTRL+LEFT ARROW
      Move to the first page when zoomed out.
CTRL+DOWN ARROW or CTRL+RIGHT ARROW
      Move to the last page when zoomed out.




Some of the content in this topic may not be applicable to some languages.
To print this topic, press TAB to select Show All, press ENTER, and then press CTRL+P.

Keyboard shortcuts for using the Help

Keyboard shortcuts for using the Help task pane and Help window

The Help Pane is a task pane that provides access to all Office Help content. As a task pane, the Help Pane appe
             It is good to try to practice these. Like the first one -
             - ALT+SHIFT+F1



sert sheet and then F4. Use ALT+SHIFT+F1 rather than SHIFT+F11


ving between pages




ress CTRL+PAGE DOWN or, to select a different sheet, press CTRL+PAGE UP.




ood with the F8 key and long lists
hat contains data and that is bounded by empty cells or worksheet borders.).




 rightmost used column.
, Split command).




 mark all of the inputs when using the special feature




ywhere in the sheet)
erting and deleting rows




mes to get the entire sheet




holders for objects.




blank rows and blank columns). In a PivotTable report, select the entire PivotTable report.

e results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formul
ne, and the arrow keys extend the selection.

to the start of the range you want to add, and then press F8 and the arrow keys to select the next range.


ether with hiding remaining rows or columns
s the active cell.




s the active cell.




does not work if you have turned on transition navigation keys (Tools menu, Options command, Transition tab).
range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges,




t you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can
by using the numeric key pad:




get this to work




ms multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas a


ool; you must first use the F2 key



splay the Function Arguments dialog box.

sert the argument names and parentheses.

 a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand
u can then press ENTER or CTRL+SHIFT+ENTER (for array formulas) to replace the selected portion with the calculated value




y have changed since the last calculation.

kbooks, including cells not marked as needing to be calculated.




e active cell as you edit cell contents.




matic correction.
                200000%




parentheses).




nd minus sign (–) for negative values.
alog box
mand)

or the current column.
 stuff to powerpoint




 ting up model




 eld List active.

 want. Press RIGHT ARROW or LEFT ARROW to open or close a field that can be expanded.

 to open the list.

want to move the field, and then press ENTER.
d – Layout dialog box
votTable and PivotChart Wizard.




t. Use the arrow keys to select the field.




ects both an item and all of its llower-level items.
 want lables in the X-axis



 t sheet you want is selected.

 chart sheet you want is selected.




W until Drawing is selected, and then press ENTER.




 ugh the objects until round sizing handles (sizing handle: One of the small circles or squares that appears at the corners and side
 between the Review command and the AutoShapes command, and ENTER performs the selected command.




object by pressing TAB or SHIFT+TAB. Starting from a worksheet, do the following:

RIGHT ARROW to select the Select Objects


 ugh the objects until sizing handles appear on the object you want to select.

 group, and then the next object.




n press the RIGHT ARROW key.




RIGHT ARROW to select the Text Box




Format Text Box dialog box. When you finish formatting, press ENTER, and then press ESC to return to the worksheet.




ordArt command).

he dialog box.
ss CTRL+1 to display the Format WordArt dialog box.




RL+TAB to select the Size tab.




RL+TAB to select the Size tab.




 ct in one-pixel increments.




py attributes such as fill color and line style from one object to another, do the following:
our default e-mail program. Most of these keys do not work with Outlook Express.


ssage header. In the message header, moves to the Subject, Bcc (if displayed), Cc, To, and From (if displayed) boxes, then to th




Field commands.

s command).
aracters (neutral characters: Characters that do not have strong right-to-left or left-to-right language attributes. Numerals are an example of n

utral characters).

inter into the phonetic guides.




acter, converts the numbers to the character.
 to its hexadecimal code.



in a hilighted range)




n are selected, move down.

n are selected, move up.




T+ENTER: press ALT+T and then O (Tools menu, Options command), press CTRL+TAB until the Edit tab is selected, and the
rightmost used column.

does not work if you have turned on transition navigation keys (Tools menu, Options command, Transition tab).




LL LOCK off, cell selection moves the distance you scroll. To scroll without changing which cells are selected , turn on SCROL
at the top of a Microsoft Office Web site article




ayed Help topic.

layed Help topic.

from (untiled) the active application.
window have active focus (click an item in the Help window).




, Split command).




ook window.




d (on the Control menu for the workbook window). Use the arrow keys to move the window, and when finished press ESC.

(on the Control menu for the workbook window). Use the arrow keys to resize the window, and when finished press ESC.




 present, switch to the next smart tag and display its menu or message.
io cues, you must have a sound card. You must also have Microsoft Office Sounds installed on your computer.

Sounds from the Microsoft Office Web site. On the Help menu, click Microsoft Office Online and search for "Microsoft Office




es commonly used commands. Its location and small size allow you to use these commands while still working on your files.) from another p

LT to place focus on the menu bar, and then pressing CTRL+TAB to move to the task pane.


carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.) is active, move to a task pan




in a group of options



levant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.); open a drop-down menu for the selected ga

e menu or submenu




s the names of menus. A menu bar can be the built-in menu bar or a custom menu bar.), or close an open menu and submenu at the same
s relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.) for the selected item.




een the main menu and the submenu.




bar with the keyboard. To select the menu bar, press ALT. Then to select a toolbar, press CTRL+TAB repeatedly until you select



itle bar that contains the names of menus. A menu bar can be the built-in menu bar or a custom menu bar.).

 buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then cli




tions menu.




tions menu.
row keys to move one pixel at a time. To undock the toolbar, press DOWN ARROW repeatedly. To dock the toolbar vertically o



 itional commands.

 then press ENTER.

ow keys to resize by one pixel at a time.



itional commands.

d then press ENTER.

 row keys to move one pixel at a time.




group of options.




button with the bold outline, often the OK button).
 path (path: The route that the operating system uses to locate a folder or file; for example, C:\House finances\March.doc.) to a folder.




x keyboard shortcuts. (To view standard shortcuts for dialog boxes, refer to the Use Dialog Boxes and Use Edit Boxes Within D




ge from which you can find and go to other Internet sites or to documents on an intranet. Many search pages provide various ways to search
relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.) for a selected item such as a folder or file




en press V):
task pane, the Help Pane appears as part of the active application. The Help window displays topics and other Help content and
ay range shares a common formula; an array constant is a group of constants used as an argument.) containing the active cell.
d, Transition tab).
efer to hard to understand ranges, such as Sales!C20:C30.) from row and column labels.




ge on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.).
multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).




cts, to refer to hard to understand ranges, such as Sales!C20:C30.) into a formula.
portion with the calculated value.
at appears at the corners and sides of a selected object. You drag these handles to change the size of the object.) appear on the em
cted command.




to return to the worksheet.
om (if displayed) boxes, then to the address book for the Bcc, Cc, To, and From boxes, and then to cell A1.
butes. Numerals are an example of neutral characters.)).




til the Edit tab is selected, and then change the Move selection after Enter settings.
nd, Transition tab).




cells are selected , turn on SCROLL LOCK first.
nd when finished press ESC.

d when finished press ESC.
your computer.

 and search for "Microsoft Office Sounds." After you've installed the sound files, you need to select the Provide feedback with




working on your files.) from another pane in the program window. (You may need to press F6 more than once.)




rs tab.) is active, move to a task pane. (You may need to press CTRL+TAB more than once.)




 drop-down menu for the selected gallery item




pen menu and submenu at the same time.
 he selected item.




L+TAB repeatedly until you select the toolbar you want. Press the underlined letter in the menu that contains the command you w




 e on the Tools menu, and then click the Toolbars tab.) or task pane (task pane: A window within an Office application that prov
y. To dock the toolbar vertically on the left or right side, press LEFT ARROW or RIGHT ARROW respectively when the toolba
ances\March.doc.) to a folder.




xes and Use Edit Boxes Within Dialog Boxes sections in the main Keyboard Shortcuts topic.) These dialog boxes also support t




ages provide various ways to search, such as by topic, by keyword, or by matches to user queries.)
selected item such as a folder or file
opics and other Help content and appears as a window next to, but separate from, the active application.
nt.) containing the active cell.
TRL+SHIFT+ENTER.).
ze of the object.) appear on the embedded chart you want to select.
en to cell A1.
elect the Provide feedback with sound check box on the General tab of the Options dialog box (Tools menu). When you selec
 that contains the command you want. In the menu that appears, press the underlined letter in the command that you want.




hin an Office application that provides commonly used commands. Its location and small size allow you to use these commands w
OW respectively when the toolbar is all the way to the left or right side.
These dialog boxes also support the shortcuts below.
ox (Tools menu). When you select (or clear) this check box, the setting affects all Office programs that support sound.
e command that you want.




low you to use these commands while still working on your files.) you want.
ams that support sound.
Table of Contents

   Key
CTRL+(            9
CTRL+)            0
CTRL+&            7
CTRL+_            8
CTRL+~        `
CTRL+$            4
CTRL+%            5
CTRL+^            6
CTRL+#            3
CTRL+@            2
CTRL+!            1
CTRL+-
CTRL+*

CTRL+:
CTRL+;
CTRL+`
CTRL+'
CTRL+"
CTRL++
CTRL+1
CTRL+2
CTRL+3
CTRL+4
CTRL+5
CTRL+6
CTRL+7
CTRL+8
CTRL+9
CTRL+0
CTRL+A



CTRL+B
CTRL+C

CTRL+D
CTRL+E
CTRL+F

CTRL+G

CTRL+H
CTRL+I
CTRL+J
CTRL+K
CTRL+L
CTRL+M
CTRL+N
CTRL+O

CTRL+P
CTRL+Q
CTRL+R
CTRL+S
CTRL+T
CTRL+U
CTRL+V
CTRL+W
CTRL+X
CTRL+Y
CTRL+Z



F1



F2

F3

F4

F5

F6



F7

F8



F9




F10



F11
F11



F12
ARROW
KEYS




BACKSPACE

DELETE

END



ENTER




ESC

HOME




PAGE DOWN



PAGE UP



SPACEBAR




TAB
TAB




 100,000.00

 100,000.00

      11.00



      11.00



      11.00
CNTL Shortcuts

                                                      Description
Unhides any hidden rows within the selection.
Unhides any hidden columns within the selection.
Applies the outline border to the selected cells.
Removes the outline border from the selected cells.
Applies the General number format.
Applies the Currency format with two decimal places (negative numbers in parentheses).
Applies the Percentage format with no decimal places.
Applies the Exponential number format with two decimal places.
Applies the Date format with the day, month, and year.
Applies the Time format with the hour and minute, and AM or PM.
Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
Displays the Delete dialog box to delete the selected cells.
Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
In a PivotTable, it selects the entire PivotTable report.
Enters the current time.
Enters the current date.
Alternates between displaying cell values and displaying formulas in the worksheet.
Copies a formula from the cell above the active cell into the cell or the Formula Bar.
Copies a formula from the cell above the active cell into the cell or the Formula Bar.
Displays the Insert dialog box to insert blank cells.
Displays the Format Cells dialog box.
Applies or removes bold formatting.
Applies or removes italic formatting.
Applies or removes underlining.
Applies or removes strikethrough.
Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
Displays or hides the Standard toolbar.
Displays or hides the outline symbols.
Hides the selected rows.
Hides the selected columns.
Selects the entire worksheet.
If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the
When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog
CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a
Applies or removes bold formatting.
Copies the selected cells.
CTRL+C followed by another CTRL+C displays the Microsoft Office Clipboard.
Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the
Blank
Displays the Find dialog box.
SHIFT+F5 also displays this dialog box, while SHIFT+F4 repeats the last Find action.
Displays the Go To dialog box.
F5 also displays this dialog box.
Displays the Find and Replace dialog box.
Applies or removes italic formatting.
Blank
Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing
Displays the Create List dialog box.
Blank
Creates a new, blank file.
Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments.
Displays the Print dialog box.
Blank
Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells
Saves the active file with its current file name, location, and file format.
Blank
Applies or removes underlining.
Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut
Closes the selected workbook window.
Cuts the selected cells.
Repeats the last command or action, if possible.
Uses the Undo command to reverse the last command or to delete the last entry you typed.
CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when


Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion
SHIFT+F2 edits a cell comment.
Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to
Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion
of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks,
CTRL+F9 minimizes a workbook window to an icon.
Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it
CTRL+F10 maximizes or restores the selected workbook window.
Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any
Displays the Save As dialog box.
Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains
data and that is bounded by empty cells or datasheet borders.) in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as
LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu
is open, these arrow keys switch between the main menu and the submenu.
DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of
ALT+DOWN ARROW opens a selected drop-down list.
Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.
Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.
CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold
ALT+ENTER starts a new line in the same cell.
CTRL+ENTER fills the selected cell range with the current entry.
SHIFT+ENTER completes a cell entry and selects the cell above.
Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.
Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
CTRL+HOME moves to the beginning of a worksheet.
CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
   If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing
   When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.
Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
CTRL+TAB switches to the next tab in dialog box.
CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
      A       B
One       1       2
Two       4       5
Table of Contents
       Key               Alone               Shift                 Ctrl                  Alt
        F1                Help        What's This Help                           Insert Chart Sheet
        F2             Edit Mode        Edit Comment                                  Save As
        F3        Paste Name Formula   Paste Function          Define Name
        F4           Repeat Action       Find Again           Close Window          Quit Excel
        F5                Goto               Find          Restore Window Size
        F6             Next Pane          Prev Pane           Next Workbook       Switch To VBA
        F7            Spell Check                             Move Window
        F8          Extend Selection  Add To Selection        Resize Window         Macro List
        F9            Calculate All  Calculate Worksheet    Minimize Workbook
       F10           Activate Menu      Context Menu        Restore Workbook
       F11             New Chart       New Worksheet         New Macro Sheet         VB Editor
       F12              Save As              Save                  Open
         A                                                      Select All
         B                                                          Bold
         C                                                         Copy
         D                                                       Fill Down          Data Menu
         E                                                                          Edit Menu
         F                                                        Find              File Menu
         G                                                        Goto
         H                                                      Replace              Help Menu
          I                                                      Italics            Insert Menu
         J
         K                                                   Insert Hyperlink
         L
         M
         N                                                   New Workbook
         O                                                   Open Workbook         Format Menu
         P                                                        Print
         Q
         R                                                      Fill Right
         S                                                        Save
         T                                                                          Tools Menu
         U                                                      Underline
         V                                                        Paste
        W                                                    Close Workbook       Window Menu
         X                                                         Cut
         Y                                                    Repeat Active
         Z                                                        Undo
       ` (~)                                               Toggle Formula View
       1 (!)                                                   Cell Format
      2 (@)                                                    Toggle Bold
      3 (#)                                                   Toggle Italics
      4 ($)                                                  Toggle Underline
      5 (%)                                                 Toggle Strikethru
      6 (^)                                                          a
      7 (&)                                                          a
       8 (*)                                                      Outline
       9 (()                                                    Hide Rows
       0 ())                                                  Hide Columns
         -                                                   Delete Selection      Control Menu
       = (+)      Formula                                                 Auto Sum
         [                                     Direct Dependents
         ]                                      All Dependents
 ; (semicolon)                                     Insert Date        Select Visible Cells
' (apostrophe)                                                               Style
    : (colon)                                      Insert Time
         /                                         Select Array
         \                                      Select Differences
      Insert     Insert Mode                           Copy
      Delete        Clear                     Delete To End Of Line
      Home        Begin Row                    Start Of Worksheet
       End         End Row                      End Of Worksheet
    Page Up        Page Up                     Previous Worksheet       Left 1 screen
  Page Down      Page Down                       Next Worksheet         Right 1 screen
   Left Arrow     Move Left     Select Left      Move Left Area
  Right Arrow    Move Right    Select Right      Move Right Area
    Up Arrow       Move Up      Select Up         Move Up Area
  Down Arrow     Move Down     Select Down      Move Down Area          Drop down list
   Space Bar        Space      Select Row         Select Column          Control Box
       Tab       Move Right     Move Left         Next Window          Next Application
  BackSpace                                      Goto Active Cell
    Shift Ctrl


Names From Labels


 Prev Workbook




      Print
Formula Arguments




   Font Name




 Select Comments
     Font Size




  General Format
 Number Format
   Time Format
   Date Format
 Currency Format
  Percent Format
 Exponent Format
   Apply Border
   Select Region
   Unhide Rows
 Unhide Columns
    No Border
     Insert dialog
  Direct Precedents
   All Precedents
      Insert Time
Copy Cell Value Above

    Select Array
Select Unequal Cells




     Select All
  Previous Window
Table of Contents                          Create Input Colours
                                    0.06      0.06         0.06
Formula                          $0.00         150
Multiplication with =                  8
Addition with =                       52
Input Number                           1       2.2
Formula                            -100
Formula                               52
Year                               2005              Percentage Input              5%
                                 2005.1              Percentage Input              5%
Zero Input                             0             Compound Percentage       15.00%

With Dependent                       55
                                                     Partial Inputs                0.5
With = sign                          55                      275      13750     20625
Without Dependent                    55
                                                     Round formula               20625
Partial Input                        275             Round Test               FALSE
Partial Input with 1               291.5             Bad Form                 125812.5
Second Partial Input         400,812.50              Bad Form                  8429438
Partial Input with 100         4,008.13
Partial Input with 200        FALSE                            1         2          3
Dollar Sign                        $100
Dollar Sign 0                     $0.00                      13

Formula                           15125
Input with No Dependent             100
Function                             0.1
Formula with -                   -15125
Formula with +                    15125

Partial Input with Formula      76125.5
          Show Comments




    0.5
10312.5




     4
Table of Contents
                                                    Create Linked Colours
           ='CNTL Shortcuts'!C5-->     Displays the Format Cells dialog box.

           ='Payback Function'!C5-->         -100

           =Interpolate!D16-->          5.331235

           =D8+D6-->                    -94.6688
                                                                               Show Comments
ed Colours




      Show Comments
Table of Contents
                         Available Colours for Setting Inputs

        1           11                     21                   31   41
        1           12                     22                   32   42
        2           13                     23                   33   43
        3           14                     24                   34   44
        4           15                     25                   35   45
        5           16                     26                   36   46
        6           17                     27                   37   47
        7           18                     28                   38   48
        8           19                     29                   39   49
        9           20                     30                   40   50

                     0 No Fill
51
52
53
54
55
56
                S
Table of Contents et The Column Area
                                                                   11
                                                                                 Discussion of Show Comments Code
               To set column macro in another sheet                              (It is very easy to create a button by you
               Copy the four columns below and run initial macro


                                   13 columns




                   Show Comments
                                                   FALSE



               To copy:
               1: Copy the button
               2. Insert a range name comment in the new file
               3. Copy the macro named set comment into the new file
               4. Change the cell link from comments to comment
                 a. Right Click and go to format control




                                                                        Change the name comments to
                                                                        comment after making sure to
                                                                        change the name
                                                            Code for Comments

'                                                              Create the code by turning on macro and switching on the com

Sub set_comments()
'
' set_comments Macro
' Macro recorded 5/2/2006 by Elvis Presley
'

    Dim test As Boolean

    test = Range("comments")                                   'Create a range name


'
    If test = True Then                                       'Add and if statement
      Application.DisplayCommentIndicator = xlCommentAndIndicator

    Else
     Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    End If

End Sub
n of Show Comments Code
easy to create a button by yourself)




omments to
acro and switching on the comment indicator
Table of Contents

                        Sheet

                    8   Sheet8
                    7   Sheet7
                    6   Sheet6
                    4   Sheet4
                    5   Sheet5
                    6   Sheet6
                    7   Sheet7
                    8   Sheet8
                    9   Sheet9
Table of Contents
                    Alternative Print Macros




                                                     Show Comments



                                  These macros manage macros that can print sheets

                                  Must copy and modify the PRINTFORM1 userform
hat can print sheets   This macro illustrates how you can create a new menu

FORM1 userform         Must use your own macros to print sheets
Table of Contents


                    Create Range Name for Constant

                    =day-->

                    =days-->
nge Name for Constant

           Thursday

                 365
Table of Contents




                    Instructions to Copy this macro to your file:

                    Step 1: Copy the macro (Alt F11 and copy to your file)
                    Step 2: Copy the Disclaimer Userform

                    To copy the disclaimer form it is a little tricky -- go to the project explo
                                                    Works by hiding all sheets with auto close function
                                                    In auto_open program, unhide the sheets




and copy to your file)


a little tricky -- go to the project explorer and copy into the new file
Table of Contents
Model Name

IMPORTANT NOTICE - CONFIDENTIALITY AND DISCLAIMER


The terms and conditions of the Confidentiality Deed Poll and the Due Diligence Protocol plus the Disclaimers in
the Information Memorandum of DD MMM YYYY apply to this model

You would enter some words here regarding the confidentiality.

To implement this technique, you would use the auto_open and auto_close marcros -- (note that the marcros
would have to be re-named)
Table of Contents

                                Re-Size and/or Delete All Graphs in a Workbook


                    This macro re-sizes multiple grahs in a sheet
                    Simply copy the macro into another sheet

                    If the option to apply to charts is used, the sheet name must begin with the name chart -- eg.




                    CNTL J - Enlarge Sheets
                    CNTL M - Back to 100%


                    ALT C




                    Delete all non-hidden charts in file




                                                  2012      2013        2014        2015
                                test               200       100        -300         500
phs in a Workbook




must begin with the name chart -- eg. Chart EPS; EPS Chart would not work
Table of Contents

                    Change the Format of SEC Downloads and Move She




                    This macro is good for reading SEC docmumets

                    After Running - Use CNTL I to run the macro


                     The macro called here re-formats sheets after data is
                     copied and pasted from the SEC formats.

                     First copy the data from the SEC and then run the
                     macro




                    Application.OnKey "^i", "AA______Format_sheet_SEC_Download"
ownloads and Move Sheets for Accumulation

                                                      2009     2010

                                   Other Income        282     2 01
                                   Interest Expense   (259 )   (113
                                   Other Expense      (729 )   (791
                                   Misc                 (4 )     (4




after data is




 heet_SEC_Download"
    2011

     229
)    (85 )
)   (766 )
)     (4 )
 1      2     3      4      5

        A     B      C      D


100    200    300   400    500


AA            CC    DD     EE

1000   2000         4000   5000

 X      Y     Z     AA     AB

 1      2     3      4      5
                     test
600



500



400



300



200



100                                       test




  0
       2012   2013          2014   2015


-100



-200



-300



-400
Table of Contents

        First: Make the Graph with the F11 Key

                           2008    2009               2010          2011     2012               2013
        One                 100     120                 90            80      100                120
        Two                 130     150                190           200      250                300




                                          Click on Chart to Enlarge


                     350


                     300


                     250


                     200
                                                                                    One   Two

                     150


                     100


                      50


                      0
                            2008   2009        2010          2011     2012   2013                      It is easy to ma
                                                                                                       Create a sepa
                                                                                                       Use the visible
                                                                                                       Make a user fo
To Show the Graph

Step 1: Copy the Graph Somewhere
Step 2: Select the chart option and put the chart in a new sheet (remember the chart number)
Step 3: Right Click on the Chart and Create a New Macro
Step 4: Copy the macro from this file into the Macro
Step 5: Edit the Macro and Use the Correct Chart Number
Step 6: Copy the userform ContinueGr to sheet
            To see how to copy, refer to sheet named copy forms




It is easy to make this from scratch:
Create a separate sheet with the chart
Use the visible=TRUE and visible=FALSE to first show and then hide
Make a user form yourself that begins at 0,0
Table of Contents   This Macro Moves Sheets into a ma

                    To Use

                    Run the macro once

                    Then use CNTL M from the sheet
o Moves Sheets into a master sheet -- I use it when downloaded files must be consolidated




CNTL M from the sheet
                    Show Notes in Program for Documentation
                                               This process is quite simple
Table of Contents
                                               Step 1; Add a column
      Show Notes

                                               Step2: Use SHIFT-ALT-F1 to Group

                                               Step3: Add a Check Box Form or C

 FALSE                                         Step4: Make a range name such as

                                               Step5: Edit the range name in the m
ess is quite simple



e SHIFT-ALT-F1 to Group the column

d a Check Box Form or Copy the Form Here

ke a range name such as show_notes and link the check box to the variable

t the range name in the macro
Table of Contents

Rows to Delete        1         2       3      4      5

                                A       B      C      D


                     100       200      300   400    500


                     AA                 CC    DD     EE

                     1000     2000            4000   5000

                      X         Y       Z     AA     AB

                      1         2       3      4      5




                    S&P
          Month and Year 500 WMT
 2-Jan-75 1 1975         70.23   0.02
 3-Jan-75 1 1975         70.71   0.01
 6-Jan-75 1 1975         71.07   0.02
 7-Jan-75 1 1975         71.02   0.02
 8-Jan-75 1 1975         70.04   0.02
 9-Jan-75 1 1975         71.17   0.02
10-Jan-75 1 1975         72.61   0.02
13-Jan-75 1 1975         72.31   0.02
14-Jan-75 1 1975         71.68   0.02
15-Jan-75 1 1975         72.14   0.02
16-Jan-75 1 1975         72.05   0.02
17-Jan-75 1 1975         70.96   0.02
20-Jan-75 1 1975         71.08   0.02
21-Jan-75 1 1975          70.7   0.02
22-Jan-75 1 1975         71.74   0.02
23-Jan-75 1 1975         72.07   0.02
24-Jan-75 1 1975         72.98   0.02
27-Jan-75 1 1975         75.37   0.02
28-Jan-75 1 1975         76.03   0.02
29-Jan-75 1 1975         77.26   0.02
30-Jan-75 1 1975         76.21   0.02
31-Jan-75 1 1975         76.98   0.02
          1 1975 Total 1596.41   0.43
 3-Feb-75 2 1975         77.82   0.02
 4-Feb-75 2 1975         77.61   0.02
 5-Feb-75   2 1975         78.95   0.02
 6-Feb-75   2 1975         78.56   0.02
 7-Feb-75   2 1975         78.63   0.02
10-Feb-75   2 1975         78.36   0.02
11-Feb-75   2 1975         78.58   0.02
12-Feb-75   2 1975         79.92   0.02
13-Feb-75   2 1975         81.01   0.02
14-Feb-75   2 1975          81.5   0.02
18-Feb-75   2 1975         80.93   0.02
19-Feb-75   2 1975         81.44   0.02
20-Feb-75   2 1975         82.21   0.02
21-Feb-75   2 1975         82.62   0.02
24-Feb-75   2 1975         81.44   0.02
25-Feb-75   2 1975         79.53   0.02
26-Feb-75   2 1975         80.37   0.02
27-Feb-75   2 1975         80.77   0.02
28-Feb-75   2 1975         81.59   0.02
            2 1975 Total 1521.84   0.38
 3-Mar-75   3 1975         83.03   0.02
 4-Mar-75   3 1975         83.56   0.02
 5-Mar-75   3 1975          83.9   0.02
 6-Mar-75   3 1975         83.69   0.02
 7-Mar-75   3 1975          84.3   0.03
10-Mar-75   3 1975         84.95   0.03
11-Mar-75   3 1975         84.36   0.03
12-Mar-75   3 1975         83.59   0.03
13-Mar-75   3 1975         83.74   0.03
14-Mar-75   3 1975         84.76   0.03
17-Mar-75   3 1975         86.01   0.03
18-Mar-75   3 1975         85.13   0.03
19-Mar-75   3 1975         84.34   0.03
20-Mar-75   3 1975         83.61   0.03
21-Mar-75   3 1975         83.39   0.03
24-Mar-75   3 1975         81.42   0.03
25-Mar-75   3 1975         82.06   0.03
26-Mar-75   3 1975         83.59   0.03
27-Mar-75   3 1975         83.85   0.03
31-Mar-75   3 1975         83.36   0.03
            3 1975 Total 1676.64   0.56
 1-Apr-75   4 1975         82.64   0.03
 2-Apr-75   4 1975         82.43   0.03
 3-Apr-75   4 1975         81.51   0.03
 4-Apr-75   4 1975         80.88   0.03
 7-Apr-75   4 1975         80.35   0.03
 8-Apr-75   4 1975         80.99   0.03
 9-Apr-75   4 1975         82.84   0.03
10-Apr-75   4 1975         83.77   0.03
11-Apr-75   4 1975         84.18   0.03
14-Apr-75   4 1975          85.6   0.03
15-Apr-75   4 1975          86.3   0.03
16-Apr-75   4 1975          86.6   0.03
17-Apr-75   4 1975         87.25   0.03
18-Apr-75   4 1975          86.3   0.03
21-Apr-75   4 1975         87.23   0.03
22-Apr-75   4 1975         87.09   0.03
23-Apr-75   4 1975         86.12   0.03
24-Apr-75   4 1975         86.04   0.03
25-Apr-75   4 1975         86.62   0.03
28-Apr-75   4 1975         86.23   0.03
29-Apr-75   4 1975         85.64   0.03
30-Apr-75   4 1975          87.3   0.03
            4 1975 Total 1863.91   0.66
 1-May-75   5 1975          88.1   0.03
 2-May-75   5 1975         89.22   0.03
 5-May-75   5 1975         90.08   0.03
 6-May-75   5 1975         88.64   0.03
 7-May-75   5 1975         89.08   0.03
 8-May-75   5 1975         89.56   0.03
 9-May-75   5 1975         90.53   0.03
########    5 1975         90.61   0.03
########    5 1975         91.58   0.03
########    5 1975         92.27   0.03
########    5 1975         91.41   0.03
########    5 1975         90.43   0.03
########    5 1975         90.53   0.03
########    5 1975         90.07   0.03
########    5 1975         89.06   0.03
########    5 1975         89.39   0.03
########    5 1975         90.58   0.03
########    5 1975         90.34   0.03
########    5 1975         89.71   0.03
########    5 1975         89.68   0.03
########    5 1975         91.15   0.03
            5 1975 Total 1892.02   0.63
 2-Jun-75   6 1975         92.58   0.03
 3-Jun-75   6 1975         92.89   0.03
 4-Jun-75   6 1975          92.6   0.04
 5-Jun-75   6 1975         92.69   0.03
 6-Jun-75   6 1975         92.48   0.03
 9-Jun-75   6 1975         91.21   0.03
10-Jun-75   6 1975         90.44   0.03
11-Jun-75   6 1975         90.55   0.03
12-Jun-75   6 1975         90.08   0.03
13-Jun-75   6 1975         90.52   0.03
16-Jun-75   6 1975         91.46   0.03
17-Jun-75   6 1975         90.58   0.04
18-Jun-75   6 1975         90.39   0.04
19-Jun-75   6 1975         92.02   0.04
20-Jun-75   6 1975         92.61   0.04
23-Jun-75   6 1975         93.62   0.04
24-Jun-75   6 1975         94.19   0.04
25-Jun-75   6 1975         94.62   0.04
26-Jun-75   6 1975         94.81   0.04
27-Jun-75   6 1975         94.81   0.04
30-Jun-75 6 1975          95.19   0.04
           6 1975 Total 1940.34   0.74
  1-Jul-75 7 1975         94.85   0.04
  2-Jul-75 7 1975         94.18   0.04
  3-Jul-75 7 1975         94.36   0.04
  7-Jul-75 7 1975         93.54   0.04
  8-Jul-75 7 1975         93.39   0.04
  9-Jul-75 7 1975          94.8   0.04
 10-Jul-75 7 1975         94.81   0.04
 11-Jul-75 7 1975         94.66   0.04
 14-Jul-75 7 1975         95.19   0.04
 15-Jul-75 7 1975         95.61   0.04
 16-Jul-75 7 1975         94.61   0.04
 17-Jul-75 7 1975         93.63   0.04
 18-Jul-75 7 1975          93.2   0.04
 21-Jul-75 7 1975         92.44   0.04
 22-Jul-75 7 1975         91.45   0.04
 23-Jul-75 7 1975         90.18   0.04
 24-Jul-75 7 1975         90.07   0.04
 25-Jul-75 7 1975         89.29   0.04
           7 1975 Total 1680.26   0.72
           Grand Total12171.42    4.12
Table of Contents

                    Sample Data

                         -100          20         50         40         25          30        100




                    The example below compares the match function to the payback fucntion


                         -100          25         25         25         25          25         25
                         -100         -75        -50        -25          0          25         50




                    To copy the functions to another sheet
                    1. Make macro in anohter sheet if it does not already exist
                    2. Edit the macro in the new sheet
                    3.Use the window in the new sheet and switch the window to FM.
                    4. Once in the Functions window use the CNTL-A to select the entire module named functions
                    5. Go to the window of the new sheet
                    6. Use the CNTL-C and copy and paste all of the functions just below the macro in the new she
                       Payback Function

                       =payback(C5:I5)-->       2.75

                       Discount Rate            15%

                       =dpayback(L7,C5:I5)-->   4.28




                       =MATCH(0,C15:I15)-->        5

                                                   4




e module named functions in this FM workbook

w the macro in the new sheet
XMIRR Function


Test Series                         15    =test_series(C19:Q19)                        use .Count to find the length of the s
Test Loop                        5113     =test_loop(C19:Q19,C14:Q14)                  use the redim statement to define v
Test Loop 1                      5113     =test_loop1(C19:Q19,C14:Q14)                 start with num
xmirr                          14.91%     =xmirr(C19:Q19,C14:Q14,B24,B26)              don’t need redim




Month Increment                                             12         12         12          12          12
Dates                                      1-Jan-10   1-Jan-11   1-Jan-12   1-Jan-13    1-Jan-14    1-Jan-15

Days for Negative Cash Flow         15           0        365        730       1096         1461        1826
Days for Positive Cash Flow                   5113       4748       4383       4017         3652        3287

Cash Flow                                      -400      -300       -100       -200           400         650

IRR                               18%

                              Annual   Daily
Interest Rate for Borrowing       5% 0.000134

Re-investment Rate                 8% 0.000211

Negative                                   TRUE       TRUE       TRUE       TRUE        FALSE       FALSE
Positive                                   FALSE      FALSE      FALSE      FALSE       TRUE        TRUE

NPV of Negative                 -949.16        1.00       0.95       0.91       0.86         0.82        0.78
FV of Positive                 6,648.49        2.94       2.72       2.52       2.33         2.16        2.00
Total Days                         5113
Total Years                   14.00822

Growth Rate                    14.91%
MIRR                           14.91%
  to find the length of the series
dim statement to define variables




                    12          12          12         12         12         12         12         12         12
              1-Jan-16    1-Jan-17    1-Jan-18   1-Jan-19   1-Jan-20   1-Jan-21   1-Jan-22   1-Jan-23   1-Jan-24

                  2191        2557       2922       3287       3652       4018       4383       4748       5113
                  2922        2556       2191       1826       1461       1095        730        365          0

                    900        100        200        400        300        100        500        300        300




               FALSE      FALSE       FALSE      FALSE      FALSE      FALSE      FALSE      FALSE      FALSE
               TRUE       TRUE        TRUE       TRUE       TRUE       TRUE       TRUE       TRUE       TRUE

                   0.75        0.71       0.68       0.64       0.61       0.58       0.56       0.53       0.50
                   1.85        1.71       1.59       1.47       1.36       1.26       1.17       1.08       1.00
Table of Contents


                    Simple Function for Error Presentaion


                    Numerator                                 100

                    Denominator                                0

                    Result                          #DIV/0!

                    =err(F7,"NA")-->                  NA


                    =IRR(G12:I12)-->                #NUM!           0   100

                    =err(F16,"Not Meaningful")   Not Meaningful
      Function err(inp, output)

      Dim test As Boolean

      test = IsError(inp)

100
      If test = True Then err = output

      If test = False Then err = inp


      End Function
Table of Contents
=interpolate()-->            1          0          9


Base Data                    1          0          0           0          5                       7


With Interpolate             1          0          3           0          5          0            7

                             1          2          3                      5          6




Alternative Data     10.33124           0 5.331235             0 25.33124            0 17.33124



                    This function allows you to interpolate between numbers

                    The problem is that the function is not updated and you have to run a macro
         9


0        9



             You can do this with CNTL I



0 9.331235
Table of Contents

          Column Number Functions to Help with Indirect Command and with Macros




          Gives Column Letter of Current Column                    =col_let()-->

          Gives Subsequent Column Letter                           =col_letter(D6)-->

          Gives Column Letter for Col Number                       =column_letter(D8)-->

          Gives the Column Number for the Current Column           =col_number()-->

          The column function in excel                             =COLUMN()-->



                                                                         100
                                                                         200
                                                                         300
                                                                         400

                                                                   ROW
Input         Output

              G

    Z         AA

   200        GR

                        7

                        8



        100            200   300   400




ROW
Table of Contents

                   This illustrates how the IRR_ADJUST function works to be more flexible



                                                                                                   =IRR(.2)

            (10)    10,000,000       1,000,000          5,000           5,000          5,000        99999910%


     (5,000,000)             -              -              -              -         100,000                -54%


     (5,000,000)      1,000,000         10,000        100,000        100,000        100,000                -43%



                   Note if there are negative, then positve, then negative cash flows, this may not work
to be more flexible than the regular IRR



       =IRR(-.2)                       IRR_ADJUST

        99999910%


             -54% =irr_adjust(B13:G13)--> -0.542694926


             -43%                        -0.432379633
Table of Contents



                               Function and Macros to Show Formulas


                                                100

                                                200                20000




                    CNTL - L               =F10*F8-->              20000

                    CNTL - K                          =F10*F8-->   20000
                     F Function

=F10*F8              =f(H10)




          Function f(var1)

          f = var1.Formula

          End Function
            Month and Year   S&P 500 WMT
 2-Jan-75   1 1975               70.23   0.02
 3-Jan-75   1 1975               70.71   0.01   In this Example, the column numb
 6-Jan-75   1 1975               71.07   0.02
 7-Jan-75   1 1975               71.02   0.02
 8-Jan-75   1 1975               70.04   0.02
 9-Jan-75   1 1975               71.17   0.02
10-Jan-75   1 1975               72.61   0.02
13-Jan-75   1 1975               72.31   0.02
14-Jan-75   1 1975               71.68   0.02
15-Jan-75   1 1975               72.14   0.02
16-Jan-75   1 1975               72.05   0.02
17-Jan-75   1 1975               70.96   0.02
20-Jan-75   1 1975               71.08   0.02
21-Jan-75   1 1975                70.7   0.02
22-Jan-75   1 1975               71.74   0.02
23-Jan-75   1 1975               72.07   0.02
24-Jan-75   1 1975               72.98   0.02
27-Jan-75   1 1975               75.37   0.02
28-Jan-75   1 1975               76.03   0.02
29-Jan-75   1 1975               77.26   0.02
30-Jan-75   1 1975               76.21   0.02
31-Jan-75   1 1975               76.98   0.02
            1 1975 Total       1596.41   0.43
 3-Feb-75   2 1975               77.82   0.02
 4-Feb-75   2 1975               77.61   0.02
 5-Feb-75   2 1975               78.95   0.02
 6-Feb-75   2 1975               78.56   0.02
 7-Feb-75   2 1975               78.63   0.02
10-Feb-75   2 1975               78.36   0.02
11-Feb-75   2 1975               78.58   0.02
12-Feb-75   2 1975               79.92   0.02
13-Feb-75   2 1975               81.01   0.02
14-Feb-75   2 1975                81.5   0.02
18-Feb-75   2 1975               80.93   0.02
19-Feb-75   2 1975               81.44   0.02
20-Feb-75   2 1975               82.21   0.02
21-Feb-75   2 1975               82.62   0.02
24-Feb-75   2 1975               81.44   0.02
25-Feb-75   2 1975               79.53   0.02
26-Feb-75   2 1975               80.37   0.02
27-Feb-75   2 1975               80.77   0.02
28-Feb-75   2 1975               81.59   0.02
            2 1975 Total       1521.84   0.38
 3-Mar-75   3 1975               83.03   0.02
 4-Mar-75   3 1975               83.56   0.02
 5-Mar-75   3 1975                83.9   0.02
 6-Mar-75   3 1975               83.69   0.02
 7-Mar-75   3 1975                84.3   0.03
10-Mar-75   3 1975           84.95   0.03
11-Mar-75   3 1975           84.36   0.03
12-Mar-75   3 1975           83.59   0.03
13-Mar-75   3 1975           83.74   0.03
14-Mar-75   3 1975           84.76   0.03
17-Mar-75   3 1975           86.01   0.03
18-Mar-75   3 1975           85.13   0.03
19-Mar-75   3 1975           84.34   0.03
20-Mar-75   3 1975           83.61   0.03
21-Mar-75   3 1975           83.39   0.03
24-Mar-75   3 1975           81.42   0.03
25-Mar-75   3 1975           82.06   0.03
26-Mar-75   3 1975           83.59   0.03
27-Mar-75   3 1975           83.85   0.03
31-Mar-75   3 1975           83.36   0.03
            3 1975 Total   1676.64   0.56
 1-Apr-75   4 1975           82.64   0.03
 2-Apr-75   4 1975           82.43   0.03
 3-Apr-75   4 1975           81.51   0.03
 4-Apr-75   4 1975           80.88   0.03
 7-Apr-75   4 1975           80.35   0.03
 8-Apr-75   4 1975           80.99   0.03
 9-Apr-75   4 1975           82.84   0.03
10-Apr-75   4 1975           83.77   0.03
11-Apr-75   4 1975           84.18   0.03
14-Apr-75   4 1975            85.6   0.03
15-Apr-75   4 1975            86.3   0.03
16-Apr-75   4 1975            86.6   0.03
17-Apr-75   4 1975           87.25   0.03
18-Apr-75   4 1975            86.3   0.03
21-Apr-75   4 1975           87.23   0.03
22-Apr-75   4 1975           87.09   0.03
23-Apr-75   4 1975           86.12   0.03
24-Apr-75   4 1975           86.04   0.03
25-Apr-75   4 1975           86.62   0.03
28-Apr-75   4 1975           86.23   0.03
29-Apr-75   4 1975           85.64   0.03
30-Apr-75   4 1975            87.3   0.03
            4 1975 Total   1863.91   0.66
 1-May-75   5 1975            88.1   0.03
 2-May-75   5 1975           89.22   0.03
 5-May-75   5 1975           90.08   0.03
 6-May-75   5 1975           88.64   0.03
 7-May-75   5 1975           89.08   0.03
 8-May-75   5 1975           89.56   0.03
 9-May-75   5 1975           90.53   0.03
12-May-75   5 1975           90.61   0.03
13-May-75   5 1975           91.58   0.03
14-May-75   5 1975           92.27   0.03
15-May-75   5 1975           91.41   0.03
16-May-75   5 1975           90.43   0.03
19-May-75   5 1975           90.53   0.03
20-May-75    5 1975            90.07   0.03
21-May-75    5 1975            89.06   0.03
22-May-75    5 1975            89.39   0.03
23-May-75    5 1975            90.58   0.03
27-May-75    5 1975            90.34   0.03
28-May-75    5 1975            89.71   0.03
29-May-75    5 1975            89.68   0.03
30-May-75    5 1975            91.15   0.03
             5 1975 Total    1892.02   0.63
 2-Jun-75    6 1975            92.58   0.03
 3-Jun-75    6 1975            92.89   0.03
 4-Jun-75    6 1975             92.6   0.04
 5-Jun-75    6 1975            92.69   0.03
 6-Jun-75    6 1975            92.48   0.03
 9-Jun-75    6 1975            91.21   0.03
10-Jun-75    6 1975            90.44   0.03
11-Jun-75    6 1975            90.55   0.03
12-Jun-75    6 1975            90.08   0.03
13-Jun-75    6 1975            90.52   0.03
16-Jun-75    6 1975            91.46   0.03
17-Jun-75    6 1975            90.58   0.04
18-Jun-75    6 1975            90.39   0.04
19-Jun-75    6 1975            92.02   0.04
20-Jun-75    6 1975            92.61   0.04
23-Jun-75    6 1975            93.62   0.04
24-Jun-75    6 1975            94.19   0.04
25-Jun-75    6 1975            94.62   0.04
26-Jun-75    6 1975            94.81   0.04
27-Jun-75    6 1975            94.81   0.04
30-Jun-75    6 1975            95.19   0.04
             6 1975 Total    1940.34   0.74
  1-Jul-75   7 1975            94.85   0.04
  2-Jul-75   7 1975            94.18   0.04
  3-Jul-75   7 1975            94.36   0.04
  7-Jul-75   7 1975            93.54   0.04
  8-Jul-75   7 1975            93.39   0.04
  9-Jul-75   7 1975             94.8   0.04
 10-Jul-75   7 1975            94.81   0.04
 11-Jul-75   7 1975            94.66   0.04
 14-Jul-75   7 1975            95.19   0.04
 15-Jul-75   7 1975            95.61   0.04
 16-Jul-75   7 1975            94.61   0.04
 17-Jul-75   7 1975            93.63   0.04
 18-Jul-75   7 1975             93.2   0.04
 21-Jul-75   7 1975            92.44   0.04
 22-Jul-75   7 1975            91.45   0.04
 23-Jul-75   7 1975            90.18   0.04
 24-Jul-75   7 1975            90.07   0.04
 25-Jul-75   7 1975            89.29   0.04
             7 1975 Total    1680.26   0.72
             Grand Total    12171.42   4.12
                                          Show Comments




In this Example, the column number is 1
                                                              Senstivity

                                                        CF Sens      Inv Sens

                                                               200         -1000
                                                               210         -1100
                                                               220         -1200
              Show Comments                                    230         -1300
                                                               240         -1400

         Model with two inputs and one output
Input Driver --> Investment          -1000                           Test for Entering Sensitivity Variables as
Input Driver --> Cash Flow             200
                                       200              CF Sens              200         210
                                       200              Inv Sens           -1000       -1100
                                       200
                                       200
                                       200

Output ------->   IRR                  5%
                                                           200.00       210.00       220.00
                                                -1000       5.47%        7.03%        8.56%
                                                -1100       2.54%        4.02%        5.47%
                                                -1200       0.00%        1.41%        2.79%
                                                -1300      -2.24%       -0.89%        0.44%
                                                -1400      -4.23%       -2.93%       -1.66%
'Two Way Data Table'!$G$7:$G$11
'Two Way Data Table'!$H$7:$H$11
'Two Way Data Table'!$D$22
'Two Way Data Table'!$D$15
'Two Way Data Table'!$D$14
tering Sensitivity Variables as Rows

                     220         230     240
                   -1200       -1300   -1400




                 230.00      240.00
                 10.06%      11.53%
                  6.89%       8.28%            This marcro allows you to create data tables that do not have the excel problem
                  4.15%       5.47%
                  1.73%       3.00%            You can copy this macro multiple times for multiple tables
                 -0.41%       0.81%
                                               To copy, you must copy both the macro and the userform named TABLE1
                                               You must also copy a series of functions including COL_FORMAT, GET_SHEE
 that do not have the excel problems

multiple tables

 the userform named TABLE1
 uding COL_FORMAT, GET_SHEET, COL_LETTER, ROW_NUMBER
Table of Contents

                                            Investment Scenarios
             Show Comments
                                                  -800               -800          -900         -1000
                                                  -900
                                                 -1000
             Model for Example                   -1100
          Investment        -800                 -1200
          Cash Flow          210                 -1300                      IRR           NPV
          Cash Flow          210                 -1400
          Cash Flow          210                                     -800         14.8%      104.19
          Cash Flow          210                                     -900         10.6%       13.28
          Cash Flow          210                                    -1000          7.0%      -77.63
          Cash Flow          210                                    -1100          4.0%     -168.54
                                                                    -1200          1.4%     -259.45
          IRR                    15%                                -1300         -0.9%     -350.36
          Disc Rate              10%                                -1400         -2.9%     -441.27
          NPV                $104.19
          Sum                     460
          Payback                3.81
          Std Dev              381.74


          IRR             NPV      Sum     Payback    St Dev
                    15%    $104.19     460       3.81      381.74

                                                                            IRR           NPV
                                                                                    15%    $104.19
                                                                     -800         14.8%      104.19
                                                                     -900         10.6%       13.28
                                                                    -1000          7.0%      -77.63
                                                                    -1100          4.0%     -168.54
                                                                    -1200          1.4%     -259.45
                                                                    -1300         -0.9%     -350.36
                                                                    -1400         -2.9%     -441.27
'One Way Data Table'!$E$5:$E$11
'One Way Data Table'!$B$26:$F$26
'One Way Data Table'!$H$12
'One Way Data Table'!$C$9
      -1100      -1200        -1300   -1400




Sum           Payback     St Dev
                                              Note: to copy, you must compay functions along with the macro and the useform
   460.00          3.81      381.74           You must also copy a series of functions including COL_FORMAT, GET_SHEET
   360.00          4.29      419.54
   260.00          4.76      457.34           To copy a whole macro, you can double click on it
   160.00          8.00      495.13
    60.00          8.00      532.93           Uses TABLE1 Form
   -40.00          8.00      570.73
  -140.00          8.00      608.52




Sum       Payback St Dev
   460.00     3.81    381.74
   460.00      3.81   381.74
   360.00      4.29   419.54
   260.00      4.76   457.34
   160.00      8.00   495.13
    60.00      8.00   532.93
   -40.00      8.00   570.73
  -140.00      8.00   608.52
ong with the macro and the useform TABLE1
uding COL_FORMAT, GET_SHEET, COL_LETTER, ROW_NUMBER
'One Way Data Table'!$B$26:$F$26
'One Way Data Table'!$H$12
'One Way Data Table'!$E$5:$E$11
'One Way Data Table'!$C$9
Table of Contents


                    Show Comments


                                    Investment   -773.49
                                    Cash          150.00
                                                  150.00
                                                  150.00
                                                  150.00
                                                  150.00
                                                  150.00
                                    Terminal      244.75

                                    IRR          10.20%

                                    Target IRR   10.20%


                                    Change w/o Macro
0
With Error

  -773.49
   150.00
   150.00
   150.00
   150.00
   150.00
   150.00
   244.75

  10.00% =10%

  10.20%
Table of Contents


                    test hh'!$M$32:$M$2677

                          10               =FIND("!",D4,1)+1 find_exp
                    $M$32:$M$2677          =MID(D4,D6,20)    second_part
                           2               =FIND("$",D7,1)+1 start_num
                           4               =FIND(":",D7,1)-2 end_num
                    M$32                   =MID(D7,D8,D9)    first_range
                    M32                    =SUBSTITUTE(D10,"$","")
                           8               =FIND(":",D7,1)+2 second_start
                    M$2677                 =MID(D7,D12,100)  second_range

                    M          Loop until isnumber is true          find_number()
                    char                                            find_letter()
                      FALSE


                    If both letters are the same, then col format

                    If both numbers are the same, then row format
2677
Table of Contents

                    This shows how to find the sheet name

                                                                                         PRESS F2 Key if N/A is in Cells

                    =CELL("filename")-->                                                 C:\Docstoc\Working\pdf\[e6793

                    =FIND("]",E4,1)-->                                                          65

                    =MID(E4,E6+1,50)-->                                                  Make Disclaimer Page


                    bonjour filename

                    =FIND("]",CELL("filename"),1)-->                                            65


                    =MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,250)-->         Make Disclaimer Page




                                                       Copy this formula to your sheet
PRESS F2 Key if N/A is in Cells

C:\Docstoc\Working\pdf\[e6793508-28c6-4686-aa5e-37f7156a459f.xls]Make Disclaimer Page



Make Disclaimer Page




Make Disclaimer Page
Periods

  Current Date              27-Sep-12
  Periods per Year                 12

  Fiscal Year Month End            9
  Interest Rate                   8%

  Period                                                   0             1             2             3
  Months per Period                                        1             1             1             1
  Begin Date                                       27-Sep-12     27-Oct-12     27-Nov-12     27-Dec-12
  End Date                                         26-Oct-12     26-Nov-12     26-Dec-12     26-Jan-13
  Year                                                  2012          2012          2012          2013
  Fiscal Year End                                  FALSE         FALSE         FALSE         FALSE
  Year at Fiscal Year End                                  0             0             0             0

  Drawdowns - Cash Flow                                 100           110           121           133

  Opening Balance                                       -             100           210           331
  Add: Draws                                            100           110           121           133
  Closing                                               100           210           331           464

  Annual Interest Rate                                      8%         8%            8%            8%
  Periodic Interest                                     -            0.67          1.40          2.21

  Year                      Balance     Row            2009          2010          2011          2012

  Opening Balance            TRUE             20        -             -             -             -
  Add: Draws                 FALSE            21        -             -             -             331
  Closing                    TRUE             22        -             -             -             -

  Annual Interest Rate       TRUE             24            0%            0%            0%         0%
  Periodic Interest          FALSE            25        -             -             -               2
        4           5           6            7           8            9           10
        1           1           1            1           1            1            1
27-Jan-13   27-Feb-13   27-Mar-13    27-Apr-13   27-May-13   27-Jun-13     27-Jul-13
26-Feb-13   26-Mar-13   26-Apr-13   26-May-13    26-Jun-13    26-Jul-13   26-Aug-13
     2013        2013       2013         2013         2013        2013         2013
FALSE       FALSE       FALSE       FALSE        FALSE       FALSE        FALSE
        0           0           0            0           0            0            0

     146         161         177          195         214          236          259

     464         611         772          949       1,144       1,358        1,594
     146         161         177          195         214         236          259
     611         772         949        1,144       1,358       1,594        1,853

      8%          8%          8%           8%          8%           8%          8%
    3.09        4.07        5.14         6.32        7.62         9.05       10.62

    2013        2014        2015         2016        2017         2018         2019

     -         2,138       8,850      29,913       96,017     303,482       954,594
   2,846       8,933      28,035      87,985      276,135     866,630     2,719,857
   2,138       8,850      29,913      96,017      303,482     954,594     2,998,063

      8%          8%          8%           8%          8%          8%           8%
     110         516       1,789        5,786      18,329      57,695      181,244
       11          12          13            14            15            16          17
        1           1           1             1             1             1           1
27-Aug-13   27-Sep-13   27-Oct-13     27-Nov-13     27-Dec-13     27-Jan-14   27-Feb-14
26-Sep-13   26-Oct-13   26-Nov-13     26-Dec-13     26-Jan-14     26-Feb-14   26-Mar-14
     2013        2013        2013          2013          2014          2014        2014
TRUE        FALSE       FALSE         FALSE         FALSE         FALSE       FALSE
     2013           0           0             0             0             0           0

      285         314        345           380           418           459         505

   1,853       2,138       2,452         2,797         3,177         3,595       4,054
     285         314         345           380           418           459         505
   2,138       2,452       2,797         3,177         3,595         4,054       4,560

      8%          8%          8%            8%            8%            8%          8%
   12.35       14.26       16.35         18.65         21.18         23.97       27.03

     2020        2021       2022          2023          2024

2,998,063   9,411,344        -             -             -
8,536,077   2,630,844        -             -             -
9,411,344         -          -             -             -

      8%          0%             0%            0%            0%
 568,992     175,376         -             -             -
       18           19          20           21           22          23          24
        1            1           1            1            1           1           1
27-Mar-14    27-Apr-14   27-May-14   27-Jun-14     27-Jul-14   27-Aug-14   27-Sep-14
26-Apr-14   26-May-14    26-Jun-14    26-Jul-14   26-Aug-14    26-Sep-14   26-Oct-14
    2014         2014         2014        2014         2014         2014        2014
FALSE       FALSE        FALSE       FALSE        FALSE        TRUE        FALSE
        0            0           0            0            0        2014           0

     556          612         673          740          814         895         985

   4,560        5,116       5,727       6,400        7,140        7,954       8,850
     556          612         673         740          814          895         985
   5,116        5,727       6,400       7,140        7,954        8,850       9,835

      8%           8%          8%          8%           8%           8%          8%
   30.40        34.11       38.18       42.67        47.60        53.03       59.00
       25          26          27          28          29          30           31
        1           1           1           1           1           1            1
27-Oct-14   27-Nov-14   27-Dec-14   27-Jan-15   27-Feb-15   27-Mar-15    27-Apr-15
26-Nov-14   26-Dec-14   26-Jan-15   26-Feb-15   26-Mar-15   26-Apr-15   26-May-15
     2014        2014        2015        2015        2015       2015         2015
FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE
        0           0           0           0           0           0            0

    1,083       1,192       1,311       1,442       1,586       1,745       1,919

   9,835      10,918      12,110      13,421      14,863      16,449      18,194
   1,083       1,192       1,311       1,442       1,586       1,745       1,919
  10,918      12,110      13,421      14,863      16,449      18,194      20,114

      8%          8%          8%          8%          8%          8%          8%
   65.56       72.79       80.73       89.47       99.09      109.66      121.30
       32           33           34          35          36          37          38
        1            1            1           1           1           1           1
27-May-15   27-Jun-15     27-Jul-15   27-Aug-15   27-Sep-15   27-Oct-15   27-Nov-15
26-Jun-15    26-Jul-15   26-Aug-15    26-Sep-15   26-Oct-15   26-Nov-15   26-Dec-15
     2015        2015         2015         2015        2015        2015        2015
FALSE       FALSE        FALSE        TRUE        FALSE       FALSE       FALSE
        0            0            0        2015           0           0           0

    2,111       2,323        2,555        2,810       3,091       3,400       3,740

  20,114      22,225       24,548       27,102      29,913      33,004      36,404
   2,111       2,323        2,555        2,810       3,091       3,400       3,740
  22,225      24,548       27,102       29,913      33,004      36,404      40,145

      8%          8%           8%           8%          8%          8%          8%
  134.09      148.17       163.65       180.68      199.42      220.03      242.70
       39          40          41          42           43          44           45
        1           1           1           1            1           1            1
27-Dec-15   27-Jan-16   27-Feb-16   27-Mar-16    27-Apr-16   27-May-16   27-Jun-16
26-Jan-16   26-Feb-16   26-Mar-16   26-Apr-16   26-May-16    26-Jun-16    26-Jul-16
     2016        2016        2016       2016         2016         2016        2016
FALSE       FALSE       FALSE       FALSE       FALSE        FALSE       FALSE
        0           0           0           0            0           0            0

    4,114       4,526       4,979       5,476       6,024        6,626       7,289

  40,145      44,259      48,785      53,764      59,240       65,264      71,890
   4,114       4,526       4,979       5,476       6,024        6,626       7,289
  44,259      48,785      53,764      59,240      65,264       71,890      79,180

      8%          8%          8%          8%          8%           8%          8%
  267.63      295.06      325.23      358.42      394.93       435.09      479.27
        46          47          48          49          50          51          52
         1           1           1           1           1           1           1
 27-Jul-16   27-Aug-16   27-Sep-16   27-Oct-16   27-Nov-16   27-Dec-16   27-Jan-17
26-Aug-16    26-Sep-16   26-Oct-16   26-Nov-16   26-Dec-16   26-Jan-17   26-Feb-17
     2016         2016        2016        2016        2016        2017        2017
FALSE        TRUE        FALSE       FALSE       FALSE       FALSE       FALSE
         0        2016           0           0           0           0           0

    8,018        8,820       9,702      10,672      11,739      12,913      14,204

  79,180       87,197      96,017     105,719     116,391     128,130     141,043
   8,018        8,820       9,702      10,672      11,739      12,913      14,204
  87,197       96,017     105,719     116,391     128,130     141,043     155,247

      8%           8%          8%          8%          8%          8%          8%
  527.86       581.32      640.11      704.79      775.94      854.20      940.29
       53          54           55          56           57           58          59
        1           1            1           1            1            1           1
27-Feb-17   27-Mar-17    27-Apr-17   27-May-17   27-Jun-17     27-Jul-17   27-Aug-17
26-Mar-17   26-Apr-17   26-May-17    26-Jun-17    26-Jul-17   26-Aug-17    26-Sep-17
     2017       2017         2017         2017        2017         2017         2017
FALSE       FALSE       FALSE        FALSE       FALSE        FALSE        TRUE
        0           0            0           0            0            0        2017

   15,625      17,187      18,906       20,797      22,876       25,164       27,680

 155,247     170,872     188,059      206,965     227,762      250,638      275,801
  15,625      17,187      18,906       20,797      22,876       25,164       27,680
 170,872     188,059     206,965      227,762     250,638      275,801      303,482

      8%          8%           8%           8%          8%           8%          8%
1,034.98    1,139.15     1,253.73     1,379.77    1,518.41     1,670.92    1,838.68
       60          61          62          63          64          65          66
        1           1           1           1           1           1           1
27-Sep-17   27-Oct-17   27-Nov-17   27-Dec-17   27-Jan-18   27-Feb-18   27-Mar-18
26-Oct-17   26-Nov-17   26-Dec-17   26-Jan-18   26-Feb-18   26-Mar-18   26-Apr-18
     2017        2017        2017        2018        2018        2018       2018
FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE
        0           0           0           0           0           0           0

   30,448      33,493      36,842      40,527      44,579      49,037      53,941

 303,482     333,930     367,423     404,265     444,792     489,371     538,408
  30,448      33,493      36,842      40,527      44,579      49,037      53,941
 333,930     367,423     404,265     444,792     489,371     538,408     592,349

      8%          8%          8%          8%          8%          8%          8%
2,023.21    2,226.20    2,449.49    2,695.10    2,965.28    3,262.47    3,589.39
        67          68           69           70          71          72          73
         1           1            1            1           1           1           1
 27-Apr-18   27-May-18   27-Jun-18     27-Jul-18   27-Aug-18   27-Sep-18   27-Oct-18
26-May-18    26-Jun-18    26-Jul-18   26-Aug-18    26-Sep-18   26-Oct-18   26-Nov-18
     2018         2018        2018         2018         2018        2018        2018
FALSE        FALSE       FALSE        FALSE        TRUE        FALSE       FALSE
         0           0            0            0        2018           0           0

   59,335       65,268      71,795       78,975       86,872      95,559     105,115

 592,349      651,683     716,952      788,747      867,722      954,594   1,050,153
  59,335       65,268      71,795       78,975       86,872       95,559     105,115
 651,683      716,952     788,747      867,722      954,594    1,050,153   1,155,269

       8%           8%          8%           8%          8%           8%          8%
 3,948.99     4,344.56    4,779.68     5,258.31    5,784.81     6,363.96    7,001.02
       74          75          76          77          78           79          80
        1           1           1           1           1            1           1
27-Nov-18   27-Dec-18   27-Jan-19   27-Feb-19   27-Mar-19    27-Apr-19   27-May-19
26-Dec-18   26-Jan-19   26-Feb-19   26-Mar-19   26-Apr-19   26-May-19    26-Jun-19
     2018        2019        2019        2019       2019         2019         2019
FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE
        0           0           0           0           0            0           0

  115,627     127,190     139,908     153,899     169,289     186,218      204,840

1,155,269   1,270,895   1,398,085   1,537,993   1,691,893   1,861,182    2,047,400
  115,627     127,190     139,908     153,899     169,289     186,218      204,840
1,270,895   1,398,085   1,537,993   1,691,893   1,861,182   2,047,400    2,252,240

       8%          8%          8%          8%          8%          8%           8%
 7,701.79    8,472.64    9,320.57   10,253.29   11,279.28   12,407.88    13,649.33
        81           82          83          84          85          86          87
         1            1           1           1           1           1           1
27-Jun-19     27-Jul-19   27-Aug-19   27-Sep-19   27-Oct-19   27-Nov-19   27-Dec-19
 26-Jul-19   26-Aug-19    26-Sep-19   26-Oct-19   26-Nov-19   26-Dec-19   26-Jan-20
     2019         2019         2019        2019        2019        2019        2020
FALSE        FALSE        TRUE        FALSE       FALSE       FALSE       FALSE
         0            0        2019           0           0           0           0

  225,324      247,856      272,642     299,906     329,897     362,887     399,175

2,252,240    2,477,564    2,725,421   2,998,063   3,297,969   3,627,866   3,990,753
  225,324      247,856      272,642     299,906     329,897     362,887     399,175
2,477,564    2,725,421    2,998,063   3,297,969   3,627,866   3,990,753   4,389,928

       8%           8%           8%          8%          8%          8%          8%
15,014.93    16,517.10    18,169.47   19,987.09   21,986.46   24,185.77   26,605.02
       88          89          90           91          92           93           94
        1           1           1            1           1            1            1
27-Jan-20   27-Feb-20   27-Mar-20    27-Apr-20   27-May-20   27-Jun-20     27-Jul-20
26-Feb-20   26-Mar-20   26-Apr-20   26-May-20    26-Jun-20    26-Jul-20   26-Aug-20
     2020        2020       2020         2020         2020        2020         2020
FALSE       FALSE       FALSE       FALSE        FALSE       FALSE        FALSE
        0           0           0            0           0            0            0

  439,093     483,002     531,302     584,432      642,876     707,163      777,880

4,389,928   4,829,021   5,312,023   5,843,325    6,427,757   7,070,633    7,777,796
  439,093     483,002     531,302     584,432      642,876     707,163      777,880
4,829,021   5,312,023   5,843,325   6,427,757    7,070,633   7,777,796    8,555,676

       8%          8%          8%          8%           8%          8%           8%
29,266.19   32,193.47   35,413.48   38,955.50    42,851.72   47,137.55    51,851.98
       95           96           97           98           99          100
        1            1            1            1            1            1
27-Aug-20    27-Sep-20    27-Oct-20    27-Nov-20    27-Dec-20    27-Jan-21
26-Sep-20    26-Oct-20    26-Nov-20    26-Dec-20    26-Jan-21    26-Feb-21
     2020         2020         2020         2020         2021         2021
TRUE         FALSE        FALSE        FALSE        FALSE        FALSE
     2020            0            0            0            0            0

  855,668      941,234    1,035,358    1,138,894    1,252,783    1,378,061

8,555,676    9,411,344   10,352,578   11,387,936   12,526,829   13,779,612
  855,668      941,234    1,035,358    1,138,894    1,252,783    1,378,061
9,411,344   10,352,578   11,387,936   12,526,829   13,779,612   15,157,674

       8%           8%           8%           8%           8%           8%
57,037.84    62,742.29    69,017.19    75,919.57    83,512.20    91,864.08
Table of Contents           Nasdaq Stock Prices




Date       Open     High     Low      Close       Volume    Adj_Close
 2-Jun-97 1407.07 1467.29 1375.45 1442.07          6.29E+08 1442.07     81
   1-Jul-97 1442.65 1595.76 1432.42 1593.81        6.82E+08 1593.81      1 Sum
 1-Aug-97 1594.67 1638.26 1545.13 1587.32          6.63E+08 1587.32     72 St Dev
 2-Sep-97 1595.07 1702.49 1595.07 1685.69          7.36E+08 1685.69      7 Vol
  1-Oct-97 1690.79 1748.78 1465.84 1593.61          8.5E+08 1593.61
 3-Nov-97 1609.62 1654.43 1508.41 1600.55          6.26E+08 1600.55
 1-Dec-97 1608.56 1652.84 1486.51 1570.35          7.15E+08 1570.35
 2-Jan-98     1574.1 1629.54 1465.61 1619.36       7.23E+08 1619.36
 2-Feb-98 1640.06 1783.74 1640.06 1770.51          8.09E+08 1770.51
 2-Mar-98 1778.72 1840.83 1708.48 1835.68           8.1E+08 1835.68
  1-Apr-98 1838.15 1931.83 1788.74 1868.41         8.64E+08 1868.41
 1-May-98 1871.81      1890.8 1742.05 1778.87      7.52E+08 1778.87
 1-Jun-98 1770.37 1898.62 1715.04 1894.74          7.62E+08 1894.74
   1-Jul-98 1904.24 2028.18 1871.72 1872.39        8.34E+08 1872.39
 3-Aug-98 1869.72 1874.31 1498.73 1499.25          7.98E+08 1499.25
 1-Sep-98 1509.01 1769.71 1475.49 1693.84          7.92E+08 1693.84
  1-Oct-98    1663.3 1781.63 1343.87 1771.39       9.24E+08 1771.39
 2-Nov-98 1783.71 2025.04       1771.4 1949.54      9.4E+08 1949.54
 1-Dec-98 1928.51 2200.63 1924.15 2192.69          9.07E+08 2192.69
 4-Jan-99 2207.54 2506.68 2192.68 2505.89          1.14E+09 2505.89
 1-Feb-99 2522.38 2533.44 2224.21 2288.03          9.65E+08 2288.03
 1-Mar-99 2286.83 2520.63 2235.19        2461.4    9.69E+08    2461.4
  1-Apr-99 2493.07 2677.76 2329.87 2542.86         1.16E+09 2542.86
 3-May-99 2546.33 2632.74 2339.12 2470.52           9.6E+08 2470.52
 1-Jun-99 2467.51 2696.87 2364.59 2686.12          9.52E+08 2686.12
   1-Jul-99 2692.96 2874.92 2619.08 2638.49        1.04E+09 2638.49
 2-Aug-99 2638.31      2819.9 2442.22 2739.35      9.64E+08 2739.35
 1-Sep-99 2752.33 2897.53       2684.7 2746.16     1.07E+09 2746.16
  1-Oct-99 2729.04 2978.63 2632.01 2966.43         1.15E+09 2966.43
 1-Nov-99 2970.93 3469.35 2967.63 3336.16          1.44E+09 3336.16
 1-Dec-99     3341.1 4090.61 3321.57 4069.31       1.45E+09 4069.31
 3-Jan-00 4186.19 4303.15 3711.09 3940.35          1.75E+09 3940.35
 1-Feb-00 3961.07 4698.46 3911.84 4696.69          1.89E+09 4696.69
 1-Mar-00 4732.82 5132.52 4355.69 4572.83          1.97E+09 4572.83
  3-Apr-00 4494.89 4572.84 3227.04 3860.66         1.93E+09 3860.66
 1-May-00 3930.18 3982.38 3042.66 3400.91          1.47E+09 3400.91
 1-Jun-00 3471.95 4073.73 3459.85 3966.11          1.61E+09 3966.11
   3-Jul-00 3950.59 4289.06 3615.79 3766.99        1.62E+09 3766.99
 1-Aug-00 3760.95 4208.73 3521.14 4206.35          1.53E+09 4206.35
 1-Sep-00 4252.15 4259.87 3614.66 3672.82          1.84E+09 3672.82
  2-Oct-00 3714.48 3714.48 3026.11 3369.63          2.1E+09 3369.63
 1-Nov-00 3316.51 3480.01 2523.04 2597.93          1.95E+09 2597.93
 1-Dec-00 2644.09 3028.75 2288.16 2470.52          2.34E+09 2470.52
 2-Jan-01 2474.16 2892.36 2251.71 2772.73          2.46E+09 2772.73
 1-Feb-01 2771.57 2796.89       2127.5 2151.83     2.03E+09 2151.83
 1-Mar-01     2126.3 2243.78 1794.21 1840.26       2.13E+09 1840.26
  2-Apr-01 1835.22 2202.86 1619.58 2116.24         2.23E+09 2116.24
1-May-01     2116.24   2328.05   2052.41   2110.49   1.97E+09   2110.49
1-Jun-01     2131.12   2264.58    1973.7   2160.54   1.84E+09   2160.54
  2-Jul-01   2156.76   2181.05   1934.67   2027.13   1.64E+09   2027.13
1-Aug-01     2051.56   2103.16   1777.11   1805.43   1.46E+09   1805.43
4-Sep-01     1802.29   1836.19   1387.06    1498.8    2.2E+09    1498.8
 1-Oct-01    1491.45   1792.87   1458.41    1690.2   1.99E+09    1690.2
1-Nov-01     1705.52   1965.09   1683.99   1930.58   1.91E+09   1930.58
3-Dec-01     1915.13   2065.69   1898.98    1950.4   1.85E+09    1950.4
2-Jan-02     1965.18   2098.88   1851.49   1934.03   1.95E+09   1934.03
1-Feb-02     1928.83   1942.15   1696.55   1731.49   1.89E+09   1731.49
1-Mar-02     1745.49   1946.23   1742.08   1845.35   1.81E+09   1845.35
 1-Apr-02    1834.59   1865.37   1640.97   1688.23   1.85E+09   1688.23
1-May-02     1683.76   1759.33   1560.29   1615.73   1.89E+09   1615.73
3-Jun-02      1613.5    1621.5   1375.53   1463.21   1.96E+09   1463.21
  1-Jul-02   1457.04   1459.84   1192.42   1328.26   2.13E+09   1328.26
1-Aug-02     1322.47   1426.76   1205.68   1314.85   1.48E+09   1314.85
3-Sep-02     1302.67   1347.27   1160.07   1172.06   1.54E+09   1172.06
 1-Oct-02    1180.26   1347.58   1108.49   1329.75   1.76E+09   1329.75
1-Nov-02     1320.95   1497.44   1313.72   1478.78   1.83E+09   1478.78
2-Dec-02     1507.94   1521.44   1327.19   1335.51   1.44E+09   1335.51
2-Jan-03     1346.93   1467.35   1303.64   1320.91   1.55E+09   1320.91
3-Feb-03     1324.74   1352.07   1261.79   1337.52   1.35E+09   1337.52
3-Mar-03     1344.21   1425.73   1253.22   1341.17   1.56E+09   1341.17
 1-Apr-03    1347.54   1482.49   1338.23   1464.31   1.54E+09   1464.31
1-May-03        1463   1599.92   1451.32   1595.91   1.94E+09   1595.91
2-Jun-03      1612.1    1686.1    1584.7    1622.8   2.05E+09    1622.8
  1-Jul-03    1617.3    1776.1   1598.92   1735.02   1.82E+09   1735.02
1-Aug-03     1731.63   1813.82   1640.88   1810.45    1.5E+09   1810.45
2-Sep-03     1817.92   1913.74   1783.46   1786.94   1.99E+09   1786.94
 1-Oct-03    1797.07   1966.87   1796.09   1932.21   1.87E+09   1932.21
3-Nov-03     1941.31   1992.27   1878.07   1960.26   1.83E+09   1960.26
1-Dec-03     1972.97   2015.23   1887.46   2003.37   1.69E+09   2003.37
2-Jan-04     2011.08   2153.83   1999.77   2066.15    2.4E+09   2066.15
2-Feb-04     2072.13   2094.92   1991.05   2029.82   1.99E+09   2029.82
1-Mar-04     2036.92   2069.02   1896.91   1994.22   1.93E+09   1994.22
 1-Apr-04    1996.45   2079.12   1919.39   1920.15   2.02E+09   1920.15
3-May-04     1928.72   1991.87    1865.4   1986.74   1.69E+09   1986.74
1-Jun-04     1978.52   2055.65   1960.26   2047.79   1.64E+09   2047.79
  1-Jul-04   2045.53   2045.53   1829.06   1887.36   1.77E+09   1887.36
2-Aug-04     1874.93   1893.13   1750.82    1838.1   1.46E+09    1838.1
1-Sep-04     1833.37   1925.85   1833.33   1896.84   1.57E+09   1896.84
 1-Oct-04    1909.59   1984.18   1899.33   1974.99   1.79E+09   1974.99
1-Nov-04     1975.48   2117.89   1969.32   2096.81    1.9E+09   2096.81
1-Dec-04     2104.58   2185.56   2097.86   2175.44   2.08E+09   2175.44
3-Jan-05     2184.75    2191.6   2008.68   2062.41   2.25E+09   2062.41
1-Feb-05     2063.27   2103.45      2023   2051.72   2.04E+09   2051.72
1-Mar-05     2057.47   2100.57   1968.58   1999.23   1.88E+09   1999.23
 1-Apr-05    2009.09   2021.82   1889.83   1921.65   1.88E+09   1921.65
2-May-05     1923.23    2076.8   1916.03   2068.22    1.7E+09   2068.22
1-Jun-05     2067.23   2106.57   2039.69   2056.96   1.75E+09   2056.96
  1-Jul-05   2060.97   2201.39    2050.3   2184.83   1.72E+09   2184.83
1-Aug-05     2191.49   2219.91   2112.25   2152.09   1.56E+09   2152.09
1-Sep-05     2150.03   2186.83   2093.06   2151.69   1.73E+09   2151.69
 3-Oct-05     2152.7      2167   2025.58    2120.3   1.83E+09    2120.3
1-Nov-05     2109.89    2269.3   2108.86   2232.82    1.8E+09   2232.82
1-Dec-05     2244.85   2278.16   2200.51   2205.32    1.7E+09   2205.32
3-Jan-06     2216.53   2332.92   2189.91   2305.82    2.2E+09   2305.82
1-Feb-06     2294.11   2313.53   2232.68   2281.39   2.06E+09   2281.39
1-Mar-06     2288.15   2353.14   2239.54   2339.79   2.15E+09   2339.79
 3-Apr-06    2352.24   2375.54   2299.42   2322.57    2.2E+09   2322.57
1-May-06     2329.79   2352.56   2135.81   2178.88   2.18E+09   2178.88
1-Jun-06     2179.82   2233.88   2065.11   2172.09   2.09E+09   2172.09
  3-Jul-06   2177.91   2190.44   2012.78   2091.47    1.9E+09   2091.47
1-Aug-06     2080.34   2193.34   2048.22   2183.75   1.73E+09   2183.75
1-Sep-06     2194.56    2273.3   2147.44   2258.43   1.96E+09   2258.43
 2-Oct-06       2257   2379.29   2224.21   2366.71   2.03E+09   2366.71
1-Nov-06     2373.49   2468.42   2316.82   2431.77   1.96E+09   2431.77
1-Dec-06     2430.75   2470.95   2392.95   2415.29   1.82E+09   2415.29
3-Jan-07     2429.72   2508.93   2394.66   2463.93   2.24E+09   2463.93
1-Feb-07     2474.08   2531.42   2395.35   2416.15   2.23E+09   2416.15
1-Mar-07     2377.18   2459.96   2331.57   2421.64   2.07E+09   2421.64
 2-Apr-07    2425.36   2562.99   2409.04   2525.09   2.09E+09   2525.09
1-May-07     2529.95    2607.9   2510.57   2604.52   2.12E+09   2604.52
1-Jun-07     2614.01    2634.6   2534.97   2603.23   2.26E+09   2603.23
  2-Jul-07   2617.39   2724.74    2545.9   2546.27   2.28E+09   2546.27
1-Aug-07      2538.5   2627.75   2386.69   2596.36   2.33E+09   2596.36
4-Sep-07     2596.38   2716.75   2536.93    2701.5   1.96E+09    2701.5
 1-Oct-07    2704.25   2861.51   2698.14   2859.12   2.23E+09   2859.12
1-Nov-07        2835   2835.63   2539.81   2660.96   2.51E+09   2660.96
3-Dec-07     2654.91   2734.82   2553.99   2652.28   1.96E+09   2652.28
2-Jan-08     2653.91    2661.5   2202.54   2389.86   2.76E+09   2389.86
1-Feb-08     2392.58   2419.23   2252.65   2271.48   2.41E+09   2271.48
3-Mar-08     2271.26   2346.78   2155.42    2279.1   2.34E+09    2279.1
 1-Apr-08    2306.51   2451.19   2266.29    2412.8   2.04E+09    2412.8
1-May-08     2416.49   2551.47   2416.49   2522.66   2.14E+09   2522.66
2-Jun-08     2514.82   2549.94   2290.59   2292.98   2.29E+09   2292.98
  1-Jul-08   2274.24   2353.39   2167.29   2325.55   2.41E+09   2325.55
1-Aug-08     2326.83    2473.2   2280.93   2367.52   1.95E+09   2367.52
2-Sep-08     2402.11   2413.11   1983.73   2091.88   2.58E+09   2091.88
 1-Oct-08     2075.1    2083.2   1493.79   1720.95   2.82E+09   1720.95
3-Nov-08     1718.89   1785.84   1295.48   1535.57   2.25E+09   1535.57
1-Dec-08     1496.09   1602.92   1398.07   1577.03   1.87E+09   1577.03
2-Jan-09     1578.87   1665.63   1434.08   1476.42    2.1E+09   1476.42
2-Feb-09     1460.85    1598.5   1372.42   1377.84   2.36E+09   1377.84
2-Mar-09     1356.13      1587   1265.52   1528.59   2.35E+09   1528.59
 1-Apr-09    1504.87   1753.61   1498.54    1717.3   2.45E+09    1717.3
1-May-09     1719.29   1774.33   1664.19   1774.33   2.49E+09   1774.33
1-Jun-09     1796.09   1879.92   1753.78   1835.04   2.44E+09   1835.04
  1-Jul-09   1846.12   2009.81   1727.05    1978.5   2.24E+09    1978.5
3-Aug-09     1998.35   2015.59   1967.68   2000.25   2.76E+09   2000.25
Adj_Close       Step 1:     Define Names with Shift, CNLT, F#
                            Use top name
319,991.64
    654.38      Step 2:     Make Data Validation for top list
 #NAME?
                Step 3;     Use Indirect with Name of Title


2-Dec-02                            67                          4000

                Row Start           66
                Column               6
                Length              81
                Width                1

                Offset_name


 1.2

  1

 0.8

 0.6

 0.4

 0.2

  0
       Jan-00
                                 http://finance.yahoo.com/q/hp?s=
                       29        http://finance.yahoo.com/q/hp?s=CEG&a=00&b=3&c=1950&d=09&e
                        2

               CEG



AES            AES           1
CEG            CEG           2
CISCO          CSCO          3
CITI           C             4
EBAY           EBAY          5
ED             ED            6
ERIC           ERIC          7
EXC            EXC           8
GE             GE            9
GS             GS           10
MSFT           MSFT         11
MCD            MCD          12
MOODYS         MCO          13
NASDAQ ^IXIC   ^IXIC        14
NOK            NOK          15
PRICELINE      PCLN         16
RELIANT RRI    RRI          17
SP500 ^SCP     ^GSCP        18
XOM            XOM          19
YAHOO YHOO     YHOO         20
EXCO           XCO          21
DVN            DVN          22
FCX            FCX          23
GOOG           GOOG         24
AYE            AYE          25
PPL            PPL          26
WMT            WMT          27
DOW JONES      ^DJI         28
NRG            NRG          29
                 http://finance.yahoo.com/q/hp?s=%5EGSPC&a=00&b=3&c=1950&d=09&e=16&f=2009&g=m
                 &a=00&b=3&c=1950&d=09&e=16&f=2009&g=m
0&b=3&c=1950&d=09&e=16&f=2009&g=m
Remove Pictures     TRUE                            To test the remove pictures function




                  URL Range Name        url         http://www2.standardandpoors.com

                  The Macro goes to the url and opens the file

                                                    http://www.research.stlouisfed.org/f
                                                    http://www2.standardandpoors.com
remove pictures function, copy pictures from the next page and then press the F2 to run the function




2.standardandpoors.com/portal/site/sp/en/us/page.topic/indices_csmahp/0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0.html



.research.stlouisfed.org/fred2/data/OILPRICE.txt
2.standardandpoors.com/portal/site/sp/en/us/page.topic/indices_csmahp/0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0.html
Title 1
          2   1      2
          2   2      4
          2   3    1.5

                  -0.75
                  -9.75



Title 2



Title 3
`
range_names ='Indirect and Range Names'!$G$6:$G$152
Cash        ='Goal Seek'!$G$7
Close       ='Indirect and Range Names'!$E$6:$E$152
col_input1 ='One Way Data Table'!$C$9
cols1       ='One Way Data Table'!$F$26:$F$32
columns ='Limit Sheet Size'!$C$8
comments ='Limit Sheet Size'!$E$13
Date        =OFFSET('Indirect and Range Names'!$A$6,'Indirect and Range Names'!$M$15,0,'Indirect and Range Names'!$M$
day         =TEXT(TODAY(),"dddddd")
days        =365
            =
fiscal_month'Dates and Periods'!$E$7
formulas ='One Way Data Table'!$G$25:$K$25
            =
Greater3000 INDIRECT("RC",FALSE)>'Indirect and Range Names'!$P$13
            =
GreaterThanINDIRECT("RC",FALSE)>'Indirect and Range Names'!$P$13
HasFormula  =GET.CELL(48,INDIRECT("RC",FALSE))
            =
HasNumber ISNUMBER(INDIRECT("RC",FALSE))
High        ='Indirect and Range Names'!$C$6:$C$152
            =
interest_rate 'Dates and Periods'!$E$8
Investment ='Goal Seek'!$G$6
IRR         ='Goal Seek'!$G$15
IsOffset    =ISNUMBER(Offset_name)
Low         ='Indirect and Range Names'!$D$6:$D$152
number      =INDIRECT("RC",FALSE)
Offset_name =OFFSET('Indirect and Range Names'!$A$6,'Indirect and Range Names'!$M$15,'Indirect and Range Names'!$M$16
Open        ='Indirect and Range Names'!$B$6:$B$152
output1     ='One Way Data Table'!$G$26:$K$32
show_notes  ='Show Notes'!$A$12
show_nots ='Show Notes'!$A$12
subtot      ='Misc Data'!$A$27:$D$178
Target_IRR='Goal Seek'!$G$17
Terminal ='Goal Seek'!$G$13
Volume      ='Indirect and Range Names'!$F$6:$F$152
and Range Names'!$M$17,'Indirect and Range Names'!$M$18)




nd Range Names'!$M$16,'Indirect and Range Names'!$M$17,'Indirect and Range Names'!$M$18)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:37
posted:9/27/2012
language:Latin
pages:3293