Microsoft Office Tutorials and References
In Depth Information
Playing What-If with Data Tables
in the range
5. Click OK to close the Data Table dialog box.
As soon as you click OK, Excel creates the data table in the range C8:C17
by entering a formula using its TABLE function into this range. Each
copy of this formula in the data table uses the growth rate percentage in
the same row in column B to determine the possible outcome.
6. Click cell C7, then click the Format Painter command button in the
Clipboard group on the Home tab and drag through the cell range
Excel copies the Accounting number format to the range of possible
outcomes calculated by this data table.
A couple of important things to note about the one-variable data table created
in this spreadsheet:
✓ If you modify any growth-rate percentages in the cell range B8:B17, Excel
immediately updates the associated projected sales result in the data
table. To prevent Excel from updating the data table until you click the
Calculate Now (F9) or Calculate Sheet command button (Shift+F9) on the
Formulas tab, click the Calculation Options button on the Formulas tab
and then click the Automatic Except for Data Tables option (Alt+MXE).
✓ If you try to delete any single TABLE formula in the cell range C8:C17,
Excel displays a Cannot Change Part of a Data Table alert. You must
select the entire range of formulas (C8:C17 in this case) before you press
Delete or click the Clear or Delete button on the Home tab.