Custom sorting rules in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Sorting with a Custom List in Excel

You have a list of fruits in column A: Apple, Banana, Cherry, Date, and Elderberry.

You want to sort them in this custom order: Cherry, Banana, Apple, Date, Elderberry.

Which formula in column B will assign numbers to each fruit to help sort them in this custom order?

A =IF(A1="Cherry",1, IF(A1="Banana",2, IF(A1="Apple",3, IF(A1="Date",4, 5))))

B =VLOOKUP(A1, {"Cherry",1;"Banana",2;"Apple",3;"Date",4;"Elderberry",5}, 2, FALSE)

C =MATCH(A1, {"Cherry","Banana","Apple","Date","Elderberry"}, 0)

D =RANK(A1, {"Cherry","Banana","Apple","Date","Elderberry"})

💡 Hint

Use a function that finds the position of a value in a list.

The MATCH function returns the position of the fruit in the custom list, which can be used to sort the fruits in the desired order.

❓ Function Choice

intermediate

Choosing the Right Function for Custom Sort Order

You want to sort a list of priority levels: High, Medium, Low.

Which Excel function is best to assign numeric values to these text priorities for sorting?

A MATCH(priority, {"High","Medium","Low"}, 0)

B SUM(priority, {"High",1;"Medium",2;"Low",3})

C VLOOKUP(priority, {"High",1;"Medium",2;"Low",3}, 2, TRUE)

D RANK(priority, {"High","Medium","Low"})

💡 Hint

Look for a function that finds the exact position of a value in a list.

MATCH with exact match (0) returns the position of the priority in the list, perfect for custom sorting.

🎯 Scenario

advanced

Sorting Dates with Custom Weekday Order

You have dates in column A and want to sort them by weekday in this order: Friday, Monday, Wednesday, Sunday, Tuesday, Thursday, Saturday.

Which formula in column B correctly assigns numbers to weekdays for this custom sort?

A =MATCH(TEXT(A1, "dddd"), {"Friday","Monday","Wednesday","Sunday","Tuesday","Thursday","Saturday"}, 0)

B =WEEKDAY(A1, 2)

C =RANK(WEEKDAY(A1), {1,2,3,4,5,6,7})

D =CHOOSE(WEEKDAY(A1), 5, 2, 4, 6, 1, 7, 3)

💡 Hint

Convert the date to weekday name and find its position in the custom list.

TEXT with "dddd" gets the weekday name. MATCH finds its position in the custom order list, which can be used to sort.

❓ data_analysis

advanced

Analyzing Custom Sort Impact on Data

You have a list of tasks with statuses: Done, In Progress, Not Started.

You assign numbers using this formula: =MATCH(B2, {"Not Started","In Progress","Done"}, 0).

What will be the numeric value assigned to "Done"?

A 0

B 3

C 2

D 1

💡 Hint

Check the position of "Done" in the list inside MATCH.

"Done" is the third item in the list, so MATCH returns 3.

📊 Formula Result

expert

Complex Custom Sorting with Multiple Criteria

You have a table with columns: Priority (High, Medium, Low) and Due Date.

You want to sort first by Priority in order High > Medium > Low, then by Due Date ascending.

Which formula in column C correctly assigns a sorting key combining both criteria?

A =RANK(A2, {"High","Medium","Low"})*B2

B =MATCH(A2, {"High","Medium","Low"}, 0) + B2/100000

C =B2*100000 + MATCH(A2, {"High","Medium","Low"}, 0)

D =MATCH(A2, {"High","Medium","Low"}, 0)*100000 + B2

💡 Hint

Multiply priority rank by a large number to keep it dominant, then add due date.

Multiplying the priority rank by 100000 ensures priority sorting dominates. Adding the due date sorts by date within each priority.

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