appropriate place(s) in the cell address.
F2 Ã¾ F5: Tracing Back to Formula Sources
Once in the edit mode with the F2 key you can highlight a cell
address and then press F5. This is a quick â€˜â€˜go to.â€™â€™ This is most
helpful for tracing back to the source(s) when the cell you are
editing contains a long formula.
F2QF9: Recalculating Portions of a Formula
Press F2 while you are in a cell with a long formula, then high-
light a portion of the formula with your mouse and press F9. The
highlighted portion of the formula will be shown as the result.
This is a great way to check if your formulas are calculating
correctly, because you can highlight increasingly larger portions
to follow the sequence of calculations.
Hereâ€™s an example:
1. Press F2 to edit the formula in E6. Highlight D2 in the
A B C D E F G
Your Model-Building Toolbox: F Keys and Ranges 51
2. Press F9. D2 now changes to 0.60, the contents of the
A B C D E F G
2 10 60.0% 140.0
3 80.0% 20.0
4 4.0% 50.0
3. Now highlight E2 in the formula and press F9. E2
changes to 140, the number it contains.
A B C D E F G
4. Now highlight the expression 0.60*140 . . .
A B C D E F G
5. . . . and when you press F9, this expression changes to 84.
A B C D E F G
You can continue doing the successive highlighting and
pressing F9 through any formula, no matter how long. F2 and
F9 will always show you the result.
Be careful. After you have finished your checking, make
sure you press the Esc key and not the Enter key. Pressing Esc
will undo all the results and revert the formula to its original
form. Pressing Enter will convert the portions of the formula you
have been editing in this way into the result. If you pressed the
Enter key after step 5, the formula will now be permanently
changed to Â¼ 84 Ã¾ D3*E3 Ã¾ D4*E4. Fortunately, you can still
reverse this action by pressing Ctrl Ã¾ z, for Undo.
F3: Paste a Name into a Formula
This function pastes a name from the list of range names into a
formula (see the section on Ranges later in this chapter). As you
write or edit the formula, press F3 and a â€˜â€˜Paste Nameâ€™â€™ dialog
box will pop up on your screen. From this, you can select the
range name you want to paste into the formula. Once you click
on OK, then Excel returns you to the formula and you can con-
tinue working with the formula.
F4: Repeat the Last Action
F4, by itself, will repeat the last command. This can be a short-cut
sequence, like Ctrl Ã¾ B for bold, or even the keyboard sequence
equivalent, Format > Cells > Font > Bold. This is a time-saving
Your Model-Building Toolbox: F Keys and Ranges 53
key when you need to do something that involves hitting mul-
tiple keys across many cells in your sheet, and this is worth
If you are doing a sequence that requires two operations
(e.g., selecting a row across the worksheet and then performing
an Insert command to add a whole row across), F4 will only
repeat the last command. This means that you will be inserting
a row on only one column (the last command), since F4 does
not also select the whole row (the second-to-last command). In
such a case, exploring the keyboard alternative may prove useful.
The keystroke sequence for inserting a whole row is Alt >
Insert > Rows. Your cursor can be on any column in the row to
do this. Now when you press F4, this sequence is repeated and
you can then quickly insert rows anywhere else on the work-
F5: Go To
Pressing F5 will show you this form. If you have a file that
has no range names, the â€˜â€˜Go to:â€™â€™ box will be blank, as shown
in Figure 4-1.
If there are range names, and you want to go to a particular
one, just highlight it and then click on OK. If you want to go to
another address, type it in the â€˜â€˜Referenceâ€™â€™ box. Here is a feature
to remember: once you get to your destination, you can press F5
again and just press OK to return to your starting point.
Click on the â€˜â€˜Specialâ€™â€™ key to see another form that lists
other destinations to go to with the F5 key. (See Figure 4-2.)
The Go To Special form is more of a specialized â€˜â€˜findâ€™â€™
function. It is a â€˜â€˜go toâ€™â€™ function to go to cells that meet the
condition that you have selected from the radio buttons. Most
of the options are self-explanatory.
Of note are the following options:
Row differences. If you have two or more columns of
numbers and you want to check if any of the cells are
different across the rows, highlight the columns and then
choose this option. It will show you all the rows where the
numbers are not identical.
Column differences. This is the same idea as with Row
differences, but only for differences across columns.
Last cell. This shows you the cell at the bottom right-hand
corner of the area of the screen that you have used. You
use a cell by inputting an entry. The more cells you use,
the bigger the amount of memory that your file needs
(and the bigger the size of the file). For this reason, if you
have been working on a sheet and making many changes,
it is a good idea to check where the last cell is, so that the
sheet is not any bigger than it needs to be. The keyboard
alternative to check this is Ctrl Ã¾ End.
To reduce the used area on your sheet, do the following:
1. Select the row just below the last row with data and
highlight all the rows below it to the last row being used.
Do this by clicking and dragging on the row numbers on
the left of the screen.
Your Model-Building Toolbox: F Keys and Ranges 55
2. Right-click on your mouse and select Delete.
3. Do the same with columns by selecting the column just to
the right of the last column of data and highlighting all
the used columns to the right of that. Do this by clicking
and dragging on the column letters on the top of the
4. Again, right click on your mouse and select Delete.
5. Save the file and close it. When you reopen it, the used
area will have reduced itself.
(Another way to see the used range and one that does not
use the F5 key at all is to go to cell A1, which you can do by
pressing Ctrl Ã¾ Home. Then press Shift Ã¾ End Ã¾ Home keys
together. This will highlight the used range. Pressing End Ã¾
Home alone will make the cursor go to the last used cell, without
highlighting the used range.)