Custom sorting rules in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a custom sort in Excel?

A custom sort lets you arrange data in an order you choose, not just alphabetical or numerical. For example, sorting days of the week in their natural order instead of A-Z.

intermediate

How do you create a custom list for sorting in Excel?

Go to File > Options > Advanced > Edit Custom Lists. Then type your list items in order, separated by commas or one per line, and add it. This list can be used to sort data in that specific order.

beginner

Why use custom sorting instead of normal sorting?

Normal sorting arranges data alphabetically or numerically. Custom sorting lets you sort by your own rules, like sorting priority levels (High, Medium, Low) or months in calendar order.

beginner

What steps do you follow to apply a custom sort to a column in Excel?

1. Select your data. 2. Go to Data tab > Sort. 3. Choose the column to sort. 4. Under Order, select Custom List. 5. Pick your custom list and click OK.

intermediate

Can you use custom sorting with multiple columns in Excel?

Yes! You can add levels in the Sort dialog to sort by one column first, then by another. Each column can use its own custom list if needed.

What does a custom sort in Excel allow you to do?

A Sort data in any order you define

B Only sort data alphabetically

C Sort data by color only

D Sort data randomly

Custom sort lets you define your own order, like sorting days or priority levels.

Where do you add a custom list for sorting in Excel?

A Home tab > Format

B File > Options > Advanced > Edit Custom Lists

C Data tab > Sort

D Insert tab > Table

Custom lists are created in Excel Options under Advanced settings.

Which of these is an example of a good use for custom sorting?

A Sorting months in calendar order

B Sorting numbers from smallest to largest

C Sorting text alphabetically

D Sorting by cell color

Months don’t sort correctly alphabetically, so custom sorting fixes that.

How do you apply a custom sort to a column?

A Insert a new column and sort manually

B Right-click and choose Sort > Custom

C Use Home > Sort & Filter > Custom

D Use Data > Sort, select column, then choose Custom List

The Sort dialog under Data tab lets you pick custom lists for sorting.

Can you sort by multiple columns using custom lists?

A Only if columns have numbers

B No, only one column at a time

C Yes, by adding levels in the Sort dialog

D Only if columns have dates

Excel allows multiple levels of sorting, each with its own custom list if needed.

Explain how to create and use a custom sort list in Excel.

Think about where Excel stores your custom order and how you apply it.

Describe a real-life example where custom sorting is helpful.

Consider lists that don’t follow alphabetical or numeric order.

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