Have you ever used Excels What-If Analysis Data Table? How does it work and what is it good for? Well, if you watch this video, you might realize that this feature is what you need to replace your many formulas or complicated analysis. Let's quickly take a look. Okay, so, you're saving up for your retirement, and you're thinking, "how much could I afford to pay into my retirement fund each single month?". The number you come up with is $200. So, I'm putting minus 200 here, because it's a cash outflow, it's leaving your bank account and going into your fund. The number of years you have till retirement, let's say, 20 years and the yearly interest rate you could get now, let's say, is 4%. How much are you going to have in this retirement fund after 20 years? Let's do the calculation. The function you need here is the Future Value function or the FV function in Excel. The first argument we need is the rate, this is our interest rate. But here, you have to be careful. The frequency here is on a yearly basis but we are paying monthly, so we have to convert this number into a monthly value by dividing it by 12. Next is, the number of periods. Well, we have 20 years but again, we need to have this on a monthly basis, so this time we have to multiply this by 12. Next argument is the payment. Well, that's the one right here, it's 200, remember it's minus $200. The next arguments here are optional. This is your present value amount and type. It's whether it's at the end of the period or beginning of the period and default is end of the period. So, we don't need to add anything else, we just have to close the bracket, press Enter and that's our dollar amount, $73,355 is what we're going to have in our retirement fund after 20 years. Not such a great investment. A better investment that will probably give you better returns, is if you subscribe to this channel. It's free and it can pay you back with interest much better one than this. Now, what if you're hopeful and you want to know what happens if the interest rate was 4.25%, 4.5%, 6%, you don't have to go changing this number. All you need to do is build your prototype, which is what we've done here, and then create your data table to create your simulation. Our first simulation is to have different interest rates, so let's start with 3%, and then do 3.5. So, let's just drag this down until here. Your next step is to go to the cell that's beside these values, and then go one cell above. Now, bring over your prototype formula that you wrote. So, I'm just going to say equals this cell right here. You could, of course, type out the entire formula here, if you want, but because we already have it here, I'm just going to make a direct reference. So, this is what we would like to happen, I would love to be able to drag this down and have my number change by plugging in this 6.5% in here and getting the correct number beside it, right? But obviously, dragging this down is not going to magically do this. The magic is done with a data table. Here's what you need to do. You need to highlight these cells, right? So, starting from the empty cell here, then go to Data, What-If Analysis, Data Table. Now here, things can get a bit confusing because you have two empty boxes that you supposedly need to fill. One is called the row input cell and the other is the column input cell. First decision you need to make is which one do you need. Well, the way you decide that is by taking a look at what you've highlighted. Are the values that you want to simulate in a column or in a row? Well, in this case, our interest rates are in a column. So this is the box that we need to use. Next decision you need to make is, what do you put in the box. Are you supposed to highlight all of these? No, you don't select anything from the highlighted area. You go back to your prototype and you pick the input cell that represents your interest, and that was in the Prototype. Which one is that? It's this one. This represents our interest rates. Once you select that, click on OK, and we get our simulation. So, take a look at this, 4%, that's the same number that we see here. If we could have better interest rates, we would be getting better results. Now, these cells aren't formatted, so let me just quickly copy the formatting and paste it here. Now one thing that can get a bit confusing and annoying, is this number here. This was our prototype. So, you might want to hide this, you can just change the font color to white, or you can just disguise this to be something else. What I like to do is to use icons, sometimes. I'm going to use the shortcut key, Ctrl+1 to go and format the cells, so basically, number formatting, come down here to Custom. Let's just remove the current formatting we have in place, and inside quotation marks, I'm going to bring up the Windows emojis and let's find the one for money. Actually, I'll go with this one and put it in quotation marks, right? So, this emoji is like text, then click on OK and we have our emoji up there. We can change the font color to be something else. Now, another great thing about using a Data Table is that it uses an array to bring over the formulas. This means that you can't just go ahead and delete a number from the middle, it just doesn't work. I have to press Escape to leave. Now, how about if you wanted to simulate another variable as well? For example, the years. So, we want interest on one side and year on the other. Okay, so, over here on another tab, I've removed my formulas, I have the interest rate here, and I want to simulate different years up here. We're going to go with 4, 8, let's do every four years and drag this until, well, 28 years. Okay, so, we set up our table like this, the formula comes right here in this blank cell. So, we're going to do equals this value here. Next step is to highlight everything, go back to Data, What-If Analysis, Data Table. Now, which one of these do we need? Well, we need both. We're simulating the years in the rows and the interest rate in the columns. Next decision is, what do we put in here? Well, nothing from what we've highlighted. We're going back to our prototype. What do we have in the rows, these are the years, so we have to select year here. For column, our interest, and then we're going to click on OK, and we have all of our different combinations applied. Let's just copy the formatting and paste it here. Well, here's a great table we can reference. If the interest rate was really bad, if it was at 3% and we had 16 years to save, we would just end up with $49,209, whereas if the interest rate was 11%, we would get over a hundred thousand. Now again, you can hide this value, if you want. In fact, I'm just going to go and copy the formatting and apply it to this as well. One thing you need to keep in mind is that, data tables keep recalculating every time you make changes, so they can slow down your spreadsheet. There is an option that you can use to speed up your spreadsheets, and that's if you go to Formulas, Calculation Options, and select Automatic Except for Data Tables. Now, if you make changes your data table isn't recalculated. If you want recalculation to apply, just press the shortcut key F9. Okay, so, that's a quick overview of the Data Table What-If Analysis in Excel. I have to admit, it doesn't have the most user-friendly interface. Sometimes I forget what should be in the rows, what should be in the columns, but once you get the hang of it and actually use it a few times, it becomes easier. Let me know what you think below this video. That's it for today. Thank you for watching. Thank you for being here and I'm going to catch you in the next video.