When you examine business data in Microsoft® Excel®, you may want to discover the result of formulas if you provide different inputs. You can calculate the results of those changes by using a data table. Using data tables makes it easy to examine a range of possibilities at a glance.
You can create one or two variable data tables, depending on the number of inputs and formulas that you want to test.
Use a one variable data table if:
- You want to see how different values of one variable in one or more formulas will change the results of those formulas.
Use a two variable data table to:
- See how different values of two variables in one formula will change the results of that formula.
In this example, we use a two variable data table to calculate the projected sales for 2016. The input values are possible sales and expense growth.
Note: You are welcome to download the example workbook to practice.
Applies To: Microsoft® Excel® 2010 and 2013.
1. For a two variable data table, the copy of the original formula must be entered at the intersection of the row and column input values. At cell B7, type =B5.
(The formula in B5 is =(Sales 2015+( Sales 2015*Growth 2016)-( Sales 2015*Expense 2016)).
2. Type one list of input values in the same column, below the formula.
3. Enter the second list in the same row as the formula, to its right.
4. Select the range of cells that contains the formula, both the row and column of values, and the cells in which you want the calculated values. (B7:H15).
5. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
6. In the Row input cell box, enter the reference to the input cell for the input values in the row.
7. In the Column input cell box, enter the reference to the input cell for the input values in the column.
8. Click OK.
9. Format the values in the range C8:H15 to the currency of your choice.
A table displaying the projected sales for 2016 has been generated. The table shows the projected sales for 2016 based on possible sales and expense rates.
