Creating a Data Table in Microsoft Excel

Data Table What-If Analysis in Excel


Data tables are part of the What If analysis tools that allow us to change the value of some cells to see how it affected the original result. A data table analyzes a set of values and determines possible outcomes.

Data table of one variable

The simplest example of a data table is one that uses a variable for calculations. Assume the following scenario:

Data Table What-If Analysis Initial Data

In this example I’m making a sales forecast for 2015 based on 2014 sales and expecting a growth rate of 3.2%. What I want to do is to know what the sales projection for 2015 if the growth rate was different. For this test I will place the growth rates that will be used as follows:

Growth Rates Estimation with Data Table What If Analysis

To create the data table I select the range of cells as shown in the previous image and then go to the Data tab, and in the Analysis group press the What-If button finally select the Data Table option.

Data Table What-If Analysis Button

The Table Data dialog box is displayed and in the text box Column Input Cell you must select the cell B2 that is the cell that contains the percentage of growth.

Data Table What-If Analysis Column Input Cell

Clicking the OK button, adjacent cells will be filled with growth rates projected value corresponding to each of the sales rates.

Data Table What-If Analysis Growth Estimations

Excel has created the data table in the range A4: B12 and thus can analyse different sales projections for different growth rate. Once you’ve finished analysing the information, if I try to remove some of the cells belonging to the range of the data table, Excel will display a warning message that you cannot change part of a data table. If you want to delete the data table you must first select the entire range before pressing the delete key. (Source: Excel Total)