How To Use Excel What If Analysis Data Table

On the data tab in the forecast group click what if analysis.

How to use excel what if analysis data table. The data table is perfect for that situation. For the row input cell select the term input c4. Once selected click on the data then what if analysis from that dropdown select data table. Select the range f2 l13. Click what if analysis in the data tools group.

For example you can do what if analysis to build two budgets that each assumes a certain level of revenue. Now you will get the instalment amount till selected range of columns. Or you can specify a result that you want a formula to produce and then determine what sets of values will produce that result. On the data tab in the forecast group click what if analysis. Excel provides several different tools to help you perform the type of analysis that fits your needs.

Select data table from the drop down menu. Sometimes a formula depends on multiple inputs and you d like to see how different inputs values would impact the result. Once select data table the below pop up will come. Under the data tools section press the what if analysis button. 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.

Now select the entire table to apply data table of what if analysis as shown in the below screenshot. For the column input cell select the interest rate c3. 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. And the data table analysis is one of them. This is extremely useful to analyze a problem in excel and figure out the best solution.

It will display data table dialogue box. Click the data tab on the ribbon.