SWITCH function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick way to categorize sales regions into broader zones for a summary report.

📊 Data: You have a list of sales transactions with columns for Region and Sales Amount.

🎯 Deliverable: Create a new column that uses the SWITCH function to assign each Region to a Zone: 'North' and 'East' regions belong to 'Zone 1', 'South' and 'West' belong to 'Zone 2', and any other region should be labeled 'Other'.

Sample Data

Transaction ID	Region	Sales Amount
1001	North	500
1002	East	300
1003	South	450
1004	West	600
1005	Central	200
1006	North	700
1007	East	400
1008	South	350

1

Step 1: Insert a new column next to the Region column and name it 'Zone'.

Expected Result

A new empty column labeled 'Zone' next to the Region column.

2

Step 2: In the first cell of the 'Zone' column (assume D2), enter the SWITCH formula to categorize the Region.

=SWITCH(B2, "North", "Zone 1", "East", "Zone 1", "South", "Zone 2", "West", "Zone 2", "Other")

Expected Result

For Region 'North' in B2, the formula returns 'Zone 1'.

3

Step 3: Copy the formula down the entire 'Zone' column for all rows of data.

Drag the fill handle from D2 down to D9.

Expected Result

Each row shows the correct Zone based on the Region: 'North' and 'East' as 'Zone 1', 'South' and 'West' as 'Zone 2', and 'Central' as 'Other'.

4

Step 4: Verify the results for each row to ensure correct categorization.

Expected Result

Row with Region 'Central' shows 'Other'; rows with 'North' or 'East' show 'Zone 1'; rows with 'South' or 'West' show 'Zone 2'.

Final Result

Transaction ID | Region  | Sales Amount | Zone  
--------------|---------|--------------|-------
1001          | North   | 500          | Zone 1
1002          | East    | 300          | Zone 1
1003          | South   | 450          | Zone 2
1004          | West    | 600          | Zone 2
1005          | Central | 200          | Other 
1006          | North   | 700          | Zone 1
1007          | East    | 400          | Zone 1
1008          | South   | 350          | Zone 2

✓The SWITCH function efficiently groups regions into zones without nested IFs.

✓Regions 'North' and 'East' are categorized as 'Zone 1'.

✓Regions 'South' and 'West' are categorized as 'Zone 2'.

✓Any region not listed is labeled as 'Other', such as 'Central'.

Bonus Challenge

Create a summary table that shows total Sales Amount for each Zone using the categorized data.

Show Hint

Use the SUMIF function with the Zone column as the criteria range and Sales Amount as the sum range.

Practice

1. What does the SWITCH function do in Excel?

easy

A. It sorts data alphabetically.

B. It adds numbers in a range.

C. It selects a value from multiple options based on a single expression.

D. It counts the number of cells with text.

Solution

1. Step 1: Understand the purpose of SWITCH

   The SWITCH function evaluates one expression and compares it to a list of values.

2. Step 2: Identify what SWITCH returns

   It returns the result corresponding to the first matching value or a default if no match is found.

3. Final Answer:

   It selects a value from multiple options based on a single expression. -> Option C

4. Quick Check:

   SWITCH chooses from options based on one value [OK]

Hint: SWITCH picks one result from many choices based on one value [OK]

Common Mistakes:

• Confusing SWITCH with SUM or COUNT functions
• Thinking SWITCH sorts data
• Believing SWITCH counts text cells

2. Which of these is the correct syntax for the SWITCH function in Excel?

easy

A. SWITCH(expression, value1, result1, [value2, result2], ..., [default])

B. SWITCH(expression; value1; result1; [value2; result2]; ...; [default])

C. SWITCH(expression, [value1, result1], [value2, result2], ..., default)

D. SWITCH(expression: value1: result1: value2: result2: default)

Solution

1. Step 1: Recall Excel function syntax

   Excel functions use commas to separate arguments, not semicolons or colons.

2. Step 2: Match the correct SWITCH syntax

   The correct syntax is SWITCH(expression, value1, result1, [value2, result2], ..., [default]).

3. Final Answer:

   SWITCH(expression, value1, result1, [value2, result2], ..., [default]) -> Option A

4. Quick Check:

   Commas separate SWITCH arguments in Excel [OK]

Hint: Remember: Excel uses commas, not semicolons or colons [OK]

Common Mistakes:

• Using semicolons or colons instead of commas
• Placing default value without commas
• Using brackets incorrectly around arguments

3. What is the result of this formula?
=SWITCH(3, 1, "One", 2, "Two", 3, "Three", "Other")

medium

A. "One"

B. "Two"

C. "Other"

D. "Three"

Solution

1. Step 1: Identify the expression value

   The expression is 3, so SWITCH looks for value 3 in the list.

2. Step 2: Find matching value and return result

   Value 3 matches the third pair, so it returns "Three".

3. Final Answer:

   "Three" -> Option D

4. Quick Check:

   Expression 3 matches value 3, returns "Three" [OK]

Hint: Match expression to value pairs in order to find result [OK]

Common Mistakes:

• Choosing default result instead of matching value
• Confusing value and result pairs order
• Ignoring the expression value

4. Identify the error in this formula:
=SWITCH(A1, 1, "One", 2, "Two", 3, "Three")
Assume A1 contains 4.

medium

A. Missing default value causes #N/A error when no match found.

B. Syntax error due to missing commas.

C. Incorrect use of quotes around numbers.

D. SWITCH cannot handle numeric values.

Solution

1. Step 1: Check for default value in SWITCH

   The formula lacks a default result for unmatched values.

2. Step 2: Understand behavior when no match found

   Since A1 is 4 and no match exists, SWITCH returns #N/A error without default.

3. Final Answer:

   Missing default value causes #N/A error when no match found. -> Option A

4. Quick Check:

   Always add default to avoid errors [OK]

Hint: Always add a default result to SWITCH to prevent errors [OK]

Common Mistakes:

• Forgetting default value causes #N/A error
• Thinking SWITCH needs quotes around numbers
• Assuming SWITCH can't handle numbers

5. You want to assign letter grades based on scores in cell B2: 90+ = "A", 80-89 = "B", 70-79 = "C", 60-69 = "D", below 60 = "F".
Which SWITCH formula correctly assigns these grades?

hard

A. =SWITCH(B2, 90, "A", 80, "B", 70, "C", 60, "D", "F")

B. =SWITCH(TRUE, B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", "F")

C. =SWITCH(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", "F")

D. =SWITCH(B2, TRUE, "A", FALSE, "F")

Solution

1. Step 1: Use SWITCH with logical TRUE to evaluate conditions

   Using SWITCH(TRUE, ...) lets us test multiple conditions in order.

2. Step 2: Check conditions from highest to lowest

   Conditions check if B2 is >=90, >=80, etc., returning correct grade or default "F".

3. Final Answer:

   =SWITCH(TRUE, B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", "F") -> Option B

4. Quick Check:

   SWITCH(TRUE, conditions...) assigns grades by ranges [OK]

Hint: Use SWITCH(TRUE, condition1, result1, ...) for range checks [OK]

Common Mistakes:

• Using SWITCH with exact values instead of conditions
• Not ordering conditions from highest to lowest
• Missing default grade for scores below 60