Custom sorting rules in Excel - Step-by-Step Guide

Introduction

Custom sorting lets you arrange your data in an order you choose, not just alphabetical or numerical. This helps when you want to sort by special lists like days of the week or priority levels.

When you want to sort a list of tasks by priority: High, Medium, Low instead of alphabetically.

When you have dates as text like Monday, Tuesday, Wednesday and want them in the correct weekday order.

When sorting product categories that don’t follow alphabetical order, like Small, Medium, Large.

When you want to sort survey answers by custom labels like Agree, Neutral, Disagree.

When you want to sort a list of employees by department codes that have a specific order.

Steps

Step 1: Select the cells or column you want to sort

- Excel worksheet

The data range is highlighted for sorting

Step 2: Click the Data tab

- Ribbon at the top of Excel

Data tools and sorting options appear

Step 3: Click Sort

- Sort & Filter group on the Data tab

Sort dialog box opens

Step 4: In the Sort dialog, click Options

- Sort dialog box

Sort Options dialog appears

Step 5: Select 'Sort left to right' if sorting rows or leave default for columns, then click OK

- Sort Options dialog

Returns to Sort dialog with options updated

Step 6: Under 'Column', choose the column to sort by

- Sort dialog box

Selected column is set for sorting

Step 7: Under 'Order', click the dropdown and select 'Custom List...'

- Sort dialog box

Custom Lists dialog opens

Step 8: Choose a predefined list like days of the week or type your own list separated by commas, then click Add

- Custom Lists dialog

Your custom list is saved and selected

Step 9: Click OK to close Custom Lists, then click OK in the Sort dialog

- Custom Lists dialog and Sort dialog

Data is sorted according to your custom list order

Before vs After

Before

Column A has days listed as: Tuesday, Monday, Wednesday, Friday

After

Column A is sorted as: Monday, Tuesday, Wednesday, Friday following the weekday order

Settings Reference

Sort by

📍 Sort dialog box

Choose which column to sort your data by

Default: First column selected

Order

📍 Sort dialog box

Choose the sorting order or use a custom list

Default: A to Z

Custom List

📍 Custom Lists dialog (accessed from Sort dialog)

Define the exact order for sorting your data

Default: No custom list selected

Common Mistakes

Trying to sort by a custom order without creating or selecting a custom list

Excel sorts alphabetically or numerically by default if no custom list is chosen

Always select or create a custom list in the Sort dialog to apply your custom order

Selecting only part of the data range when sorting

This can cause data to become mismatched or lose row alignment

Select the entire data range including all columns before sorting

Summary

Custom sorting lets you arrange data in any order you want, like days or priorities.

You create or select a custom list in the Sort dialog to define the order.

Always select the full data range to keep rows intact when sorting.

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