Custom sorting rules in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a sales report sorted by product priority instead of alphabetical order. The priority order is: High, Medium, Low.

📊 Data: You have a table with columns: Product Name, Category, Sales Amount, and Priority (High, Medium, Low).

🎯 Deliverable: Create a sorted sales report where rows are ordered by Priority in the custom order High, Medium, Low, then by Sales Amount descending.

Sample Data

Product Name	Category	Sales Amount	Priority
Alpha	Electronics	1200	Medium
Bravo	Home	800	High
Charlie	Electronics	950	Low
Delta	Garden	1100	High
Echo	Home	700	Medium
Foxtrot	Garden	600	Low
Golf	Electronics	1300	High
Hotel	Home	900	Medium

1

Step 1: Add a helper column named 'Priority Rank' next to the Priority column to assign numeric values for sorting.

In cell E2, enter: =IF(D2="High",1,IF(D2="Medium",2,3)) and copy down for all rows.

Expected Result

Priority Rank column shows 1 for High, 2 for Medium, 3 for Low.

2

Step 2: Select the entire data range including the helper column.

Select A1:E9 (including headers and all rows).

Expected Result

All data including Priority Rank is selected.

3

Step 3: Open the Sort dialog and set up a custom sort.

Sort by 'Priority Rank' ascending, then by 'Sales Amount' descending.

Expected Result

Data is sorted first by Priority (High first), then by Sales Amount from largest to smallest within each priority.

4

Step 4: Optionally, hide the helper column 'Priority Rank' to keep the report clean.

Right-click column E and choose 'Hide'.

Expected Result

Priority Rank column is hidden but sorting remains.

Final Result

Product Name | Category    | Sales Amount | Priority
---------------------------------------------------
Golf         | Electronics | 1300         | High
Delta        | Garden      | 1100         | High
Bravo        | Home        | 800          | High
Alpha        | Electronics | 1200         | Medium
Hotel        | Home        | 900          | Medium
Echo         | Home        | 700          | Medium
Charlie      | Electronics | 950          | Low
Foxtrot      | Garden      | 600          | Low

✓High priority products lead in sales with Golf having the highest sales amount.

✓Medium priority products have moderate sales, with Alpha leading this group.

✓Low priority products have the lowest sales amounts.

Bonus Challenge

Create a dynamic dropdown to select the priority order and have the report sort automatically based on the selected order.

Show Hint

Use a named range for the dropdown and the SORTBY function with MATCH to assign ranks dynamically.