How to Do a What If Formula in Excel
Master what-if formulas in Excel with IF, IFS, and SWITCH. This practical guide covers real-world examples, data tables, and best practices for reliable scenario analysis.
With Excel's built-in logical functions, you can perform what-if scenarios by creating formulas that test conditions and return results based on inputs. Start with IF, expand with IFS or SWITCH for multiple outcomes, and pair with AND/OR to handle complex rules. This approach lets you model different inputs directly in your worksheets, without external tools.
What Qualifies as a What If Formula in Excel
What we call a what-if formula in Excel is any expression that returns different results based on changing inputs or assumptions. It often combines logical tests (TRUE/FALSE) with arithmetic operations and references to cells containing inputs. The core idea is to let the worksheet itself explore scenarios without manual rewriting each time. Excel users frequently treat IF as the starting point, then layer more logic with sibling functions to handle multiple outcomes. In practice, a what-if formula might determine eligibility for a loan, a grade from a score, or a forecast category from a numeric projection. The key is to clearly separate inputs (assumptions) from outputs (results) so the model remains transparent and auditable. For consistent results, keep inputs in named cells and document what each test represents.
What Qualifies as a What If Formula in Excel
What we call a what-if formula in Excel is any expression that returns different results based on changing inputs or assumptions. It often combines logical tests (TRUE/FALSE) with arithmetic operations and references to cells containing inputs. The core idea is to let the worksheet itself explore scenarios without manual rewriting each time. Excel users frequently treat IF as the starting point, then layer more logic with sibling functions to handle multiple outcomes. In practice, a what-if formula might determine eligibility for a loan, a grade from a score, or a forecast category from a numeric projection. The key is to clearly separate inputs (assumptions) from outputs (results) so the model remains transparent and auditable. For consistent results, keep inputs in named cells and document what each test represents.
Tools & Materials
- Microsoft Excel (any modern version)(Prefer 2016+ for IFS/SWITCH support)
- Workbook with sample data(Create a dedicated sheet for inputs and calculations)
- Practice dataset(Optional, to test multiple scenarios)
Steps
Estimated time: 60-90 minutes
- 1
Define inputs and outputs
Identify the cells that will act as scenarios (inputs) and the result you want to compute (output). Place inputs in a clearly labeled area and use named ranges to simplify formulas.
Tip: Using named ranges makes formulas easier to read and maintain. - 2
Start with a simple IF
Create a basic IF test that returns two outcomes. Example: =IF(B2>=60, 'Pass', 'Fail'). This establishes the domino effect for more complex tests.
Tip: Keep the condition and results readable; avoid over-nesting in early steps. - 3
Add nested IFs for multiple outcomes
Extend the IF logic to handle several ranges. Example: =IF(B2>=90, 'A', IF(B2>=80, 'B', 'C')). Preview results on a separate column.
Tip: Label each branch to prevent ambiguity; consider readability aids like commented cells. - 4
Incorporate AND/OR for complex rules
Combine conditions to cover multiple criteria. Example: =IF(AND(B2>=60, C2=
Tip: Be mindful of operator precedence; use parentheses to enforce order. - 5
Move to IFS for many conditions
Use IFS to replace a long chain of nested IFs. Example: =IFS(B2>=90, 'A', B2>=80, 'B', B2>=70, 'C', TRUE, 'D').
Tip: I need TRUE at the end to catch any unmatched case; it acts like a default. - 6
Experiment with SWITCH for distinct outcomes
Switch is handy when you test one value against many possibilities. Example: =SWITCH(D2, 1,'Low', 2,'Medium', 3,'High', 'Unknown').
Tip: SWITCH is often cleaner than nested IFs for discrete categories. - 7
Test and validate inputs
Create a small test table with edge cases to verify outputs. Use conditional formatting to highlight unexpected results.
Tip: Include a separate row for invalid inputs and document expected data types. - 8
Introduce data validation to inputs
Use Data Validation to restrict inputs to valid ranges or categories, preventing logical errors.
Tip: Lock critical cells to avoid accidental edits in shared workbooks. - 9
Leverage a simple what-if data table
Build a one-variable data table to see how changing one input affects the output. Go to Data > What-If Analysis > Data Table.
Tip: Set the Row Input cell to the input you want to vary; the Column Input can remain blank for one-variable analysis. - 10
Document and share your model
Add a short description, assumptions, and a legend for each test. A self-contained sheet improves collaboration.
Tip: Include a readme cell with a brief explanation of the logic used.
People Also Ask
What counts as a what-if formula in Excel?
A what-if formula in Excel tests different inputs and returns outcomes based on logical tests. It typically uses IF, IFS, or SWITCH to decide which result to show when inputs change. These formulas enable scenario analysis directly in worksheets.
A what-if formula tests different inputs and shows outcomes based on logical tests, using IF, IFS, or SWITCH.
When should I use IFS vs SWITCH?
Use IFS when you need to evaluate multiple conditions in order and return a corresponding result. Switch is ideal when you test a single value against several predictable outcomes. Both simplify long nested IFs and improve readability.
Use IFS for multiple ordered conditions and SWITCH for a single value with many outcomes.
Can I combine what-if formulas with data tables?
Yes. Data Tables let you vary inputs across rows or columns to see how outputs change. They work well with simple IF/IFS/SWITCH outcomes and are a staple in quick scenario analysis.
Data tables let you vary inputs to observe outputs, complementing IF/IFS/SWITCH.
Are nested IFs still a good idea?
Nested IFs can work for simple cases, but they become hard to read as complexity grows. When you exceed three or four conditions, switch to IFS or SWITCH and keep the model organized.
Nested IFs work for small cases, but for many conditions use IFS or SWITCH for clarity.
How do I protect my what-if model from changes?
Use sheet protection, lock cells that contain formulas, and maintain a separate documentation sheet. These practices help prevent accidental edits while preserving the integrity of the scenario analysis.
Protect the sheet and lock formula cells to avoid accidental edits.
Watch Video
The Essentials
- Plan inputs and outputs before writing logic
- Choose the right function: IF for simple tests, IFS or SWITCH for many conditions
- Keep formulas readable with named ranges and comments
- Test with data tables to model multiple inputs efficiently
