here I'll show you how to do a sensitivity analysis in Excel it's going to allow you to view a list of results a list of outputs based off of a changing series of inputs effectively allowing allowing you to stress test or determine the sensitivity of your numbers how much they're going to change when you change their inputs in Excel this is called a data table not a terrifically helpful or descriptive name but it's a really powerful feature before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials here we'll work on two different data sets and the first one is a super super simple data set with only three things going on we have price quantity and total so simple number here five another number 20 and then just a tiny little formula that multiplies the two together so I want to make it as simple as possible because your data up here can get as complex as it needs to be that is irrelevant because you're going to set it up the exact same way for the data table so let's go ahead and make what's called a one variable data table that means that we're going to change one input and then view the result of those changes and in order for this to work and any data table to work you need to have data where you have inputs like price and quantity their inputs and a cell that utilizes those inputs so this cell must use whatever cell we would like to change up here because well just think about it if total down here is hard-coded and it doesn't use these cells up here changing these cells will never change the total so they must always talk to one another or this must always use the inputs okay so now let's go with how you set it up it's a very specific format we need to make two columns I'm going to give them titles you do not have to give them titles but you will very quickly see how important it is to do that so we have quantity and we have total now what I want to do is I want to change the quantity over here and see the resulting change in total over here now the first row under these headers is reserved so it's reserved to be over here I'm just going to write the word original it's going to be the original value and you'll understand what that means a moment and over here what you want to do is you want to link it to the cell that has the result which we would like to be viewing I want to view the total result so I link it to that cell now in some examples you will see where what they do over here where I typed original as they simply just put 20 so I have 20 up here I just put 20 down there you do not need to do that I don't particularly like that format because you don't always want this guy up here to be the bottom of what you're going to be stress testing so now over here this is where you put your increments so I want to test for quantity of one quantity of 5 10 15 and 20 so this is the basic one variable setup you have a column of values that you're going to change these are the inputs you're going to use you're going to be changing leave the top one at original easy-peasy and right here the formula that we want to use to see the result so basically the result that you want to see maybe I made that sound a little confusing the result you want to see right here and a link it to your data set now all you do is to select these dudes like this oops select them go to the data tab what-if analysis data table and over here this is an easy one to forget you only have two inputs but which one is it a row input or a column input just think about it like this right now we're going up and down that's a column so column input cell now this is the value to change this is what we want to change to affect the total so we want to change the quantity so we select the quantity sell up here because what Excel is going to do is it's going to change this a bunch of times and record the result of that change as it relates to total so we hit OK and there we go look at that so if I have a quantity of 1 with a price of 5 I have five dollars or 25 50 75 100 and you can see that our values up here 20 and 100 20 and 100 that's what they appear you can format this if you want so we could go ahead and put some dollar signs down here however you want to do it the one thing to note you cannot this is a special set of data to data table you can't just delete one so if I go to delete this can't change part of a data table so what you could do if you want to delete everything or what you have to do is select all the cells at once and then delete it if you want it back just select the data data tab what-if analysis that that's a table column quantity okay now that's the basic one that's the one variable one I'm gonna show you two variations of this right now because you're going to see people using them in different ways I prefer to use it just how I showed you for one variable but co-workers don't always agree so let's say we want to keep it just like this okay okay now original okay total so the thing is this cell right here for total for total does not actually have to link to this cell it just has to be a formula so you could actually just copy this formula and put it right in here then select the data what if data table column input cell quantity okay it works everything's good everyone's happy right well the problem is when you want to change this later on you've now got to change let's say you want to change this formula you now have to change it here and here and you're never going to remember that or someone else that gets a workbook isn't going to remember that so this bad way to do it this good way because you linked this cell so if you want to change this formula in the future you just change it in one place now let's do one other variation you won't see it too often but you might see it and that is that we don't have to do it up and down we can do it left and right across the columns in a row so basically all we're going to do is to transpose this so you could do quantity total okay old original goes up here remove the bold total want it to equal this cell okay and for quantity I'm going to quickly transpose this control-c go over here alt e s e enter awesome little shortcut now select the data data tab what if that's a table this time it is a row input instead of column input because we're going across a row across the rows instead of up and down in a column so a row input quantity okay and there we go nice meat and as expected you're not going to see it too often this way left to right but I just want to show you that you can do it that way so the only thing that's really easy to forget here really is the set up of these guys right here and the fact that this needs to be a formula now let's go to the two variable setup which is pretty similar just a tiny bit different and that allows us to change the price and the quantity to see its effect on the total amount all right down here to the variable okay so there are many different ways to set up the titles by the way as you may have noticed titles right here these ones are pretty logical but for the to a variable one it can be a little bit confusing you can you might want to set up your time you'll see in a moment I'll just show you actually quantity right here then let's go over and up this will be a formula so I'll just type it in there as a placeholder and over and up price now it's sometimes helpful to merge and center and change the alignment so quantity will go up and down like this and you can merge and center price if you want it doesn't really matter but you'll see in a moment how that can be kind of confusing because quantity here we're not going to put below it so it's the same as before quantity is going to go up and down here so 1 5 10 15 20 price now this is what price do we want to test so what we can do here let's just put whatever you want to say 2 3 let's just go up by one okay so 8 bucks so now it's going to test the price from 2 to 8 dollars and the quantity 1 to 20 and our formula so I just put this here as a placeholder to more easily set up the data the formula is exactly what we have here so with the 100 so all we want to do is to equal sign go up here enter so this right here is the result that we want to see based off changing these or the formula we want to see the result of this formula when we change these inputs maybe that sounds a little confusing so let's just do it just like before we want to make sure to select the formula along with the inputs so the inputs down here the only reason that before we selected original as well so original was selected is because that was just a placeholder value we didn't really want to put anything there here we don't need a placeholder value because of how the is set up so you select the inputs along with the formula and we go to data what-if analysis data table okay this time we have a row input and a column input so the row input we want to change price and the column input we want to change quantity all right hit OK and bam so if I have a one book at $2 my total will be $2 one book at five is five fifteen books at five is 75 we can go ahead and change the formatting make it however you want and now you have a two-way data table so the easiest thing to forget the hardest thing to remember here is just how you set up the data okay so with the one variable just remember headers what you want to change right here what you want to see once you change that input right here so this is what's going to be changed this will be the result of the change first cell here always a formula which should just link to the data set now when you go for the two-way one just think about it diagonally okay this is the data in the left column this is your formula this is the data in the top row just Bam Bam Bam do those three first then input your values like this and like this and I find at least that makes it a little bit easier to remember how to do this now that's it for the basic one and two variable data table sensitivity analysis it's great I love it but before I go I'm going to show you one really cool thing which is that we can do it with multiple values so here we just did it with the total but let's say that we wanted to see the impact of changing the quantity on different formulas multiple formulas so let's go to the multiple values tab now here we have something just a little bit more complex not too much it's like a loan or a mortgage calculator you're going to see this in most of the results online so this is the loan amount this is how long it is for here it's in years doesn't actually matter the rate and here this is what matters we have three results calculated based off of these inputs up here so before we only had one result it was calculated the total one result that's all here we have three results based off of these inputs and we can check all three of them when we change one of the inputs so I'll do the test one real quick let's say we want to see the rate or change the rate and see how that affects the payment remember this our original and over here we just a link to the result we would like to see which is this formula I want to see how that changes and then over here we just input let's say two and 2.5 so I want to change the rate from 2 to 6% select the data that tab what if data table column input cell so what are we going to change well I'm going to change the rate okay and there we go and you can see the 5% rate five thirty six eighty two the same five thirty six 82 so now two with multiple results it's almost exactly the same it's really cool I don't know why more people don't use this part of this feature I want to see the payment but now let's go and check out the total paid and the interest paid so it's almost the same we just add two more columns do original following the same pattern we linked this payment - payment total paid total paid interest paid interest paid now I'm just going to copy these dudes so when I test it for the same interest rates and we're almost done all you do select the data so select the formulas up here and the values that will be changed over here what if that's a table column input the same all we are changing is rate okay and there you go how cool is that and here we have the numbers 193 255 78 sames here same as here all is good so now you can see that it's it's pretty darn cool we've got a bunch of different stuff here and if you want you can go ahead and change some stuff up here so change the loan amount to and you're going to see all the values changing down here and I got to say I love this feature it's very simple but it's very powerful you can quickly sift through the values especially with the two-way one down here it's very very helpful especially here these were all were linear examples where the data changed by a predictable amount but that's not always the case especially when you have massive calculations it can be very helpful now when a caveat is it can also slow down your spreadsheet because you may have noticed let me zoom out a little bit when you do anything in the spreadsheet go down here hit enter everything updates you can see all of the data tables updating and that can slow things down so if it does go to the formulas tab calculation options and check automatic except for data tables so what that means is when I go here hit enter the data tables do not automatically update or change if you want to get that feature back just go here formulas tab calculation options automatic now you'll see everything changes when I input anything in the cell so if you turn it off just remember to turn it back on when you need it and that pretty much does it for this tutorial so memorize the format of single and double variable data tables and you'll be able to do this as quickly as anything in Excel it's a great little feature I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials