Excel has many powerful tools, including the What-If Analysis, which helps you perform different types of mathematical calculations. It allows you to experiment with different formula parameters to explore the effects of variations in the result.
So, instead of dealing with complex mathematical calculations to get the answers you seek, you can simply use the What-If Analysis in Excel.
When Is What-If Analysis Used?
You should use the What-If Analysis if you would like to see how a change of value in your cells will affect the outcome of the formulas in your worksheet.
Excel provides different tools that will help you perform all sorts of analyses that fit your needs. So it all comes down to what you want.
For example, you can use the What-If Analysis if you want to build two budgets, both of which will require a certain level of revenue. With this tool, you could also determine what sets of values you need in order to produce a certain result.
How to Use What-If Analysis Tools in Excel
There are three kinds of What-If Analysis tools that you have in Excel: Goal Seek, Scenarios, and Data Tables.
Goal Seek
When you create a function or a formula in Excel, you put different parts together to get appropriate results. However, Goal Seek works oppositely, as you get to start with the result you want.
Goal Seek is most often used if you want to know the value that you need to get a certain result. For example, if you want to calculate the grade, you would need to get in school in order for you to pass the class.
A simple example would look like this. If you would like your final grade to have an average of 70 points, you first need to calculate your overall average, including the empty cell.
You can do that with this function:
=AVERAGE(B2:B6)
Once you know your average, you should go to Data > What-If Analysis > Goal Seek. Then calculate the Goal Seek using the information that you have. In this case, that would be:
If you want to learn more about it, you can check out our following guide on Goal Seek.
Scenarios
In Excel, Scenarios allow you to substitute values for multiple cells simultaneously (up to 32). You can create many Scenarios and compare them without having to change the values manually.
For example, if you have the worst and the best-case scenarios, you can use the Scenario Manager in Excel to create both of these scenarios.
In both scenarios, you need to specify the cells that change values, and the values that might be used for that scenario. You can find a good example of this on Microsoft’s website.
Data Tables
Unlike Goal Seek or Scenarios, this option allows you to see multiple results simultaneously. You can replace one or two variables in a formula with as many different values as you like and then see the results in a table.
This makes it easy to examine a range of possibilities with just one glance. However, a Data Table cannot accommodate more than 2 variables. If that is what you were hoping for, you should use Scenarios instead.
Visualize Your Data With What-If Analysis in Excel
If you use Excel frequently, you will discover many formulas and functions that make life easier. The What-If Analysis is just one of many examples that can simplify your most complex mathematical calculations.
With the What-If Analysis in Excel, you get to experiment with different answers to the same question, even if your data is incomplete. To get the most out of the What-If Analysis, you should be fairly comfortable with functions and formulas in Excel.