How to Sort in Excel

Sorting in Excel is one of the most frequently used commands for data analysis. When handling a large amount of data, sorting makes the data set easier to comprehend by organizing the data in specific, designated ways. 

Excel allows users to sort both text and numbers in ascending order, descending order, alphabetical order as well as by oldest to newest and color. It is designed to be a simple tool to organize and prepare the data for further analysis.

Let's take a look at how to sort in Excel, from the absolute basics to multi-level sorting and some helpful hacks.

---

---

How to sort in Excel: the basics

Depending on how the data set is being organized, users can apply sorting to the entire spreadsheet or a specific range of cells.

1. To sort the entire spreadsheet

To sort the entire spreadsheet, the data is always organized by a specific column. When sorting is applied, the corresponding information in the row moves up or down accordingly.

There’s also only one set of data available in the designed spreadsheet. In the example below, we will sort these city names alphabetically, from A to Z.

1. Click on any city name under column A. In this demonstration, it’s A2 (Los Angeles). 

2. Under Data on the ribbon, click the Sort A to Z command to sort the city names alphabetically from A to Z.

The city names under column A are now sorted alphabetically from A to Z, with the corresponding zip and state name intact.

2. To sort a specific range of cells

Often a specific range of cells is selected for sorting as there are other sets of data available in the same spreadsheet, or only a specific part of data is required in the analysis. It helps to avoid mixing up the information by specifying the size of the data set.

Using the same set of data above, this time, we want to sort the data based on zip code but specifying the range of cells.

1. Begin by selecting the range of cells. In this example, it’s A1:C15.

2. Under Data on the ribbon, click the Sort command. 

The Sort dialogue box will pop up and it allows you to sort by column, sort on criteria, and order. In our case, it’s to sort the zip column, based on the cell values, from smallest to largest. Click Ok.

The sort is complete. The city names are being organized based on the zip code, from smallest to largest.

Multi level sorting in Excel

In most scenarios, sorting data by one column serves the purpose. However, when handling a large database, it’s common to organize and sort the data on multiple levels based on priorities. 

There are two ways to do multi level sorting — by using the dialogue box or the A-Z/Z-A Sort icons. The former option involves a few more steps but gives considerably more precise results. The latter is a quick shortcut to multi level sorting and might result in errors sometimes. 

1. By using dialogue box

In the example, say we want to sort the first name alphabetically from A to Z and their age from smallest to largest.

1. Begin by selecting the data range (A1:E11). 
2. Click on the Sort icon under Data in the ribbon, and then the sort dialogue box will appear.

In this scenario, there are two levels:

• 1^st level: to sort the first names alphabetically, from A to Z
• 2^nd level: to sort the ages from smallest to largest

To apply the 1^st level, select the options below accordingly:

• Sort by: First Name
• Sort On: Cell Values
• Order: A to Z 

To add the 2^nd level, click Add Level. Then another level of sorting options will appear. For the 2^nd level:

• Sort by: Age
• Sort On: Cell Values
• Order: Smallest to Largest.

Then, click Ok. The data set has been sorted based on the given criteria — to first sort by First Name (from A to Z) then by Age (from smallest to largest).

---

Note: If users choose to sort by Age (smallest to largest) then First Name (A to Z) the data will look completely different. It’s important to identify the priorities prior to selecting the first column to sort.

---

More levels of sorting are available by adding levels in the dialogue box. Likewise, users can choose to delete unwanted levels.

2. By using A-Z/Z-A Sort icons

Using the same sample data set, we aim to sort the first name alphabetically from A to Z and their age from smallest to largest. However, the A-Z/Z-A Sort icons work a bit differently than the dialogue box. Users will need to sort the second level first, then the first level.

1. Begin by selecting the column of the 2^nd level — Column D (Age in this case). 
2. Click on the A to Z icon on the Data tab.

3. A sort warning will appear. Select Expand the selection to ensure that the full data set is sorted as well. 
4. Click Sort. 

5. Now, to sort the first name, repeat the same steps above. Select Column A (First Name). Then click the Sort A to Z icon. Choose to expand the selection. 

The data set has been sorted based on the given criteria — to firstly sort by First Name (A to Z) then by Age (smallest to largest). This gives the same results as using the dialogue box.

---

Note: Though both ways give the same results, it’s recommended to sort using the dialogue box as it presents a clear picture of the levels being sorted and in what orders. The Sort icon works best in situations where the data set is relatively simpler and smaller in scale with a few columns. 

---

How to sort in custom order

In some cases, the default sorting options in Excel are not able to sort the data in the desired order. When that happens, set up a custom sort that accommodates your own sorting needs.

In the example below, we are required to sort the employees’ education level from highest to lowest.

If we sort the column alphabetically (from A to Z or from Z to A) the order will not be correct. The correct order should be Doctoral, Master, Bachelor, and Lower secondary. So, to sort in the order we want, we’ll need to create a custom sort to tackle this scenario.

1. Begin by selecting the data set.
2. Click the Sort icon to access the sort dialogue box.
3. Select Custom List under Order.
4. Click Ok. 

The custom lists dialogue box will appear.

5. Click NEW LIST under the Custom lists box, and manually enter the desired order under list entries: Doctoral, Master, Bachelor, and Lower secondary. Press Enter to separate list entries.

6. Click Ok to proceed. The new custom order is now available as a sorting order.
7. Select the desired order, and click Ok. 

Now the data set is being sorted based on the education level in the desired order — Doctoral, Master, Bachelor, and Lower secondary. 

How to sort rows in Excel

The examples above illustrate how to sort data by columns. However, for some data sets, where data is organized by row, it is necessary to sort data by row.

The example below illustrates the year-round climate data of Texas. We can sort rows so that the month with the highest average high in Fahrenheit is on the left. 

1. Same as sorting columns, start by selecting the range of cells (A1:M4). 
2. Click the Sort icon under Data to access the sort dialogue box. 

3. Click Options. The Sort Options dialogue will appear.
4. Select Sort left to right to opt for sorting row
5. Click Ok to proceed. 

You may notice the left corner now says Row, instead of Column like before.

6. Select Row 2 under Sort by. Row 2 represents the data for the average high in Fahrenheit. 

7. Under Order, select Largest to Smallest, then click Ok. 

The data is sorted by the values under row 2 — average high in F, from largest to smallest.  

---

Challenge me!

Test your advanced sorting skills with this FREE Excel challenge!

---

How to sort by cell icon

Sometimes, cell icons are added to the data set to enhance the results. Excel allows sorting by cell icons in addition to sorting by alphabetical order, ascending order, descending order, or custom order.

There are two ways to do it, either by using the sort dialogue box or by using the dropdown list. 

1. Sort by cell icon with the sort dialogue box

In the example below, we will sort the cell icons under Age based on the color, with yellow on top.

1. To begin, select the range of cells and click the Sort icon under the Data tab. The sort dialogue box will appear.
2. Under Order, select Yellow, On Top. Click Ok. 

Now, the yellow cell icons are on top.

2. Sort by using dropdown list. 

1. Create a dropdown list by adding a filter under each column.

2. Click the arrow to access the dropdown list.
3. Select Sort by Color and select the desired color to be on top (green in this example). 

The data set is sorted accordingly, with green cell icons on top. 

Problems with sorting in Excel

As handy as sorting seems to be, sometimes the data can’t be sorted properly due to some of the possible scenarios below.

1. Hidden rows or columns

Excel can’t sort hidden rows or columns. They will remain in the same cells regardless of the sorting order.

For example, if row 14 is hidden, when we sort the city names from Z to A, all other city names will reshuffle their order, except row 14.

The city names are sorted from Z to A but because row 14 continues to be hidden it’s not affected by the sort.

Before performing sorting in Excel, ensure that there are no hidden rows or columns in the data set.

2. Data inconsistency

Excel can sort data of the same type only. Sometimes, a number could actually be text. When that happens, Excel cannot sort properly.

In the example below, “90069” is actually a text. So when performing a sort from smallest to largest, “90069” is at the bottom as it’s not recognized as a number.

Before performing any sorting on numbers, examine the data type, and convert the column to text if necessary.

3. Multirow header

Sorting in Excel works only when the header is in a single row only. If there’s a multi header like below, Excel will sort the second header as a part of the data set.

To avoid this, manually select the range of cells you want to be sorted as described in our basics section above.

The data set below has multirow headers.

If we sort the city name from Z to A, the second header (name) is also sorted into the data set.

Summary 

We hope this resource was helpful in learning how to sort in Excel! Sorting is a great tool to organize vast amounts of data in many different ways. 

Data can be sorted alphabetically from A to Z or from Z to A. It can be sorted numerically from smallest to largest or from largest to smallest as well as with cell icons and custom orders.

However, to ensure an accurate sort, review the data to make sure that there are no hidden rows or columns, that the data type is consistent, and that there is no multirow header.

To learn more about sorting, filtering, and other tools and techniques to work with your data, try our Excel - Basic and Advanced course now. You can also try our free Excel in an Hour course to cover some basics in Excel.

Learn Excel for free

Start learning formulas, functions, and time-saving hacks today with this free course!

Start free course