Creating a Three Dimension Data Table in Excel

Creating a Three Dimension Data Table in Excel


Welcome, this video will outline one way to create
a three-dimension data table in Excel. In order to properly demonstrate this approach, I
will firstly create a simple data table for a
company’s gross revenue. This will be based on unit price, units sold and
number of sellers. We will employ a price of $40, 300 units sold and 80 sellers of these units. This will realise $960,000 in gross revenue. We can now begin the creation of the data table. We can now begin the creation of the data table. The source output, Gross Revenue, must be
referenced inside the data table. Hence cell C11 is referencing C9; the calculated
Gross Revenue output. Firstly, we will create the x-axis or column values,
which will refer to the column input cell value. This will allow the data table to compute varying
Gross Revenue amounts, based on changing
Units Sold variables. Secondly, our row or y-axis values will denote the
Unit Price, and how it also impacts Gross Revenue
amounts. We are now ready to create the data table. Remember to select the range from the y and x-
axes, in order to successfully generate the what-if
outputs via the data table. The Row input cell is the variable on the y-axis, which is the unit price running down the page. Hence we need to reference the input cell in C6
for Unit Price. The Column input cell will be C7 for Units Sold, which is the x-axis of units sold located across
the page. Our data table is now complete, and we’ll quickly
format the cells with the dollar sign. Next, we’ll create a highlight cell rule in conditional
formatting, which will highlight the unit price and units sold what-if combinations that equal $960,000 in Gross Revenue. Remember, no output cell in a data table can edited
separately – you would need to clear all the output cells
simultaneously to effect a change. Finally, we’re going to hide the cell C11, which references the source output cell for Gross Revenue. This can be realised by creating a custom format
using 2 x semi colons. We can now leverage our simple two-dimension
data table, in order to create our three-dimension data table. As we can see in this sales analysis example to compute Gross Revenue, there are three variables: prices, units sold and number of sellers. We need to add number of sellers, which will be the third dimension of this data
table. Hence, a list of number of sellers will be created
from 10 sellers up to 100 sellers. Next, a drop-down list referencing these variables
in the range K12 to K21 will be created in cell J11 for Number of Sellers. Now the input cell in C8 will now reference this
drop-down list in cell J11. Therefore, based on the quantity of sellers
selected in the range from 10 to 100, our source Gross Revenue number will change and so will the data table. Let’s move this range across to column J. To enhance and improve the three-dimension
data table, the range of sellers will be conditionally formatted
to flag the input variable selected in the input cell C8. So, whenever we adjust the value selected in the
drop-down list in cell J11, it will be flagged in the range J12 to
J21. This is one approach to creating a three-
dimension data table in Excel.