Tag: Excel Table

Two Variable Data Table in Excel

Two Variables Data Table in Excel



In a previous article I showed the benefits of the data tables (Data Table in Microsoft Excel) as an option of What-If analysis. This time we review a classic example of the use of a table of data for analysis of two variables.


The scenario is as follows. Suppose I want to apply for a personal loan from a bank, I’m doing a research with several banks and each of them has given me an interest rate as well as several different payment terms. The first case that I investigated is:

Two Variables Data Table Initial Data

Two Variables Data Table Instead of making the calculation for each option interest rate and term, adequately I will accommodate the data to form a data table. In the rows I will specify different rates of interest and in the columns possible terms.

Rows and Columns for the Data Table


To create the data table with two variables I will select the range B4: G11 and I go to the Data tab and click the “What-If” button to select whether the “Data Table” option. Within the dialog I must choose each of the input cells for the data table as follows:

What-If Data Table Dialog Box

When click OK the data table is generated

Two Variable Data Table Results

In this data table can observe what we expected, the higher is the term the monthly payments are reduced and the greater is the interest rate your monthly payment increases. With this information I can properly analyze and choose the option that best fits my chances of monthly payment. (Source: Excel Total)



Data Table What-If Analysis in Excel

Creating a Data Table in Microsoft 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)