How To Use Excel What If Analysis Data Table

For the row input cell select the term input c4.

How to use excel what if analysis data table. On the data tab in the forecast group click what if analysis. This is extremely useful to analyze a problem in excel and figure out the best solution. Click the data tab on the ribbon. Now select the entire table to apply data table of what if analysis as shown in the below screenshot. Select data table from the drop down menu.

For example you can do what if analysis to build two budgets that each assumes a certain level of revenue. Excel provides several different tools to help you perform the type of analysis that fits your needs. Now you will get the instalment amount till selected range of columns. 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. Now type a 2 in the column input cell box and click on the ok button. On the data tab in the forecast group click what if analysis. Under the data tools section press the what if analysis button. And the data table analysis is one of them.

Or you can specify a result that you want a formula to produce and then determine what sets of values will produce that result. 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. Once selected click on the data then what if analysis from that dropdown select data table. Select the range f2 l13. Click on the what if analysis option and select data table option.

The data table is perfect for that situation. Once select data table the below pop up will come.