Now, a two-variable data table works on a very similar principle to the one-variable version. Previously, we were flexing just one input cell. With a two-variable data table, we can flex two inputs at the same time.
In this example, we’re going to flex both quantity sold and price.
First, I’ll enter the different quantity sold values vertically: 800, 900, and 1,000.
Then across the top, I’ll enter the price values: 100, 125, and 150.
Next, in the top-left corner of the table – where the row and column headers meet – I link to the output we're interested in, which is Year 5 revenue.
Now I select the entire range – the prices, quantities, and that linked output cell – and go to Data > What-If Analysis > Data Table.
In the Data Table dialog box:
-
The row input cell refers to the values across the top – these are the price inputs. So I select the original price input cell.
-
The column input cell refers to the values down the side – the quantity sold inputs. So I select that corresponding input cell.
Once I click OK, Excel calculates all the combinations and fills out the table.
Let’s test it to check it's working correctly.
Currently, with a price of 100 and quantity of 800, we get 285,957 – which matches the top-left result in the table.
Now, let’s try a different scenario. If we choose price = 125 and quantity = 900, the data table shows a result of 402,127.
So, if I manually enter 125 into the price input and 900 into the quantity input, we should get the same result.
And we do – the Year 5 revenue updates to 402,127, confirming that the data table is working as expected.
That’s how you can use a two-variable data table in Excel to quickly analyse how changes in two different inputs affect a specific output.
