Microsoft Office Tutorials and References

In Depth Information

**Analyzing data by using data tables**

Analyzing data by using data tables

When you examine business data in Excel, you will often want to discover the result of

formulas if you provide different inputs. For example, the executive team of Consolidated

Messenger might want to calculate the change in revenue from raising shipping rates,

increasing package volume, or both. In Excel 2013, you can calculate the results of those

changes by using a data table.

To create a data table that has one variable, you create a worksheet that contains the data

required to calculate the variations in the table.

IMPORTANT
The data and formulas must be laid out in a rectangle so the data table you

create will appear in the lower-right corner of the cell range you select.

The formula used to summarize the base data appears in cell D2, the cells with the

changing values are the range C3:C5, and the cells to contain the calculations based on those

values are D3:D5. To create the single-variable data table, select the cell range C2:D5, which

encompasses the changing rates, summary formula, and target cells. Then, on the Data tab,

click What-If Analysis, and then click Data Table to display the Data Table dialog box.

8

Now you need to identify the cell that contains the summary formula’s value that will

change in the data table’s cells. In this case, that cell is B3. Because the target cells D3:D5

are laid out as a column, you enter the cell address B3 in the Column Input Cell box and

click OK.