Custom sorting rules in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sort the range A1:A10 in ascending order.

Excel

=SORT(A1:A10, [1], TRUE)

Drag options to blanks, or click blank then click option'

A2

B1

C0

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or 2 as the sort index causes errors or unexpected results.

Leaving the sort index blank.

The second argument in SORT specifies the column to sort by. Since we have a single column range, use 1.

2fill in blank

medium

Complete the formula to sort the range B2:C10 by the second column in descending order.

Excel

=SORT(B2:C10, [1], FALSE)

Drag options to blanks, or click blank then click option'

A0

B3

C2

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 2 sorts by the first column.

Using 0 or 3 causes errors.

The second argument is the column index to sort by within the range. Here, column 2 means the second column of B2:C10.

3fill in blank

hard

Fix the error in the formula to sort the range A1:A5 by a custom list order: "High", "Medium", "Low".

Excel

=SORTBY(A1:A5, MATCH(A1:A5, [1], 0))

Drag options to blanks, or click blank then click option'

A{"High", "Medium", "Low"}

B("High", "Medium", "Low")

C["High", "Medium", "Low"]

DHigh, Medium, Low

Attempts:

3 left

💡 Hint

Common Mistakes

Using square brackets or parentheses instead of curly braces.

Not quoting text values inside the array.

Custom lists in Excel formulas use array constants with curly braces and double quotes for text.

4fill in blank

hard

Fill both blanks to sort range B1:B6 by a custom list "Red", "Green", "Blue" and then by values ascending.

Excel

=SORTBY(B1:B6, MATCH(B1:B6, [1], 0), [2])

Drag options to blanks, or click blank then click option'

A{"Red", "Green", "Blue"}

BTRUE

C1

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE/FALSE instead of 1/0 for sort order in SORTBY.

Using square brackets for the custom list.

The MATCH function uses a custom list array in curly braces. The third argument of SORTBY is the sort order: 1 for ascending.

5fill in blank

hard

Fill all three blanks to create a formula that sorts range A1:A8 by a custom list "Small", "Medium", "Large", then by values descending, and finally by the original order ascending.

Excel

=SORTBY(A1:A8, MATCH(A1:A8, [1], 0), [2], SEQUENCE(ROWS(A1:A8)), [3])

Drag options to blanks, or click blank then click option'

A{"Small", "Medium", "Large"}

B0

C1

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 instead of -1 for descending order.

Mixing up the order of sort orders.

The custom list is an array constant in curly braces. The second sort order is -1 for descending. The third sort order is 1 for ascending.

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