How To Use Excel What If Analysis Data Table

This is extremely useful to analyze a problem in excel and figure out the best solution.

How to use excel what if analysis data table. On the data tab in the forecast group click what if analysis. Now type a 2 in the column input cell box and click on the ok button. Once select data table the below pop up will come. Click the data tab on the ribbon. Excel provides several different tools to help you perform the type of analysis that fits your needs.

For the column input cell select the interest rate c3. Or you can specify a result that you want a formula to produce and then determine what sets of values will produce that result. Now you will get the instalment amount till selected range of columns. Select data table from the drop down menu. Click what if analysis in the data tools group.

Once selected click on the data then what if analysis from that dropdown select data table. Do the analysis with the what if analysis tool data table select the range of cells that contains the formula and the two sets of values that you want to substitute i e. You can use excel s goal seek feature to find the answer. Sometimes a formula depends on multiple inputs and you d like to see how different inputs values would impact the result. The data table is perfect for that situation.

The data table function is a function that allows a table of what if questions to be posed and answered simply and is useful in simple what if questions sensitivity analysis variance analysis and even monte carlo stochastic analysis of real life model within excel. Select the range f2 l13. For the row input cell select the term input c4. Under the data tools section press the what if analysis button. Now select the entire table to apply data table of what if analysis as shown in the below screenshot.

Click on the what if analysis option and select data table option. For example you can do what if analysis to build two budgets that each assumes a certain level of revenue.