# What is What-If Analysis in Excel? (2023)

If you’re working on an Excel sheet and maintaining multiple parameters, it must be challenging to track all of them simultaneously. Let’s say you have a product and wish to analyze the profit on its sales using different sales prices and units sold. How would you do that? This is precisely where a What-If analysis in Excel can save you a lot of time.

Still confused? Imagine you’re planning a road trip to a famous tourist destination. Naturally, you will consider the distance, fuel consumption, travel, and departure time for various commuting options, budgets, and alternatives. Before finalizing the plan, you will consider different scenarios and see which one suits you best. This is exactly how Excel’s what-if analysis helps in analyzing data.

• What-If Analysis in Excel
• Importance of What-If Analysis in Decision-Making
• Scenario Evaluation
• Risk Assessment
• Resource Allocation
• Forecasting
• Data Analysis Tools in Excel
• Different Types of What-If Analysis In Excel
• Using What-If Analysis for Sensitivity Analysis
• Conducting Sensitivity Analysis in Excel
• Assessing the Impact of Changing Variables
• Scenario Analysis with Scenario Manager
• Integrating Scenarios with Scenario Manager
• Analyzing Different Scenarios
• What-If Analysis with Data Tables
• Using Data Tables for What-If Analysis
• Creating and Interpreting Data Tables
• What-If Analysis with Goal Seek
• Applying Goal Seek for What-If Analysis
• Setting Up Goal Seek Analysis
• Using Goal Seek to Find Desired Results
• Using Solver For Complex Scenarios
• Incorporating Multiple Variables in What-If Analysis
• Limitations and Best Practices of What-If Analysis
• Understanding the Limitations of What-If Analysis
• Following Best Practices for Accurate Analysis
• Conclusion

## What-If Analysis in Excel

As the name suggests, what-if analysis in Excel is simply finding answers to “what happens if we do this.” It is the process of observing or analyzing how changing cell values impacts the result. This powerful feature allows you to explore dynamic scenarios, like changing formulas, etc., and then note how these dependencies impact the worksheet’s outcome.

## Importance of What-If Analysis in Decision-Making

Almost every data analyst or expert works with Excel sheets to make better, quicker, and more accurate data decisions. The What-If analysis in Excel is very efficient and widely used in several applications like scenario evaluation, risk assessment, cost-benefit analysis, forecasting, and more. Let’s read about some of them in detail.

### Scenario Evaluation

With the what-if analysis in Excel, analysts can input different values as “IFs” and see “What” happens in each scenario. This helps decision-makers to see the potential consequences of different scenarios.

### Risk Assessment

Using this analysis, decision-makers can also assess potential risks. For instance, technical people working in oil rigs or anywhere in the oil and gas industry continually use the What-If analysis in Excel to see how different volumes and capacities impact oil and gas production.

### Resource Allocation

What-if analysis in Excel can also assist in optimizing resource allocation by analyzing different scenarios. Using this analysis, decision-makers can identify resource requirements, evaluate cost implications, and anticipate investments based on their potential impact.

### Forecasting

Analysts and decision-makers can use the various tools available for what-if analysis in Excel to forecast stock prices, future sales, and any other relevant factors.

Excel offers numerous tools that you can use for data analysis and gain insights, summarize unorganized data, perform statistical analysis, and much more. In this section, let’s explore some of the most used ones.

• Pivot Tables: These tables help you summarize and analyze massive datasets by clubbing data based on some criteria. Pivot tables can generate stats and perform exploratory data analysis. Further, they can also be used to create custom reports using a follow-up feature called PivotCharts. Click on Insert > PivotChart > PivotChart and PivotTable to use this tool.
• Conditional Formatting: Formatting per your requirements can save you a lot of time analyzing data. It allows you to handle cells just as you want—you can delete them, hide them, or even highlight them based on the specified condition. It works best for finding outliers, duplicates, and specific patterns. Click on Conditional Formatting > then choose the columns/rows and work.
• XLOOKUP: This function combines HLOOKUP and VLOOKUP, extending the lookup feature vertically and horizontally. Once you have mentioned a range, using XLOOKUP, you can find or “look up” the values within the range. Use the following command =XLOOKUP(‘value’ , ‘range’).

## Different Types of What-If Analysis In Excel

There are three main kinds of what-if analysis in Excel: Scenario, Goal Seek, and Data Tables.

• Scenario What-If Analysis: As per Excel terminology, a ‘scenario’ is a set of values that a worksheet saves and can automatically substitute in the cells. You can tailor different groups in a worksheet and then switch to any scenario you created previously.
• Goal Seek What-If Analysis: Goal seek allows you to find the input value needed to achieve a specific target or goal for a calculated result. Using this type of what-if analysis in Excel, you can perform reverse computations and find the desired input level.
• Data Tables What-If Analysis: Data tables allow you to calculate multiple values based on different inputs. They help explore various scenarios and understand the impact of changing variables.

## Using What-If Analysis for Sensitivity Analysis

The What-If analysis in Excel is also widely used in sensitivity analysis. Here’s how it is done.

### Conducting Sensitivity Analysis in Excel

• Set up your Excel sheet and consider different scenarios before identifying the cells you wish to include.
• In the dialog box, enter the cells (rows and columns) you wish to analyze. Enter a range of values for the input variable(s).
• Click OK to generate a table corresponding to your inputs.
• Analyze the data table results to understand your model’s sensitivity.

### Assessing the Impact of Changing Variables

By observing the sensitivity table, you can see how changing values impact the calculated results. You can use this information to make more informed decisions, identify areas of risk or uncertainty, and prioritize your focus on the most influential variables.

## Scenario Analysis with Scenario Manager

This section discusses the scenario what-if analysis in Excel.

### Integrating Scenarios with Scenario Manager

Scenario Manager is a robust Excel tool widely used for what-if analysis. It allows you to create and handle several scenarios to see how changing variables impact your worksheet. Primarily, it compares multiple sets of input values to observe different outcomes.

• Set up your Excel sheet and consider different scenarios before identifying the cells you wish to include.
• Head to the “Data” tab and click on “What-If Analysis” > “Scenario Manager.”
• In the Add Scenario dialog box, provide a name for your scenario to identify it quickly.
• Select the desired cells and enter the new values.
• Do the same for multiple scenarios by “Adding.”
• Once you have created all the desired scenarios, you can easily switch between them.

### Analyzing Different Scenarios

Scenario Managers lets you generate a summary for each scenario. You can also generate a report or display the results visually in a separate worksheet.

## What-If Analysis with Data Tables

Data tables are one of the most systematic ways to calculate results based on different combinations of inputs. Here’s how it is done.

### Using Data Tables for What-If Analysis

• Set up your Excel sheet and consider different scenarios before identifying the cells you wish to include.
• Head to the “Data” tab and click on “What-If Analysis” > “Data Tables.”
• In the dialog box, enter the cells (rows and columns) you wish to analyze. Enter a range of values for the input variable(s).
• Click OK to generate a table corresponding to your inputs.
• Analyze the results.

### Creating and Interpreting Data Tables

Utilizing Data Tables for What-If Analysis allows you to quickly produce different results by changing the input values. This enables you to investigate many situations, evaluate your model, and base your judgments on the outcomes.

## What-If Analysis with Goal Seek

Let’s move on to see how Goal Seek works.

### Applying Goal Seek for What-If Analysis

With Excel’s proficient Goal Seek tool, you can perform What-If Analysis by determining the input value required to reach a particular target or goal for a calculated result.

### Setting Up Goal Seek Analysis

• Set up your Excel sheet and consider different scenarios before identifying the cells you wish to include.
• Head to the Data” tab and click on “What-If Analysis” > “Goal Seek.”
• In the Goal Seek dialog box that appears, you’ll see three input fields:
• “Set Cell”: output cell that contains the calculated result. Excel will adjust the value in this cell.
• “To value”: Enter the target value you want to achieve.
• “By changing cell”: Select the input cell you want to adjust to reach the target value.
• Once you’ve entered the inputs, click OK to command Excel to perform the goal seek calculation.
• You will get a display message whether a solution was found or not. If a solution is found, the adjusted value for the input cell will be displayed.

### Using Goal Seek to Find Desired Results

To determine how the modified input value affected the calculated result, analyze the results. You can also evaluate the viability of the Goal Seek solution.

In addition to the standard data analysis techniques, Excel also offers many advanced what-if analysis tools, like Solver, A/B Testing, Agent-based Modeling, and more. Here, we’re focusing on how Solver is used for complex what-if analyses.

### Using Solver For Complex Scenarios

Solver is a powerful Excel tool commonly used for mathematical modeling, optimization, and constraint analysis. Here’s how you can use Solver in Excel for complex scenarios.

• Enable the Solver add-in. Go to “File” > “Options” > “Add-ins.”
• Define the problem. Whether it is maximization, minimization, etc.
• Determine the constraints or limitations that need to be satisfied.
• Once you have defined the problem, go to the “Data” tab and click on the “Solver” button in the “Analysis” group.
• In the Solver Parameters dialog box, set the parameters. Specify the objective cell, decision variables, and cells having constraints.
• Click on “Solve” to start the process. Solver will try different combinations of values for the decision variables to find the optimal solution.

### Incorporating Multiple Variables in What-If Analysis

Performing a what-if analysis in Excel with more than two variables works best using Scenarios. A scenario can have as many as 32 different values, and you can always create multiple scenarios. Here, an IF condition can have other IF conditions contained in it. These can simultaneously test multiple scenarios and return different results for each.

Contrasting it with Data Tables and Goal Seek, the former works best with one or two variables, and the latter will only perform with one variable.

## Limitations and Best Practices of What-If Analysis

Despite how beneficial a what-if analysis is, a few drawbacks and limitations restrict them. For starters, if you frequently work with worksheets, you know that worksheets do fail—and they fail a lot of times. Besides, there are a few other limitations; read on.

### Understanding the Limitations of What-If Analysis

• Non-intuitive: Generally, a what-if statement or logic is based on intuitions. You rely on your intuition and see how things turn out. But with spreadsheets, this analysis is non-intuitive. Whether you use goal seek or scenarios, your analysis will only succeed if your data is precise and your problem is straightforward.
• Specifying Cell References is Cumbersome: To set up a what-if analysis for success, you’ll need to be very precise while specifying cells, constraints, control variables, and other parameters. A tiny bit here and there, and your analysis could be inaccurate.
• The Analysis is Only the First Step: Once you’re through with the analysis, you need to visualize the results. On the one hand, spreadsheets allow you to perform the what-if analysis quickly. However, on the other hand, it’ll ask you to generate not one but multiple visualizations to make some sense of the data.

### Following Best Practices for Accurate Analysis

Despite the abovementioned limitations, you can still use Excel’s what-if analysis. Follow the practices below to maintain data integrity, minimize errors, and improve the reliability of your results.

• Be precise when you define your objects. Understand the questions you are trying to answer or the problems you are trying to solve.
• Ensure you gather high-quality, reliable, and relevant data for your analysis.
• Document your analysis—the methodology, sources, benchmarks, and everything.
• Use appropriate tools and techniques. Familiarize yourself with the capabilities and limitations of the chosen tools, ensuring they align with your objectives.

## Conclusion

Wrapping up, you can see that what-if analysis in Excel is very efficient in empowering analysts and decision-makers to explore scenarios, assess risks, allocate resources, and do much more. By incorporating multiple variables and using advanced techniques such as Solver, data tables, and scenario analysis, Excel enables users to gain valuable insights. Once you define the problem, set up all the constraints, and specify the suitable cells, you’ll get the most out of your what-if analysis in a few seconds.

To learn more about the same, you can look at some tutorials and blogs on Analytics Vidhya. It is a leading platform for data science, machine learning, and artificial intelligence and can be a valuable resource in leveraging what-if analysis and enhancing analytical capabilities.

Q1. Why “IF” is used in Excel?

A. The IF function holds a particular purpose. It is used to make logical comparisons and see how the outcome differs from what you expect.

Q2. What is the difference between what-if analysis and sensitivity analysis?

A. What-if Analyses are called sensitivity analyses because they help you determine how sensitive the outcome is to changing variables.

Q3. What is the IFS formula in Excel?

A. The IFS function sees whether multiple IF conditions are met simultaneously, and a value corresponding to the initial TRUE condition is returned.

Q4. Give some examples of what-if analyses in real life.

A. What-if analysis in Excel can be used for cost-minimization, cost-effectiveness, cost-utility, risk assessment, etc.

### Related

data analysisData analysis toolsdata tablesExcelGoal Seekscenario managerSensitivity AnalysisWhat-if AnalysisWhat-If Analysis Techniques

## FAQs

### What is What-If Analysis in Excel? ›

By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the various results. For example, you can do What-If Analysis to build two budgets that each assumes a certain level of revenue.

How do you evaluate answers in Excel? ›

Description
1. Select the cell that you want to evaluate. ...
2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
3. Click Evaluate to examine the value of the underlined reference. ...
4. Continue until each part of the formula has been evaluated.
5. To see the evaluation again, click Restart.

What is a real world example in which you would use what if analysis? ›

An example of what-if analysis would be to ask: what would happen to my revenue if I charged more for each loaf of bread? In the simple case, where the volume of bread sold doesn't depend on the price of the bread, the analysis is very easy. An X% rise in the price per loaf will lead to an X% increase in sales.

Why is my Excel function giving the wrong answer? ›

Select the problematic cell then ensure that you don't see “Circular References” at the bottom-left corner of the page. You can also select “Formulas” then check the “Error Checking” opttion to check for Circular References in your sheet. You can learn more about Circular References in Excel on Microsoft's website.

How do I analyze a lot of data in Excel? ›

Try it!
1. Select a cell in a data range.
2. Select Home > Analyze Data. The Analyze Data pane will appear and show different visual and analysis types, such as: Rank. Trend. Outlier. Majority.
3. Choose an option and select Insert PivotChart.

How do I calculate text responses in Excel? ›

If you want to learn how to count text in Excel, you need to use function COUNTIF with the criteria defined using wildcard *, with the formula: =COUNTIF(range;"*") . Range is defined cell range where you want to count the text in Excel and wildcard * is criteria for all text occurrences in the defined range.

How do you evaluate Excel skills in an interview? ›

Here are some sample questions to consider asking in a technical interview:
1. How do you use a pivot table? ...
2. What's an Excel macro? ...
3. How do you lock a worksheet to prevent others from editing it?
4. What are some limitations of VLOOKUP?
5. What is the difference between a formula and a function in Excel?
Aug 14, 2022

What are the three types of what if analysis? ›

Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables.

What is the primary reason for performing a What If analysis? ›

What-if analysis is used to explore and compare various plan and schedule alternatives based on changing conditions. It can be applied in the primary project phases to try out scenarios and optimize your plan. During execution, it is an important tool used to predict the consequence of any event (late delivery etc.)

What type of situation would call for you to use a What If analysis? ›

A business can use what if scenario analysis to see how a given outcome, such as project costs, might be affected by changes in particular variables, such as the late delivery of supplies or the unavailability of key personnel.

### What to do if Excel is not calculating correctly? ›

Re: Formulas Not Calculating in Workbook

Check Calculation Settings: Click on the "Formulas" tab in the Excel ribbon. In the "Calculation" group, ensure that the calculation mode is set to "Automatic." If it's set to "Manual," formulas won't recalculate automatically.

How do you tell Excel to do nothing if value is false? ›

Leaving a Cell Value Unchanged If a Condition Is False in Excel
1. Step 1: Select the Cell.
2. Step 2: Open the Conditional Formatting Dialog Box.
3. Step 3: Add a New Rule.
4. Step 4: Select the Rule Type.
5. Step 5: Set the Condition.
6. Step 6: Set the Formatting.
7. Step 7: Click "OK"

Why is my Excel acting weird? ›

Re: Excel - is acting wierd

The causes of this issue are when your Excel worksheet is in the page layout view, when Windows protection is turned on and when the Excel sheet is protected by the earlier versions of Excel. Because of all these issues, Excel Freeze Panes does Not Work properly.

How do you analyze data effectively? ›

How to analyze data
1. Establish a goal. First, determine the purpose and key objectives of your data analysis. ...
2. Determine the type of data analytics to use. Identify the type of data that can answer your questions. ...
3. Determine a plan to produce the data. ...
4. Collect the data. ...
5. Clean the data. ...
6. Evaluate the data. ...
7. Visualize the data.
Feb 3, 2023

What is an example of data analysis? ›

A simple example of data analysis can be seen whenever we make a decision in our daily lives by evaluating what has happened in the past or what will happen if we make that decision. Basically, this is the process of analyzing the past or future and making a decision based on that analysis.

How do you analyze a large amount of data? ›

For large datasets, analyze continuous variables (such as age) by determining the mean, median, standard deviation and interquartile range (IQR). Analyze nominal variables (such as gender) by using percentages. Activity #2: Discuss with a colleague the conclusions you would make based on Table 2.

Is there an evaluate function in Excel? ›

The Evaluate Formula feature walks you through each argument in a formula to help identify and fix any mistakes. You can also use it to understand complex formulas, seeing how each part of a nested function is calculated to reach the final result.

What is Excel evaluation? ›

A Microsoft Excel assessment test is a tool that employers use to determine whether a candidate is suitable for a position based on their proficiency with the software. These tests are becoming more common, as many companies use the software to make graphs and analyse data.

How do you evaluate survey results in Excel? ›

How to analyze survey data in Excel
1. Tip #1: Create blank data rows with a filter.
2. Tip #2: Count the number of blanks in a range with COUNTBLANK.
3. Tip #3: Convert numbers to ranges with VLOOKUP.
4. Tip #4: Convert binary answers to binary numbers.
5. Tip #5: Calculate the correlation between survey results with CORREL.

Which rule does Excel follow to evaluate? ›

Excel follows general mathematical rules for calculations, which is Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction, or the acronym PEMDAS (Please Excuse My Dear Aunt Sally). Using parentheses allows you to change that calculation order.

Top Articles
Latest Posts
Article information

Author: Kimberely Baumbach CPA

Last Updated: 19/09/2023

Views: 5627

Rating: 4 / 5 (41 voted)

Author information

Name: Kimberely Baumbach CPA

Birthday: 1996-01-14

Address: 8381 Boyce Course, Imeldachester, ND 74681

Phone: +3571286597580

Job: Product Banking Analyst

Hobby: Cosplaying, Inline skating, Amateur radio, Baton twirling, Mountaineering, Flying, Archery

Introduction: My name is Kimberely Baumbach CPA, I am a gorgeous, bright, charming, encouraging, zealous, lively, good person who loves writing and wants to share my knowledge and understanding with you.