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.