Home // Video Tutorials // 6 - 10 (Advanced) // 8 // 3 Way Data Table Excel

3 Way Data Table Excel

A method to create three way data tables in Excel using the normal data table feature which normally only allows a two way data table.

NEW: Get the templates from the Multi Variable Data Tables Tutorial.

To follow along with the video, download the spreadsheet below

Three-way-data-table-excel-spreadsheet

If you are aware of data tables in excel, you’ll know that it is possible to create a two- way data table for a particular model. So in this very simple example I have got a principal amount, a term and an interest rate and I want to work out the repayment amount. The data table shows me what the combination of principal and interest rate will be. So at any point here I can look and assess what the repayment will be given a certain principal and a certain interest rate. Now what happens if you want to create a 3 way data table Excel, or a four or five-way data table?

So for example, I want to compare these principal amounts with these terms, with these interest rates. This cannot be done in the normal data table way and you basically have to “trick” excel into doing it.

In order to create a 3 way data table you actually have to forget about the two-way data table and go back to a one-way data table. The setup is quite important. So you will see we have got the same information, but I have created another area here. What I’ve done is I’ve given it a scenario number and I’ve created one to twenty seven and you’ll see why just now. I’ve then got a space and I’ve linked my answer into our answer cell. This is very important. What I’ve done is I’ve created a matrix of combinations, so you’ll notice that the first one shows the principal, the term and the interest rate. The second one and the first two are the same, only that one changes. So what it is doing is going through and creating every possible combination of these three variables and their three different options – hence the twenty seven scenarios which is three x three x three.

Once that is set up you need to actually set up so that your input cells – in this case I am using a VLOOKUP and I have created a little scenario button here. What this is going to do is the data table is actually going to run through these scenarios and depending on what scenario you have chosen the VLOOKUP is now going, looking at the scenario and pulling through the relevant variable in that sensitivity.

Now to create the data table, you do the normal thing, you’ll see I only highlight the scenarios and our payment column – these are just useful for the calculations up here. Go to data, WHATIF analysis, data table- you go to the column input cell and I tell it to look here. When I click ENTER, you’ll see it runs through and creates these answers. If you look closely now, what this has done, is for every scenario, each of these items it has given us an answer. The first one, those options have given us 836 which is the same as this number here. The next one the only thing that changes is the percentage from 8 to 10 – this answer. And so it goes through and every possible combination is at rest.

The problem now is how to present this information. The two-way data table is a simple matrix –reasonably easy to present. Here we have now got three variables and you can see it can be a little confusing to look at. So one way of doing it is you might just put a data filter on. As you can see now you can decide what are all the results for – for example principal of R100 000 – and here is all the possible results with the various combination of terms and interest. However this is not the nicest way and the way that we think is best is by using a pivot table.

For a pivot table what we can do is highlight this whole area – in this case we include the scenario number, the data table and these inputs – that is why they have been put in this order. We go INSERT and create a pivot table – we are happy with that – and I am going to go and put it into the existing worksheet – and I will just put it here at the bottom –I am going to say OK – we are now almost ready to start.

OK, so let’s create a simple pivot quickly- I am going to take the principal – just put it across the top, the term down the side and interest rate over here. I prefer to do this in a table layout – just going to go to tabular – show it there. In this case we don’t need these sub- totals – OK. What you can see is that I have now got a bit of a matrix set up – the term is here – don’t worry about these zeros– we will remove them just now – you can see that we have got the ability to show 180 months at 8, 10 and 12 per cent for principal of zero – don’t worry about that –R 100 000, R105, R110. Let’s get our answer in – I am going to pull the payment in – I have got that. I am just going to quickly format it so it looks a little bit better. OK. Now because of the way we have had to set up data tables over here, what we need to do is it is going to always bring through this row here. I am just going to remove that and I can go to any one of them- just switch off this zero. So what I have got now is a comprehensive data table – again it can look a little bit complex but watch what we can do when we add some conditional formatting in. Set it up like that – do a three colour scale and in this case it will look like that – in this case we don’t really need the grand total either. OK.

So what we’ve got here is a view that we can quickly see where the worst number is – the dark reds – and where the best numbers are. So with one view I know that it will be a bad idea to take a 180 month loan at 12% if it is R110 000. The best would be at 8%, 180 months R100 000 but in fact the darkest green is over here. The benefit of a pivot table is if this doesn’t quite work the way we want to – let’s try move this around – I am going to take the interest rate before the term – OK – we just have to remove these sub-totals – just put the conditional formatting back on – OK – so that looks a little bit better. You can see that all the darker reds are congregated over here – so it looks like interest rate has a big bearing to play. What is nice now is that it is relatively easy to drag these things around and to find the one that provides the best view. So looking here – I believe it is relatively easy to see that’s the best we can do – that’s the worst. And simply by looking at what the headings are you can tell what combination of inputs achieve that. And perhaps making it a little bit easier to use – in Excel 2010, what we may do is just repeat the item labels, so that we can at any stage look up or across to see what the combination of inputs are.

You can use the same technique to create a four-way-data-table – so here same example – I have just included a future value of bullet payments in the calculation. Again we just need to set this up. Now because you have got four variables, with three options in each – you will see we now need 81 scenarios- but this has been all set up and we have created the pivot already. And if you look here we have a pivot table, conditional formatting put on. We have done a bit of sorting just to make the variables make the most sense and you can immediately see that here is the cheapest (dark green). Just by looking at the intersection you can see that this is with a bullet payment of R40 000, 8% interest rate which is the lowest , a term of 240 which is the longest and a principal amount or present value of R80 000 which is the lowest. So you can now look at the combination of results and see where you are comfortable playing and what combinations actually allow for that.