Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

To create a one-variable data table to perform what-if analysis in Excel 2007, you enter a series of input values either across columns of a single row or down the rows of a single column. The formulas in a one-variable data table refer to only one input cell: a row input cell for input values entered across a row or a column input cell for input values entered down a column.

The steps below for creating a one-variable data table follow a specific example (rather than using generic steps) to help you understand exactly how to use this feature in your worksheets. The following figure shows a Sales Projections worksheet for which a one-variable data table is to be created. In this worksheet, the projected sales amount in cell B5 is calculated by adding last year’s sales total in cell B2 to the amount that sales are expected to grow in 2008 (calculated by multiplying last year’s total in cell B2 by the growth percentage in cell B3).

Sales projection worksheet with a column of possible growth percentages to be plugged in to a one-v
Sales projection worksheet with a column of possible growth percentages to be plugged in to a one-variable data table.

The column of possible growth rates ranging from 1% all the way to 5.5% is entered down column B in the range B8:B17. Follow these steps to create a one-variable data table that plugs each of these values in to the sales growth formula:

  1. Copy the original formula entered in cell B5 into cell C7 by typing = (equal to) and then clicking cell B5.

    The copy of the original formula (into which the series of different growth rates in B8:B17 is to be substituted) is now the column heading for the one-variable data table.

  2. Select the cell range B7:C17.

    The range of the data table includes the formula along with the various growth rates.

  3. Choose What-If Analysis→Data Table in the Data Tools group on the Data tab.

    Excel opens the Data Table dialog box.

  4. Click in the Column Input Cell text box and then click cell B3, the Annual Growth Rate cell with the original percentage.

    Excel inserts the absolute cell address, $B$3, into the Column Input Cell text box.

  5. Click OK.

    Excel creates the data table in the range C8:C17 by entering a formula using the 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.

    Sales projection worksheet after creating the one-variable data table in the range C8:C17.
    Sales projection worksheet after creating the one-variable data table in the range C8:C17.

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 dialog box — 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.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: