Page 103 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 86
86 Part II: Using Formulas and Functions

9. Switch to the Practice Formulas.xls workbook and then copy the value in cell B3
of the Ext Ref worksheet to the Clipboard (Ctrl+C).
10. Switch to the new External Link.xls workbook, select cell B2 of Sheet1, choose
Edit➪Paste Special, and then click its Paste Link button.
The value 439,589.40 appears in cell B2 of the Sheet1 worksheet and the follow-
ing link formula with the external reference appears on the Formula bar:
=’[Practice Formulas.xls]Ext Ref’!$B$3
11. Save your changes to the External Link.xls workbook and then close this file.
12. Select cell A2 in the Formulas sheet in the Practice Formulas.xls and then update
the value in this cell from 56 to 72.
13. Check that the contents in cell B3 of the Ext Ref sheet have been updated to
779,438.27 to reflect the change to the value in cell C9 of the Formulas sheet.
Now save your changes to the Practice Formulas.xls workbook before you close it.
14. Open the External Link.xls workbook by selecting it from the File pull-down
menu.
As soon as this file opens, Excel displays an alert dialog box, informing you that
this workbook contains links to other sources.
15. Select the Update button in Excel alert dialog box to have the program update
the value in cell B2 of Sheet1 to 779,438.27.
Note that the external reference displayed on the Formula bar now contains the
entire pathname for the workbook file along with its sheet and cell reference.
16. Save your changes to the External Link.xls workbook and then close this file.

Controlling When Formulas Are Recalculated

As you’ve seen in the exercises in this chapter, Excel immediately and automatically
recalculates all the formulas in a worksheet the moment you make a change to any
cell that referred to in its formulas. This automatic recalculation mode is fine as long
as your spreadsheet is relatively small and your edits to it are few.

Working in this mode can, however, be a real drag (both literally and figuratively)
when the worksheet is large and contains lots and lots of formulas: You may be forced
to wait quite a few seconds while Excel recalculates every loving formula before you
can do anything more after each and every editing change you make.

To avoid this hassle, change the recalculation mode from automatic to manual on the
Calculation tab of the Options dialog box (Tools➪Options) so that Excel recalculates
the formulas in the worksheet only when you specifically tell it to by selecting its Calc
Now(F9) or Calc Sheet button or when you save changes to the worksheet.

Try It

Exercise 5-8: Controlling When Formulas Are Recalculated
Open your Practice Formulas.xls workbook in the Chapter 5 folder inside the My
Practice Spreadsheets folder and use it to get some experience using Excel in both
automatic and manual recalculation mode:

1. Update the value in cell A2 of the Formulas sheet from 72 to 102.