Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

With Excel 2007’s data tables, you enter a series of possible values that Excel plugs into a single formula so you can perform what-if analysis on the data. What-if analysis enables you to explore the possibilities in a worksheet by inputting a variety of promising or probable values into the same equation and letting you see the possible outcomes in the worksheet. In addition to data tables, other what-if analysis features in Excel 2007 include goal seeking and scenarios.

Excel supports two types of data tables, a one-variable data table that substitutes a series of possible values for a single input value in a formula, and a two-variable data table that substitutes a series of possible values for two input values in a single formula.

Both types of data tables use the same Data Table dialog box that you open by choosing What-If Analysis→Data Table in the Data Tools group on the Data tab of the Ribbon. The Data Table dialog box contains two text boxes: Row Input Cell and Column Input Cell.

  • When creating a one-variable data table, you designate one cell in the worksheet that serves either as the Row Input Cell (if you’ve entered the series of possible values across columns of a single row) or as the Column Input Cell (if you’ve entered the series of possible values down the rows of a single column).

  • When creating a two-variable data table, you designate two cells in the worksheet and therefore use both text boxes: one cell that serves as the Row Input Cell (that substitutes the series of possible values you’ve entered across columns of a single row) and another that serves as the Column Input Cell (that substitutes the series of possible values you’ve entered down the rows of a single column).

    An example of a two-variable data table (B3 is the row input cell; B4 is the column input cell).
    An example of a two-variable data table (B3 is the row input cell; B4 is the column input cell).

The Excel data table feature works by creating a special kind of formula called an array formula in the blank cells of the table. An array formula (indicated by being enclosed in a pair of curly brackets) is unique in that Excel creates copies of the formula in each blank cell of the selection at the time you enter the original formula (you don’t make the formula copies yourself). As a result, editing changes such as moving or deleting are restricted to the entire cell range containing the array formula.

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: