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.