Normally data tables are restricted to just two input variables, but sometimes we would like to show a third input variable in our data table. There are a number of ways to do this. In this example, we look at using a data table and then adding a pivot table on top to visualize the results. In our sales model, we have three variables that we want to flex to see how profit is affected. Price, cost, and demand. The first step is to set up the elements we need to create the data table. The first of these are some numbers that will feed a VLOOKUP function that we're going to build later.

Let's put those in row 16.

The next step is to populate our possible combinations or our answer matrix with all the different variables that we'd like to have available to our three input table model. Now I've listed these out here in a little matrix at the top here. So these are my different prices that I'd like to have. These are the different demands that I'd like and these are the different unit costs. So what I need to do is I need to build up a little answer matrix underneath in columns D, E, and F here, where we're gonna have all the possible combinations.

Now, one way to do this is to just work out in your head how many combinations that we're gonna need. So if I've got five unit cost bands and two demand bands, if I copy the unit cost in here twice, then I'll know that I'll need 29,000 for that first band and then I'll need 30,000 for the second band and then we can copy that down as well.

And we repeat this process until we've built up our matrix. The next step is we just need some zeros in our top row here.

And then we're going to number our different scenarios. So literally we're just going to have 1 and then 2. And then we're just going to fill those down to fill out the series all the way down to the bottom of our different scenario combinations. So that's 50. And we can have a little sanity check there and say that 5 times 2 times 5 is 50 and that matches our possible different combinations. So that's good. Now we've got the answer matrix and scenario numbering in place. We can start building out the sales model. So for our price here, we are going to want to assume that by default we'd like it to use whatever I've got in my input area. But if there's something in the scenario cell which is gonna drive our data table, then we'd like to use something different. So we're gonna use a combination of an IF statement with a VLOOKUP inside it. So it's gonna look a little bit like this. We're gonna start equals IF, and we're gonna say IF the scenario cell C13 is not blank or equals blank, then we are going to say that we want to have D5. So whatever's in our normal input cell, if it isn't though, we want to do a VLOOKUP and we'd look up whatever is in the scenario cell. So whichever scenario number we have found and where are we going to, it should F4 that to make sure we're always looking at that cell.

Where are we going to find those items? We're going to find that somewhere in our table answer matrix that we have just built. So I'm gonna go and select all of our data. Yeah, and again, F4, the whole thing. because it's always going to be that same data set, comma. And then which column do we want? Well that's where we're going to come and choose our column number that we inputted earlier. So we're going to go and come to, in this case D16. And again, I'm just going to fix the row here, so we are always looking at the same row here. Don't forget that because we're doing a lookup and we want to do exact matching. We need a comma false at the end and then that is our function complete when we close that last bracket. So when there's nothing in the scenario cell, we can see that the cell in B5, the price is equal to 4, whichever what whatever was in D4, sorry D5. But if we start to put something into the scenario cell, so for example, if I say I'd like scenario 3, then price should change to three as well. Because if I look at scenario 3 down here, it's gonna come and look up, find the price is 3 and then put that into our little model. Let me just go and clear off the scenario cell and let's work our way down. So that was the price one done. We're going to do a very similar thing for the other two items that we want to flex. So demand and unit cost. And to save myself a little bit of time, all I'm going to do is copy the formula that I've already written, paste that into the cell underneath and then all we need to do is instead of D5, this should be now looking at D6. And then instead of D16, this should now be looking at E16. Yeah, and that's good as well. So again, 29,000 because scenario's blank. And then we're gonna do the same thing again for our final one, which is our unit cost. Paste the whole formula in instead of D5, this is now gonna be D7.

And then at the end here, instead of D16, it is now gonna be F16.

Great. For our fixed cost, that's always going to be equal to our input. So I can just link that through to our 45,000 and then I can start working out the rest of the formulas to build out my profit. So the revenue is going to be equal to the price, multiply by the demand.

The variable cost is going to be equal to the unit cost multiplied by the demand and then our profit is gonna be equal to our revenue subtract the fixed costs and subtract the variable costs to give us 57,950. Now down in our answer matrix, we are now going to build our little data table. So we're going to link in the profit into the cell in C18. So I'm just going to do equals and go and link to our formula cell, which we just built in C11. So there's our profit at the top of our data table. And the next thing we can now do is we can go and build the data table by selecting all of my different scenarios and the column that I'd like to populate with my data table. And then I can turn the data table on. And the only thing we need to do is fill the column input cell because that's what's changing down the column with our scenario cell in C13. So if you imagine what's gonna happen, Excel is going to populate that cell with each of these different scenarios, which is going to update all of our price, demand and unit cost and give us a different profit value for each of our different scenarios. Click okay, we should now get the values all filled in.

So that's our data table built. Now we need to go and build our pivot table to go and look at this information we've now constructed. So I'm going to go and select my data, so everything including the labels all the way down to the bottom. And then we're going to go to insert and pivot table.

And then we're going to say I'd want that on an existing worksheet and I'd like to go and put that at the bottom of my table. So I'm just going to come down towards the bottom of my table And let's stick it in B71, click okay.

And now we are ready to build our pivot. So if you think about what we want to achieve, we are going to want to know what our profit is. So profit must be the thing that I'm dragging into the value section and price demand and unit cost are the things that I want to go and have in my data table or my data table emulation if you like. So I'm going to do the same thing as we have had before, which is drag price into rows. So we've got the different row options down here and then I'm gonna drag demand and then unit cost underneath.

And hopefully what you can start to see is it looks a little bit like a data table now we've got to do some tidying up in terms of formatting, but at the minute you can already start to see the figures appearing and how this might resemble a data table. So what do we have to do to go and clean this up? Well, the first thing is that we need to do go and change the format to tabular form. So we we're in our design area under report layout, we're gooing to go and choose show in tabular form. This is going to compress the thing the table down a little bit and take out some of the options. The next thing is for our subtitles, we're going to say we don't want to show any subtitles. Again, this will remove some of the extra information from our pivot. We don't need grand totals either. So I'm going to go to grand totals and turn that off for both rows and columns. We don't want these zeros appearing. So under each of these different field options, we can go and filter those out. So I'm going to go and turn off the zero option for all three of my items. So that was price. I'm going to do the same thing for demand.

And then I'm going to do the same thing for unit cost. The next thing here we might want to do is have repeated labels. So under the demand, I'm going to go to field settings layout and then say, I'd like to repeat item labels, click okay. And you'll see now we get something that looks a bit more like our data table would do with our repeated values across the top. Now our pivot table's finished, we can add some conditional formatting. So let's go and select all of our cells. Go and insert some conditional formatting. Let's just add a simple color scale and we can start to see visually how some of the higher or lower numbers are arranged. Now one of the nice things about having the pivot table option here is that I can change where these items are placed to get different settings or different arrangements of my pivot data data to see how it's better displayed. So I can carry on playing with that until I'm happy with the arrangement. This is not always easy to do if this was an actual real data table. So a nice feature of having this pivot table option.

And that's how one approach people take to having three input data tables.