Excel Data Tables Advanced

Use unlimited variables and create unlimited results! Magic!

Data Tables are used to calculate a defined result (e.g. Year 5 profit), for different values of either 1 variable (e.g. annual sales growth %) or 2 variables (e.g. ...and also £/$ exchange rate). But what if you've got 3 variables to sensitise, or your boss is interested in more than 1 result? That's where this video comes in. It looks at an amazing (but advanced) technique that allows you to effectively push as many variables as you want through them, and get back many results.

Note, this video is conceptually quite difficult and relies on a solid understanding of how Data Tables work, the INDEX function, and a technique for creating scenarios and pushing their assumptions through a model.

Check out the Downloads section of the website if you’d like to download the associated file to this video.

Transcript

Welcome to my Advanced Data Tables video - Data Table Magic! I'm Dan Stockdale! If you're not familiar with Data Tables I suggest you first check out my Data Tables Intro video - the link is below. There I explain 1 and 2-variable Data Tables. In this video we'll look at a technique to push as many variables as we want through a 1-variable data table. Which sounds completely counter-intuitive. Then we'll look at how to not only push many variables through, but also get many different defined results returned. Not just the traditional iterations of the single defined result that we'd usually get. We'll be using a 2-variable data table for that.

I'll warn you, this video is conceptually quite difficult and requires an understanding of how data tables work and a technique I use to create scenarios that uses the INDEX function - which I have a video on. I'll flash up a link to that Scenarios video and an INDEX video shortly, and the links can be found below in case you want to take more of a look. Anyway, let's get to it!

We've seen in my Data Tables Intro video how a data table can take up to 2 variables but what if we want to see a result with all 4 variables changing? We're going to do this using a 1-variable data table, which sounds completely counter intuitive but the way we're going to do it is that rather than having the data table change any of these inputs directly we're going to have it change a scenario input.

So to explain, here we have a 1-variable data table and down the side we have the scenario numbers 1 to 10. Now previously we were pushing the data table's inputs through the Quantity Sold input. This time we're going to be pushing the scenario numbers 1 to 10 through the scenario number input. On the right we've a Variables Scenario section with Price, Quantity, Price growth % and Quantity growth %. For each scenario we have a corresponding set of variables. So for Scenario 1 we have these variables Scenario 2 we have these variables, and so on.

Now how am I going to do this? I'll start by putting a 1 in this scenario number input. Then I'll put an INDEX function here to look up the corresponding set of variables for the scenario. If you're not familiar with the INDEX function, then it's absolutely crucial for advanced analysis and modelling. I've got a video you can check out on it.

I'll start by putting an INDEX. The array is this set of variables and the row number is the scenario input. I'll anchor the column, close the brackets and press ENTER. Then I'll copy and paste formulas and now this INDEX function is looking up this 1st row of variables. If I change this to a 2 it looks up the 2nd set of variables and you can see that's 100, 800, 15% and 20%. So that's this 100, 800, 15% and 20%. So I can change this to any of the scenario numbers and it will look up the corresponding set of variables.

Now, the data table will be cycling through the scenario number variables and pushing them through the scenario number input, which in turn will be updating the INDEX functions here to look up the corresponding set of variables for the scenario.

Now, we're interested in getting the Year 5 revenue result into these blue cells but right now this part of the model is quite static in that all these inputs are hard-coded. So we want to make a linkage between this part of the model which is quite dynamic and these calculations. We'll do this by linking these cells here to their corresponding cells over here. So I'll link that up now.

Now, these aren't really hard-coded inputs anymore so to help us understand what's going on I'll colour them green as they're kind of calculated inputs. And then these here are also calculated inputs because they're looking up from the variables below. So I'll also highlight those green. You can see if I change this scenario number it updates these cells by looking up the associated variables and will push them through these calculations which then update and we get a new Year 5 revenue. So I'll just show that. You can see everything's flowing through.

It's important to note that when the data table is pushing variables 1 to 10 through this and everything is updating, it will all be in memory - so you wouldn't actually see it update.

The final step here is to link this to the output we're interested in, which is the Year 5 revenue and then turn this into a data table. So we'll go Data -> What-If Analysis -> Data Table and then it's the Column Input Cell because the variables are arranged in a column. And we need to link that to the scenario number and we press OK. We can see the results are coming through here.

So to recap what's going on - the data table in memory (so we don't see it happening) is pushing these numbers 1 to 10 through the scenario number input, which in turn is updating these green cells over here to look up the associated set of variables for the scenario. These cells are linked to these green cells so these calculations up here will update, and then the Year 5 revenue here is being output to the data table.

We can test this just by changing this scenario number. If I change this to 1 we can see the 1st set of variables are picked up here. Then they're linked here. And then we get the result 242,877 which we see in the data table. If I was to change this to 10 we're looking for the 408,008 to update here. So if I change that to 10 you can see these have all updated and we've got the 408,008.

We've seen so far how we can push multiple variables through the data table to get 1 result - Year 5 revenue. But what if we want to push multiple variables through and get multiple results back? Like the revenues for Years 1 to 5. We can do this by extending the technique we've just used. We're going to do this using a 2-variable data table, and the 2nd variable here is going to be a year number which will kind of be like a results scenario number. It will correspond to the Years 1 to 5 and in turn the revenues for Years 1 to 5. All of these outputs are going to be showing in the data table so I'll colour them blue.

Now, the problem we have is that previously we were linking this cell to the Year 5 revenue but now it needs to be able to show any of these values - it needs to be more dynamic. We'll get around this by using another INDEX function and I'll start by putting a 1 in this result input. I'll then put an INDEX, and the array will be the 5 years' revenues. The column number is going to be the result here. So you can see right now it returns the revenue for Year 1. If I change that to 5 it returns the revenue for Year 5.

So when this data table is complete, as before it will be cycling through these numbers 1 to 10, pushing them through the scenario number, which will be updating these variables and pushing them through this model and we'll get the different revenues for each of the scenarios. At the same time the data table will be pushing these numbers 1 to 5 through the result number input which will then be updating the output actually being looked up - Years 1 to 5. And they'll all be displayed down here.

So we'll complete this data table by selecting the whole thing, going to Data -> What-If Analysis -> Data Table. The Row Input Cell will be these variables - so we select the result input. The Column Input Cell relates to these inputs - so we select the scenario number input. Press ENTER. And you can see the data table has updated.

We can test this - Scenario 1 right now is selected and so these results correspond to this 1st row. I change this to Scenario 2, the variables push through, we get these results, which correspond to the 2nd row. If I change that to 10 then the variables for 10 are pushed through the model, this will update the results and then they're being shown in the bottom row.

Well done if you made it this far - that was tough! But if you manage to bang that out at work you're going to look like an absolute king and you're probably going to get a promotion ;) I hope you enjoyed that! If you liked it, please Like the video. If you want to see more, then Subscribe. Or if you want to download the working file to this then visit my website at the link below and take a closer look. Thank you for watching and goodbye.