<<

. 11
( 62 .)



>>

Chapter 4
56




F6: Move to the Next Pane
If you have split the screen (Windows > Split) so that it has up to
four areas or panes, then pressing F6 will move you from one
pane to another in a clockwise direction.


F7: Spelling Command
This will launch a spell check for the active sheet only. If
you want to check the spelling on other sheets, you will have to
go to those sheets and press F7 again. F7 is the shortcut for
Tools > Spelling.


F8: Anchor the Start of a Range
to Extend a Selection
This is not a well-known key but it is useful when you want to
highlight a large range. Whereas highlighting a small range can
be easily done with the mouse, anything beyond one screen wide
and one screen high is a little tricky. What if you wanted to
highlight 80 columns across and 2000 rows down? This is
where the F8 key becomes handy. Go to the top left corner of
the range you want to highlight, then press F8. This anchors the
start of the range. If you use the keyboard arrow keys, the high-
lighted range automatically expands as you move the cursor any
way from the starting point. If you are using the mouse, clicking
on a cell will define that bottom right corner. You can keep
redefining this corner with the mouse. The final range is selected
only when you press Enter, and the active cell becomes the top
left corner, even as the whole range remains painted on your
screen.
If you changed your mind about highlighting a range after
you pressed F8, just press Esc.


F9: Recalculate
This key recalculates the whole file, and any other file that is also
open in Excel. If you have set your worksheet to have automatic
calculation, this key is not critical, as that setting means that




TLFeBOOK
Your Model-Building Toolbox: F Keys and Ranges 57




Excel will always be refreshing the worksheet to take into
account any changes that have been made.
If you have set the calculation setting to ˜˜Automatic except
for tables,™™ then you must press this F9 key if you want to
update your data tables. Other calculations will be refreshed
automatically.
If your setting is for manual calculation, then you must
remember to press this key to ensure that the screen shows the
latest set of calculated numbers. This is especially important
prior to any printing.


CtrlQAltQF9 Forced Calculation
As your model becomes more complex and sizable, Excel must
keep track of the myriad of calculations in it. Occasionally,
though not very often, you may find that the numbers do not
seem to be updating properly. One reason is that Excel has an
˜˜intelligent recalculation™™ feature that is supposed to minimize
the recalculations it has to do (i.e., it ˜˜knows™™ when cells do not
need to be recalculated because the results will not change with
further recalculations). This feature can lead to a situation where
the recalculation does not happen when it should happen. In this
case, use this sequence to override the intelligent recalculation
feature and force Excel to go through a refresh.
One hint: as we go deeper into modeling, you will be using
circular references. As mentioned in the previous chapter, you
will need to set Excel to the calculation setting to allow iterations.
If you find that the numbers are not recalculating, no matter how
many times you press F9 or Ctrl þ Alt þ F9, check that the itera-
tion setting has been enabled. If you have circular references and
the iteration setting is off, the recalculation cannot proceed.


F10: Make the Menu Bar Active
F10 will allow you to select items on the menu bar by the key-
board. When you press F10, you will see that the File menu item
on the menu bar seems to be on a raised level. This indicates that
the menu bar can now be selected by pressing the keys on the




TLFeBOOK
Chapter 4
58




keyboard that correspond with the underlined letter in the menu
item. F10 is equivalent to just pressing the Alt key.


F11: Create a Chart
This is the key that will quickly show a set of numbers that your
cursor is on as a chart.


F12: Save As Command
This is the alternative of File > Save As command. F12 allows you
to save a file quickly, but potentially under a different name than
it has. This is different from Ctrl þ S, or File > Save, which saves
the file under the name it already has.



RANGES
In Excel, we often work with a block of cells for various opera-
tions. Such a block is called a range. The top left corner of a
range is defined by one cell, and the bottom right corner is
defined by another. In this way, a range is always rectangular
and in fact can only be this four-sided shape. One cell can also be
thought of as a range, whose top left address is the same as the
bottom right. Likewise, the whole sheet from A1 to IV65536 is
range.


A B C D E F G
1 2002 2003 2004 2005 2006
2 Taxable income 100 110 121 132 145
3
4 Tax rate 35%
5 Provision for taxes =C2*$B$4
6 =C2*TaxRate



Once you have a range, you can name it, and this is where it
becomes a powerful tool for your model building.




TLFeBOOK
Your Model-Building Toolbox: F Keys and Ranges 59




Defining a Range Name Quickly
When defining a range name, use the Name Box at the upper
left-hand corner just above the corner where the column letter A
and the row number 1 meet.


=
TaxRate
A B C D E F G
1
2 10
3
4
5
6




With the cursor already placed in the cell or cells that you
want to name, click on the Name Box, type the range name, and
press Enter. Here, cell B3 has been named TaxRate. This quick
shortcut is good for naming any size range.
Let™s build on this with an example of calculating ˜˜Provision
for taxes™™ line:


= 0.35
TaxRate
A B C D E F G
1
2 Tax rate 10
3 35.00%
4 2002 2003 2004
5 Taxable income 100 110 121
6 Provision for taxes =D5*TaxRate
7 =D5*$B$3



With the range named as TaxRate (remember, range names
do not allow spaces in them) in cell D6, we can type a formula
that uses both a cell address and a range name. Range names
also have absolute addresses, so that if you copy this across, the




TLFeBOOK
Chapter 4
60




range name TaxRate will continue to point to cell B3. This named
range becomes convenient to use when you are further down in
the spreadsheet and need the input number. You can just type
TaxRate instead of having to find the exact cell address. The cell
D7 shows the formula that you would have to write if you did
not use the TaxRate range name.


The Long Way of Naming Ranges
If we do not use the Name Box for naming ranges, we have to
use the sequence Insert > Name > Define to get to the user form
that will allow us to define, or name, the range. As you can see,
this requires more steps.


Naming Many Single-Cell Ranges at the
Same Time
A variation of this longer sequence is actually useful and is a
time saver when you want to name many single-cell ranges at
the same time. This can occur when you need to set some toggle
ranges, for example. Here™s how to do it:
1. Type the range names that you want to create in the
spreadsheet. For our example, write them down
vertically. In this example, we are going to name the
cells in column C with the names that we have entered
in column B:


=
A B C D E F G
1
2 StartDate 10
3 EndDate
4 NoOfYrs
5 Rate
6 TaxDeduct
7
8




TLFeBOOK
Your Model-Building Toolbox: F Keys and Ranges 61




2. Highlight the block B2 to C6 and press Insert > Name >
Create.

=
A B C D E F G
1
2 StartDate
3 EndDate
4 NoOfYrs
5 Rate
6 TaxDeduct
7
8

<<

. 11
( 62 .)



>>