Tip:
Highlight text to annotate it
X
Data tables allow you to put the different possibilities into a nice table format so that you can easily analyze the information.
Our spreadsheet shows a basic Income Statement for a Rafting business.
Let’s say we wanted to know how the number of customers the business has affects the Net Income.
We could manually type in different possibilities into cell B7, which displays the Average number of Customers per day.
We would have to type in each value and then look down at the bottom of our statement to see how the Net Income changes.
Instead, we’ll use a Data table that will automatically calculate the results for us.
We need to start by determining the different Avg number of Customers per day we are considering
and adding them into our spreadsheet.
These are called Substitution values and will be those same numbers that we typed into cell B7 when we were doing this manually.
I will consider 40-60 customers in increments of 5.
We are looking to find the effect of changing the number of customers on the Net Income,
so I need to include a cell reference to Net Income in my data table.
I will put this at the top of my table.
With the one-variable data table, I can have multiple result cells.
For now, I will keep it simple and we will just have the Net Income.
To fill in the data table values, select your substitution values and cell references.
Then, go to the Data tab and click What If Analysis. Choose Data Table.
We are asked for a Row input and a Column input.
These are cell references that tell Excel where to plug in the Substitution values in the Income Statement.
We only have one variable, so we will not need both the row and column input cells.
Our data table will use a column input cell, since all of the substitution values are located in the same column.
We can just click cell B7 to enter it as a column input cell.
Now let’s try a two variable data table.
In this table, we will look at the effect of both the Average Customers per day and the Length of Season on the Net Income.
First, I will add the same values for Customers per day that I had in the first table.
Across the top of my table, I will type in the values for my second variable.
I will look at a length of season of 110, 120, 130, and 140 days.
My result cell will go in the upper left-hand corner of the table.
To complete the table, I will again go to the Data tab and choose Data table from the What If Analysis menu.
This time, we have two variables, so we will have both the row and column input cells.
The Length of Season values are all in the same row, so I will use this cell as my row input cell.
Average Number of Customers will be my Column Input cell.
To improve the appearance, format the data and use a Custom Cell Format to display a label instead of the actual value in the result cell.
If you ever get “stuck” in a Data table and you keep getting the error “Cannot change part of a Data table”, hit the escape key.
If you wish to delete the values from your data table, select the results and hit the Delete key (not backspace) on your keyboard.