fm

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\[c6ece72c-f7fb-45d7-b1d2-ba6e04765145.xls]Table of Contents

xls]Table of Contents



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



Sunday



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

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

Table of Contents



To Use



Run the macro once



Then use CNTL M from the sheet

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 in this FM

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 sheet

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

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 #NAME? 9





Base Data 1 #NAME? #NAME? #NAME? 5 7





With Interpolate 1 #NAME? 3 #NAME? 5 #NAME? 7



1 2 3 5 6









Alternative Data 10.33124 #NAME? 5.331235 #NAME? 25.33124 #NAME? 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





#NAME? 9







You can do this with CNTL I







#NAME? 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



F



Z AA



200 GR



6



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 #NUM!





(5,000,000) 1,000,000 10,000 100,000 100,000 100,000 #NUM!







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%





#NUM! =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

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

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%

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'!$H$17:$M$17

-1100 -1200 -1300 -1400









Sum Payback St Dev



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









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

'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 -990.00

Cash 200.00

200.00

200.00

200.00

200.00

200.00

Terminal 244.75



IRR 10.20%



Target IRR 10.20%





Change w/o Macro

0

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

Table of Contents

PRESS F2 Key if N/A is in Cells



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



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



=MID(E4,E6+1,50)--> Table of Contents





bonjour filename



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





=MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,250)--> Table of Contents









Copy this formula to your sheet

PRESS F2 Key if N/A is in Cells



C:\Docstoc\Working\pdf\[c6ece72c-f7fb-45d7-b1d2-ba6e04765145.xls]Table of Contents







Table of Contents









Table of Contents

Periods



Current Date 15-Jan-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 15-Jan-12 1-Mar-12 1-May-12 1-Jul-12

End Date 29-Feb-12 30-Apr-12 30-Jun-12 31-Aug-12

Year 2012 2012 2012 2012

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 - 1.33 2.80 4.41



Year Balance Row 2009 2010 2011 2012



Opening Balance TRUE 20 - - - -

Add: Draws FALSE 21 - - - 772

Closing TRUE 22 - - - -



Annual Interest Rate TRUE 24 0% 0% 0% 0%

Periodic Interest FALSE 25 - - - 23

4 5 6 7 8 9 10

1 1 1 1 1 1 1

1-Sep-12 1-Nov-12 1-Jan-13 1-Mar-13 1-May-13 1-Jul-13 1-Sep-13

31-Oct-12 31-Dec-12 28-Feb-13 30-Apr-13 30-Jun-13 31-Aug-13 31-Oct-13

2012 2012 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%

6.19 8.14 10.29 12.65 15.25 18.11 21.25



2013 2014 2015 2016 2017 2018 2019



- - - - - - -

1,367 2,421 4,290 7,600 13,463 23,851 42,254

- - - - - - -



0% 0% 0% 0% 0% 0% 0%

102 243 492 933 1,715 3,100 5,554

11 12 13 14 15 16 17

1 1 1 1 1 1 1

1-Nov-13 1-Jan-14 1-Mar-14 1-May-14 1-Jul-14 1-Sep-14 1-Nov-14

31-Dec-13 28-Feb-14 30-Apr-14 30-Jun-14 31-Aug-14 31-Oct-14 31-Dec-14

2013 2014 2014 2014 2014 2014 2014

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 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%

24.71 28.51 32.70 37.30 42.36 47.93 54.06



2020 2021 2022 2023 2024



- - - - -

74,855 132,610 234,926 416,186 737,299

- - - - -



0% 0% 0% 0% 0%

9,901 17,601 31,243 55,411 98,227

18 19 20 21 22 23 24

1 1 1 1 1 1 1

1-Jan-15 1-Mar-15 1-May-15 1-Jul-15 1-Sep-15 1-Nov-15 1-Jan-16

28-Feb-15 30-Apr-15 30-Jun-15 31-Aug-15 31-Oct-15 31-Dec-15 29-Feb-16

2015 2015 2015 2015 2015 2015 2016

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 0 0 0 0 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%

60.80 68.21 76.37 85.34 95.20 106.06 118.00

25 26 27 28 29 30 31

1 1 1 1 1 1 1

1-Mar-16 1-May-16 1-Jul-16 1-Sep-16 1-Nov-16 1-Jan-17 1-Mar-17

30-Apr-16 30-Jun-16 31-Aug-16 31-Oct-16 31-Dec-16 28-Feb-17 30-Apr-17

2016 2016 2016 2016 2016 2017 2017

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%

131.13 145.58 161.47 178.95 198.17 219.33 242.59

32 33 34 35 36 37 38

1 1 1 1 1 1 1

1-May-17 1-Jul-17 1-Sep-17 1-Nov-17 1-Jan-18 1-Mar-18 1-May-18

30-Jun-17 31-Aug-17 31-Oct-17 31-Dec-17 28-Feb-18 30-Apr-18 30-Jun-18

2017 2017 2017 2017 2018 2018 2018

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 0 0 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%

268.18 296.34 327.30 361.37 398.84 440.05 485.39

39 40 41 42 43 44 45

1 1 1 1 1 1 1

1-Jul-18 1-Sep-18 1-Nov-18 1-Jan-19 1-Mar-19 1-May-19 1-Jul-19

31-Aug-18 31-Oct-18 31-Dec-18 28-Feb-19 30-Apr-19 30-Jun-19 31-Aug-19

2018 2018 2018 2019 2019 2019 2019

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%

535.26 590.12 650.47 716.85 789.87 870.19 958.54

46 47 48 49 50 51 52

1 1 1 1 1 1 1

1-Sep-19 1-Nov-19 1-Jan-20 1-Mar-20 1-May-20 1-Jul-20 1-Sep-20

31-Oct-19 31-Dec-19 29-Feb-20 30-Apr-20 30-Jun-20 31-Aug-20 31-Oct-20

2019 2019 2020 2020 2020 2020 2020

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 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%

1,055.73 1,162.63 1,280.23 1,409.59 1,551.88 1,708.40 1,880.57

53 54 55 56 57 58 59

1 1 1 1 1 1 1

1-Nov-20 1-Jan-21 1-Mar-21 1-May-21 1-Jul-21 1-Sep-21 1-Nov-21

31-Dec-20 28-Feb-21 30-Apr-21 30-Jun-21 31-Aug-21 31-Oct-21 31-Dec-21

2020 2021 2021 2021 2021 2021 2021

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 0 0 0 0 0



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%

2,069.96 2,278.29 2,507.46 2,759.53 3,036.82 3,341.84 3,677.35

60 61 62 63 64 65 66

1 1 1 1 1 1 1

1-Jan-22 1-Mar-22 1-May-22 1-Jul-22 1-Sep-22 1-Nov-22 1-Jan-23

28-Feb-22 30-Apr-22 30-Jun-22 31-Aug-22 31-Oct-22 31-Dec-22 28-Feb-23

2022 2022 2022 2022 2022 2022 2023

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%

4,046.42 4,452.40 4,898.97 5,390.20 5,930.55 6,524.94 7,178.77

67 68 69 70 71 72 73

1 1 1 1 1 1 1

1-Mar-23 1-May-23 1-Jul-23 1-Sep-23 1-Nov-23 1-Jan-24 1-Mar-24

30-Apr-23 30-Jun-23 31-Aug-23 31-Oct-23 31-Dec-23 29-Feb-24 30-Apr-24

2023 2023 2023 2023 2023 2024 2024

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 0 0 0 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%

7,897.98 8,689.11 9,559.36 10,516.63 11,569.62 12,727.92 14,002.04

74 75 76 77 78 79 80

1 1 1 1 1 1 1

1-May-24 1-Jul-24 1-Sep-24 1-Nov-24 1-Jan-25 1-Mar-25 1-May-25

30-Jun-24 31-Aug-24 31-Oct-24 31-Dec-24 28-Feb-25 30-Apr-25 30-Jun-25

2024 2024 2024 2024 2025 2025 2025

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%

15,403.58 16,945.27 18,641.13 20,506.58 22,558.57 24,815.76 27,298.67

81 82 83 84 85 86 87

1 1 1 1 1 1 1

1-Jul-25 1-Sep-25 1-Nov-25 1-Jan-26 1-Mar-26 1-May-26 1-Jul-26

31-Aug-25 31-Oct-25 31-Dec-25 28-Feb-26 30-Apr-26 30-Jun-26 31-Aug-26

2025 2025 2025 2026 2026 2026 2026

FALSE FALSE FALSE FALSE FALSE FALSE FALSE

0 0 0 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%

30,029.87 33,034.19 36,338.94 39,974.17 43,972.92 48,371.55 53,210.03

88 89 90 91 92 93 94

1 1 1 1 1 1 1

1-Sep-26 1-Nov-26 1-Jan-27 1-Mar-27 1-May-27 1-Jul-27 1-Sep-27

31-Oct-26 31-Dec-26 28-Feb-27 30-Apr-27 30-Jun-27 31-Aug-27 31-Oct-27

2026 2026 2027 2027 2027 2027 2027

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%

58,532.37 64,386.94 70,826.97 77,911.00 85,703.43 94,275.11 103,703.95

95 96 97 98 99 100

1 1 1 1 1 1

1-Nov-27 1-Jan-28 1-Mar-28 1-May-28 1-Jul-28 1-Sep-28

31-Dec-27 29-Feb-28 30-Apr-28 30-Jun-28 31-Aug-28 31-Oct-28

2027 2028 2028 2028 2028 2028

FALSE FALSE FALSE FALSE FALSE FALSE

0 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%

114,075.68 125,484.58 138,034.37 151,839.14 167,024.39 183,728.16

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 147

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 6 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-Jun-97 1 4000



Row Start 0

Column 6

Length 147

Width 1



Offset_name





1.2



1



0.8



0.6



0.4



0.2



0

Jan-00

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)