How to Sort in Excel – An Easy to Follow Guide

What is Sort in Excel?

In Excel, sorting means arranging the data in a specific order. If you have data with numbers, you can sort it in smallest to largest or largest to smallest order. And if you have text values, you can sort them in A to Z or Z to A order with the Sort option. You can also use a custom order to sort the data.

Using the Sort Option

To open the sort options in Excel:

1. Go to the Data Tab.
2. Go to the Sort & Filter group.
3. Click on the Sort Button.

There are three buttons there which you can use to sort the data:

1. Smallest to Largest or A to Z – With this button, if you have numbers, you can sort data in smallest to largest order, and if you have text, then you can sort it in A to Z order.
2. Largest to Smallest or Z to A – With this button, if you have numbers, you can sort data in the largest to smallest order, and if you have text, you can sort it in Z to A order.
3. Sort Button – You can open the “Sort” dialog box with this button.

You can also add all these buttons to the quick access toolbar. With these buttons, you don’t need to go to the Data Tab when you want to sort data. These buttons are always available to use.

Open the Sorting Dialog Box

You can open the sorting dialog box from the Data tab or use the keyboard shortcut Alt > A > S > S.

When you open the SORT dialog box, there are a lot of options that you can use. We will look at all these options later in this guide, but before that, let’s sort the data.

Sorting Data in Excel

1. First, select the column name from the “Sort By” which you want to use for sorting. In this example, we are going to use the Amount column, which has numeric values.
2. After that, select the order from the “Order” drop-down. Here, we are using “Largest to Smallest” to sort the data.
3. Next, if your data has headers, make sure to tick “My data has headers”. And if your data doesn’t have headers, leave it unchecked.
4. In the end, click OK to sort the data.

Understanding All the Available Options in Sort Dialog Box

To use the SORT option in Excel effectively, you must know all the options available in the sort dialog box. So, let’s understand all the options one by one.

1. Add Level – Add more levels (columns) to the sorting with this button. That means you can use more columns (or rows) for sorting.
2. Delete Level – Select a level and then click this button to delete it.
3. Copy Level – With this, you can copy or duplicate a level you have already specified.
4. Options – This further has a few options which you can use. You can make the sort case-sensitive and change the orientation of the sort (left to right instead of top to bottom). You can learn more about it from this quick tutorial on horizontal sort.
5. My data has headers – Excel ticks this automatically when it detects a header row. It tells Excel that the data you want to sort has headers. And if your data doesn’t have headers, make sure to untick it.

Use a Custom List to Sort

In the below example, we have a list of months with the amount. And in this data, the months are sorted alphabetically (A to Z). We have April month at the beginning and September at the end.

Now we will use the custom list order to sort months correctly. So, for this, open the sort dialog box and select the Months column from the “Sort By”. And after that, select “Custom List…” from the order.

And the moment you select the “Custom List…” it opens a new dialog where you can select the list used to sort data. And here, we will use the list of short month names and click OK.

Now, you have the list of month names in the order to sort, and when you click OK, it sorts the data using the name of the months starting from Jan and ending in Dec.

Case Sensitive Sort

When you click on the “Options” button, you can select to apply case-sensitive sorting. In an A to Z case-sensitive sort, a value that starts with a lowercase letter comes before the same value that starts with an uppercase letter.

You can see in the example below; we have a list of names where some are in upper case and some are in lower case.

And when you tick the case-sensitive option while sorting the data, it sorts the data by considering both uppercase and lowercase values.

In the above example, the names that start with b and B come first, and the name with C comes after them.

Sort by Cell Color or Font Color

You can also sort data using the cell color or font color. In the example below, we have a color for each set of three months. Now we need to sort the data in a way that April-Jun should be at the top, and Jan-Mar should be at the bottom.

First, you need to create multiple levels to use this cell colour sort option. And in each level, you need to define the color you want to get on the top.

As you can see, the color we used in the first level is the Apr-Jun color. And then, Jul-Sep’s color, Oct-Dec’s color, and in the end, Jan-Mar’s color. And the moment you click OK, it sorts the data using the sort order we specified.

And in the same way, you can sort the data using the font color instead of the cell color. You only need to select the “Font Color” option from the “Sort On”.

The rest of the steps are the same as we have used in the “Cell Color”.

Sort by Conditional Formatting

The sort dialog box also allows you to sort cells based on conditional formatting icons. In the example below, you can see four kinds of arrows on the amount column values.

Now, when you open the sort dialog box, add levels to sort data based on the icons.

From the “Sort On” drop-down, select the “Conditional Formatting Icon” and select the icon you want to put on the top while sorting. In the above example, we have four levels of sorting to sort all four icons.

And when you click on OK to sort, it sorts data based on the levels you have specified.

Keyboard Shortcuts to Sort

• Alt > A > S > A: sort the selected column Smallest to Largest or A to Z.
• Alt > A > S > D: sort the selected column Largest to Smallest or Z to A.
• Alt > A > S > S: open the Sort dialog box.

Press these keys one after another, not together. Before you use them, select one cell in the column you want to sort by, so Excel sorts the whole data around it.

Sort with a Formula (SORT and SORTBY)

In Microsoft 365 and Excel 2021 and later, you can also sort with a formula. The result is a sorted copy of the data that updates on its own when the data changes, and the original data stays as it is. The following formula sorts A2:B13 by the second column (Amount) from largest to smallest:

=SORT(A2:B13,2,-1)

And to sort by a column that you don’t want to show in the result, use SORTBY. This one returns only the month names, sorted by the amounts in B2:B13:

=SORTBY(A2:A13,B2:B13,-1)

Go Back to the Original Order

Right after sorting, you can undo it with Ctrl + Z. But once you have saved and closed the file, there’s no way back. So, before you sort, add a helper column with serial numbers (1, 2, 3 and so on). Later, you can sort by that column, Smallest to Largest, to get the original order back.

Frequently Asked Questions

Important Sorting Tutorials

• Sort Horizontally in Excel
• Sort By Date in Excel
• Sort in Alphabetical Order in Excel
• Custom Sort in Excel

• Sort by Last Name in Excel
• Sort by Month (Dates) in Excel
• Sort Multiple Columns in Excel
• Reasons for Sorting Problems in Excel