Part II: Working with Formulas and Functions
calculation mode to Manual, which you can do by choosing Formulas ➪ Calculation ➪
Calculation Options ➪ Manual.
If your worksheet uses any large data tables, you may want to select the Automatic Except for Data Tables option.
Large data tables calculate notoriously slowly. A data table is not the same as a table created by choosing Insert ➪
Tables ➪ Table.
See Chapter 31, “Performing Spreadsheet What-If Analysis,” for more on data tables.
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:
F9: Calculates the formulas in all open workbooks.
Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in
the same workbook aren’t calculated.
Ctrl+Alt+F9: Forces a complete recalculation of all formulas.
Ctrl+Alt+Shift+F9: Rebuilds the calculation dependency tree and performs a complete
recalculation.
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.
Using Advanced Naming Techniques
Using range names can make your formulas easier to understand and modify and even help
prevent errors. Dealing with a meaningful name such as AnnualSales is much easier than
dealing with a range reference, such as AB12:AB68.
See Chapter 4, “Working with Excel Ranges and Tables,” for basic information regarding working with
names.
Excel offers a number of advanced techniques that make using names even more useful. We
discuss these techniques in the sections that follow. This information is for those who are
interested in exploring some of the aspects of Excel that most users don’t even know about.
