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.

Practice

1. What is the main purpose of using Custom Sorting in Excel?

easy

A. To create charts from data

B. To automatically remove duplicates from data

C. To arrange data in a specific order you define

D. To filter data based on conditions

Solution

1. Step 1: Understand what sorting does

   Sorting arranges data in order, like alphabetically or numerically.

2. Step 2: Identify what custom sorting adds

   Custom sorting lets you choose your own order, not just A-Z or smallest to largest.

3. Final Answer:

   To arrange data in a specific order you define -> Option C

4. Quick Check:

   Custom sorting = user-defined order [OK]

Hint: Custom sorting means sorting by your own list order [OK]

Common Mistakes:

• Confusing sorting with filtering
• Thinking custom sorting removes duplicates
• Assuming custom sorting creates charts

2. Which of these is the correct way to start creating a custom sort order in Excel?

easy

A. Go to Review tab > Protect Sheet

B. Go to Home tab > Conditional Formatting > New Rule

C. Right-click cell > Insert Comment

D. Go to Data tab > Sort > Order dropdown > Custom List...

Solution

1. Step 1: Locate sorting options

   Sorting options are under the Data tab in Excel.

2. Step 2: Find how to access custom lists

   In the Sort dialog, the Order dropdown has a Custom List option to define your order.

3. Final Answer:

   Go to Data tab > Sort > Order dropdown > Custom List... -> Option D

4. Quick Check:

   Custom sort setup = Data tab > Sort > Custom List [OK]

Hint: Custom sort is under Data tab, not Home or Review [OK]

Common Mistakes:

• Looking in Conditional Formatting for sorting
• Trying to add custom sort via comments
• Confusing sheet protection with sorting

3. You have a list of fruits in column A: Apple, Banana, Cherry, Date. You want to sort them in this order: Date, Apple, Banana, Cherry. Which custom list should you create to sort correctly?

medium

A. Apple, Banana, Cherry, Date

B. Date, Apple, Banana, Cherry

C. Banana, Cherry, Date, Apple

D. Cherry, Date, Apple, Banana

Solution

1. Step 1: Understand custom list order

   The custom list defines the exact order Excel uses to sort.

2. Step 2: Match desired order with custom list

   You want Date first, then Apple, Banana, Cherry, so the list must be exactly that.

3. Final Answer:

   Date, Apple, Banana, Cherry -> Option B

4. Quick Check:

   Custom list = desired sort order [OK]

Hint: Custom list order = your desired sort order [OK]

Common Mistakes:

• Using alphabetical order instead of desired order
• Mixing up the sequence of items
• Not creating a custom list at all

4. You tried to sort a list using a custom list but the order did not change. What is the most likely reason?

medium

A. You created the custom list but did not choose it in the Sort dialog

B. You used the Filter feature instead of Sort

C. Your data contains blank cells

D. You forgot to select the correct column before sorting

Solution

1. Step 1: Check if custom list was applied

   Creating a custom list alone does not sort data; you must select it in the Sort dialog.

2. Step 2: Confirm sorting steps

   If you don't pick the custom list in the Order dropdown, Excel uses default sorting.

3. Final Answer:

   You created the custom list but did not choose it in the Sort dialog -> Option A

4. Quick Check:

   Custom list must be selected to apply sorting [OK]

Hint: Always select your custom list in Sort order dropdown [OK]

Common Mistakes:

• Assuming creating list auto-sorts data
• Not selecting the right column before sorting
• Confusing Filter with Sort

5. You have a list of project statuses in column A: "In Progress", "Completed", "Not Started", "On Hold". You want to sort them so the order is: "Not Started", "In Progress", "On Hold", "Completed". How do you apply this custom sort correctly?

hard

A. Create a custom list with the order and then sort column A using that custom list

B. Sort column A alphabetically and then manually rearrange rows

C. Use Conditional Formatting to color code statuses

D. Filter column A to show only "Not Started" first

Solution

1. Step 1: Define the custom order

   Create a custom list with statuses in the desired order: Not Started, In Progress, On Hold, Completed.

2. Step 2: Apply the custom sort

   Use the Sort dialog on column A and select the custom list in the Order dropdown to sort accordingly.

3. Final Answer:

   Create a custom list with the order and then sort column A using that custom list -> Option A

4. Quick Check:

   Custom list + Sort = desired order [OK]

Hint: Define custom list then sort column using it [OK]

Common Mistakes:

• Sorting alphabetically instead of custom order
• Trying to manually move rows after sorting
• Using filtering instead of sorting