Part II: Working with Formulas and Functions
222
Tip
If your worksheet uses any data tables (described in Chapter 36), you may want to select the Automatically
Except for Data Tables option. Large data tables calculate notoriously slowly. Note: A data table is not the
same as a table created by choosing Insert ➪ Tables ➪ Table. n
When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar
when you have any uncalculated formulas. You can use the following shortcut keys to recalculate
the formulas:l (^) F9: Calculates the formulas in all open workbooks.
l Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in the
same workbook aren’t calculated.
l Ctrl+Alt+F9: Forces a complete recalculation of all formulas.
Note
Excel’s Calculation mode isn’t specific to a particular worksheet. When you change the Calculation mode, it
affects all open workbooks, not just the active workbook. n
Using Advanced Naming Techniques ................................................................................
Using range names can make your formulas easier to understand, easier to modify, and even help
prevent errors. It’s much easier to deal with a meaningful name such as AnnualSales than with
a range reference, such as AB12:AB68.Cross-Reference
See Chapter 4 for basic information regarding working with names. n
Excel offers a number of advanced techniques that make using names even more useful. I discuss
these techniques in the sections that follow.Using names for constants .......................................................................................
Many Excel users don’t realize that you can give a name to an item that doesn’t appear in a cell. For
example, if formulas in your worksheet use a sales-tax rate, you would probably insert the tax-rate
value into a cell and use this cell reference in your formulas. To make things easier, you would
probably also name this cell something similar to SalesTax.Here’s how to provide a name for a value that doesn’t appear in a cell:- Choose Formulas ➪ Defined Names ➪ Define Name. Excel displays the New Name
dialog box. - Enter the name (in this case, SalesTax) into the Name field.