Excel data tables are one of the whatif analysis tools that we have available to Aid our decision making for example we could compare the effect of saving different amounts each month or calculate a break even point the great thing about data tables is they require zero formula knowledge and they're super quick to build let's take a look the first example I want to look at is the effect of increasing a monthly saving amount by $10 in cc9 I've used the FV function to calculate the future value of 12 deposits of $100 at 5% interest perom you can see the formula is referencing cell C3 C4 and C5 now I'm curious to know the impact of increasing the saving amount by $10 and an easy way to do this is with a data table I'll start with $100 in this sale and I'm just going to use the fill series tool on the Home tab series and I want to go down the column step the value by 10 and stop when we get to 200 so there's my list of savings amounts and to insert a data table I simply select the table including the headers and the column labels that include my values and then on the data tab of the ribbon in what if analysis I want data table in this case my data table is column orientated that is the list of values is in a column so I'm going to use the column input cell which is the amount and the cell containing the amount my formula references is C5 if my data table was row orientated that is these amounts were going across the row then I would reference C5 in this field here so I'll click okay and there's my data table if I select the first cell and you look in the formula bar you can see the formula uses the table function and it references cell C5 and although this looks like a regular form formula you can't actually enter it by typing it in now because this data table is essentially made with formulas you can modify the inputs and it will automatically recalculate for example I could make the annual rate 6% and you can see the data table has automatically updated now if you wanted to compare changes to the saving amount and the interest rate you can create two variable data table for two variable tables the formula must be in the top left cell of the table and again you can see I've used the FV function to calculate the future value of 12 Savings of $100 per month at an annual rate of 5% the process is the same select all the cells including the row labels and the column headers and then on the data tab what if analysis and data table so here I have both a row input cell and a column input cell the row input cell is my interest rate and the column input cell is my saving amount click okay and there's my data table if we look in the formula bar you can see it references two celles the annual interest rate in cellc 3 and the savings amount in cellc 5 and just like before we can change the inputs so for example here I could make this 9% and if you keep your eye on the calculations for this column you can see it updates automatically another cool use for data tables is to calculate and visualize break even for example here I have data for a fictitious pizza shop the fixed costs are $66,000 per month and my profit per Pizza is $435 and if I make 1380 pizzas I'll make $3 for the month which is essentially Break Even however if I sell more or less pizzas and make more or less profit per Pizza my breake even will change so we can use a data table to visualize this again selecting the column labels and the row headers what if analysis data table the row input that's the profit per Pizza which is there and the column is the number of pizzas per month click okay and there's my data table now we can use conditional formatting to make this even easier to read so on the Home tab conditional formatting color scales and then this first one here gives us an at a glance view of the different Break Even points and we can see it starts here with 1,500 pizzas per month at $4 profit per pizza right through to 1,200 pizzas per month at $5 profit per pizza now one last tip if we look at the formula in the top left corner of the data table you can see it's actually just referencing another cell but if we look at the cell it's referencing you can see that cell contains the formula so this cell here doesn't have to contain the formula but it must reference a cell that does and Excel will be able to trace that back and create the data table according to the inputs in this formula here now if you have large data tables and you find they're slowing down your workbook you can set them to calculate manually via the file Tab and then options in the formulas tab we can choose automatic except for data tables when you have this turned on and I won't do it here you can recalculate the data tables by selecting all the cells and then pressing in the F9 key to trigger a recalculation well I hope you found data tables useful you can download the Excel file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to my channel for more and why not share with your friends who might also find it useful thanks for [Music] watching