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:
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:
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.
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.
Clicking the OK button, adjacent cells will be filled with growth rates projected value corresponding to each of the sales rates.
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)