Quick answer
The SWITCH formula compares one expression against several possible values and returns the matching result.
=SWITCH(B2,"A","Excellent","B","Good","C","Average","Review")Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=SWITCH(expression, value1, result1, [default_or_value2], [result2], ...)When to use it
Logical formulas help a spreadsheet make decisions. They are useful when a report needs to say whether a target was met, whether a row needs attention, or which label should be shown based on one or more conditions.
- Mark invoices as paid or overdue
- Create pass/fail labels for scores
- Flag rows that need review
- Build status messages for dashboards
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Item, person, or transaction |
| B | Score / status / amount |
| C | Formula result or decision label |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic SWITCH example
=SWITCH(B2,"A","Excellent","B","Good","C","Average","Review")Start with one row so you can confirm the logic before copying down.
Return a blank until the row is ready
=IF(A2="","",SWITCH(B2,"A","Excellent","B","Good","C","Average","Review"))Keep unfinished rows from showing confusing results.
Use the formula with a fixed target
=SWITCH(B2,"A","Excellent","B","Good","C","Average","Review")Lock the target cell when every row compares to the same input.
Use with a table column
=SWITCH([@Value],"A","Excellent","B","Good","C","Average","Review")Structured references are easier to read in Excel Tables.
Make the output report-friendly
=IFERROR(SWITCH(B2,"A","Excellent","B","Good","C","Average","Review"),"Check row")Show a clear message instead of letting the dashboard display an error.
Combine with LET for readability
=LET(result,SWITCH(B2,"A","Excellent","B","Good","C","Average","Review"),result)Use LET when the final formula will become long.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Text outputs are missing quotation marks.
- Rules overlap, so a later condition never has a chance to apply.
- Fixed thresholds are not locked with dollar signs before copying down.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the SWITCH formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.