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.
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.