Transcript:
What’s an Excel data table? Well, it’s much more than just a table of data. Let’s get into it. Data tables are a super powerful feature of Excel that a lot of people just don’t understand and a lot of people don’t use at all in this two video series we’re first going to look at a simple example to get you understanding the concepts of a data table, but then we’re going to look at an application of data table from a real-life example. Let’s get straight into the download file, So here’s our simple example. It’s going to help you understand the concepts. The first thing we need to understand with data tables is we have to have a model and a model consists of an input, a process and an output. In this example. We’ve got the simplest possible model. We’ve got a simple multiplication calculation. 3 X 4 equals 12 But the important thing is, you need some kind of model in order to get started in the second video. We’re going to see a real-life model and we’re going to use those tables with back. So if we’ve got a model, we can then say, OK? I want to put different values into the model and understand what the outputs are from those values. We could do this manually So I can just go to the model here. Take input 1 and put 1 in the model. Updates put 2 in the model updates. Put 3 in the model updates. I can do that manually. But what if I wanted to test? 50 100 500 values That’s going to take a very long time. A data table Does it at the click of a button super powerful. So how do we set one up? Well, let’s start, with example, a at the top here. We have One-way and Two-way data tables and a one-way data table can be in column orientation as we’re going to see an example. B can be in row orientation as we’re gonna see, In example, a now we’ve got to be careful with the initial setup. How we lay things out is important. If it’s not laid out properly, it’s not going to work, so we’re having a one-way data table we’re testing a single input in this case Input 1 so we’re going to take all of these values and Excel is going to put them into our model. So these candidate values are the values that are going to go into the model, so we need a table and we need some candidate values in a row, so that’s what we’re going to set up first. Then we have to link the table to the model. We have to say to excel. This is the output cell from the model. This is what I want you to look at and write all those outputs down in the table. We do that by hitting equals and creating a very simple formula to link this cell to the output cell in the model. It does have to be this cell the positioning. The orientation is super important. Once we’ve got this set up, we can then create the data table exciting. We do that by selecting. Everything we’ve just created that selection is important. So do it just how? I’ve done that, and then we can go to data. I’m going to switch to the whole desktop view here. We can go to data and then What-if analysis because this is a what-if analysis were saying to Excel. What if the value was 3 What if the value was 4 what would the output from the model be? So it makes sense that it’s a what-if analysis. Then we go to data table, and that gives us our data table dialog box now. I’m just going to switch back to my demonstration view here That gives us the data table dialog box Alt AWT alts AWT on the windows PC. Now this dialog box looks simple, super confusing. This must have caused people, hours and hours of trouble over the years. It certainly cause me some trouble right, so do we. Nero, doc, row or column. What orientation is a cable in as our dates are orientated in a column, or is it oriented in a row? We can see it’s oriented in a row, so we’re concerned about the row input cell. Excel is saying to us these values that you’ve put in a row. Where do they go into? The model? We’re currently concerned with input 1 so we’re gonna go to the input 1 cell, so we’ve got row inputs. They’re oriented in a row and we want. Excel to put them in this cell here. So I’ve just clicked on cell d5 I can see it in the dialog box. Now we’re ready to go. We’re doing a one-way data table, so we only need a single input so we can hit. OK, now and I can see. We’ve got some values in the table. And, moreover, when I click on these values, I can see in the formula editing bar that there is a table there, but what does it all mean well? Excel has taken the value of 1 Put it into the model, which I’m going to do now and then just written down the output here. Excel has taken the value of 6 put it into the model and then written down the output here, which is 24 so it’s doing a huge amount of work for us, knocking, not particularly exciting in this example, because it’s just a simple multiplication, but we’ll get on to a more exciting example in the next video, so that’s this example, a one-way data table with row inputs. So what about one-way dates table with a column input? This is essentially the same thing, but the layout is different. Our candidate values that we can see here. These are now in a column, but it’s going to do exactly the same thing. So what are the sets we have to work through? We have to make sure we have our candidate. Values there. Then we have to tell. Excel, where is the output cell in the model and we do that at the top of the column because they’re in column orientation. Now, so I’m hit the equals key, and I’ve gone to cell. D9 hit, enter and we can see those two cells are now linked by this very simple formula and now. I’m ready! I’ve got this set up, but this set up is important. If there’s an empty column in there or something, it’s not gonna work. Let’s get that set up, right, that’s gonna say save as stress later on. I’ve selected everything, including that top row. I’m gonna use the windows shortcut alt AWT. But you can go to the data Tab What-if analysis so again, we’ve got this simple but tricky dialog box. So now we have column inputs. Our candidate inputs are in the column, so we’re not going to put anything in the road. We’re going to go to the column input cell. This time we’re interested in input two, so I’m just gonna click on cell d7 which is where the input to value is. It’s okay and again. We’ve got our data table here so. Excel has taken this value of 10 put it in the model in input 2 and that gives us an output of 60 so it’s tested all about those candidate values written down the output, says done a lot of work for us there, so that’s one way dates tell with row orientation, one way data table with column orientation, which one is best row or column. That depends on the setup of your spreadsheet. How is it laid out? What fits in best, but they’re essentially doing the same thing, so let’s move on to a more powerful beast now. The two-way data table. I’ve said it’s more powerful, but it’s doing the same thing. It’s gonna test two input cells. Two input cells this time, okay. How does it work? We need this sets up, so we’ve got candidate values for one of the inputs in the column. Candidate values for one of the inputs in the rope. We need those kind of eight values that we need. This set up in that soft Let top left cell has to be in the top left cell. We’re going to make that link to the output cell so equals. I’m going to navigate with the keyboard equals d9 so the model is now linked. Our dates table is now linked to the output cell, so we’re now ready to create our two-way data table. We select everything and then we’re going to go Data What-if analysis alts AWT on the Windows PC. Now we’ve got our dialog box. This time we’re going to use both inputs because it’s a two-way data table. It has two inputs, so the row input cell. Well, that’s input one these values. I want to put into the model as input one and then these candidate values, which are in the column. I want to put into the model as inputs. Ooh, so I’ve made the connections there with the cell’s cell d5 cell, d7 and now. I’m just going to hit. OK, and what’s happened. Well, let’s look over here. So if we put 21 into input 1 and then let’s put 8 into input 2 here 8 into inputs. Ooh, we can say we we get 168 Which is the value here, that’s all. Excel is done. Taking all those values put them into the model created a table of all of the outputs. It’s called a data table with lots of things in Excel. Sometimes the names aren’t helpful. I’ll call it an output table or even an optimization table. That sounds interesting using this technique for optimization. I hope you’ll join me in the next video [Music].