You can easily sort your data alphabetically, based on the value in the cells, or by cell and font color. You can also do multi-level column sorting (i.e., sorting by column A and then by column B) as well as sorting rows (from left to right). And if that was not enough, Excel also allows you to create your own custom lists and sort based on that too (how cool is that). So you can sort data based on shirt sizes (XL, L, M, S) or responses (strongly agree, agree, disagree) or intensity (high, medium, low) And in this massive in-depth tutorial, I will show you all these sorting options and some cool examples where these can be useful. Since this a huge tutorial with many topics, I am providing a table of content below. You can click on any of the topics and it will instantly take you there.
Accessing the Sorting Options in Excel
Since sorting is such a common thing needed when working with data, Excel provides a number of ways for you to access the sorting options.
Sorting Buttons in the Ribbon
The fastest way to access sorting options is by using the sorting buttons in the ribbons. When you click on the Data tab in the ribbon, you will see the ‘Sort & Filter’ options. The three buttons on the left in this group is for sorting the data.
The two small buttons allow you to sort your data as soon as you click on these.
For example, if you have a data set of names, you can just select the entire dataset and click on any of the two buttons to sort the data. The A to Z button sorts the data alphabetically lowest to highest and the Z to A button sorts the data alphabetically highest to lowest. This buttons also work with numbers, dates or times.
Sorting Dialog Box
In the Data tab in the ribbon, there is another Sort button icon within the sorting group.
When you click this Sort button icon, it opens the sorting dialog box (something as shown below).
Sorting dialog is the most complete solution for sorting in Excel. All the sorting related options can be accessed through this dialog box. All the other methods of using sorting options are limited and don’t offer full functionality. This is the reason I always prefer using the dialog box when I have to sort in Excel. A major reason for my preference is that there is very less chance of you going wrong when using the dialog box. Everything is well structured and marked (unlike buttons in the ribbon where you may get confused which one to use).
Sorting Options in the Filter Menu
If you have applied filters to your dataset, you can also find the sorting options along with the filter options. A filter can be applied by selecting any cell in the dataset, clicking on the Data tab and clicking in the Filter icon.
Suppose you have a dataset as shown below and you have the filter applied.
When you click on the filter icon for any column (it’s the small downward pointing triangle icon at the right of the column header cell), you’ll see some of the sorting options there as well.
Note that these sorting options change based on the data in the column. So if you have text, it will show you the option to sort from A to Z or Z to A, but if you have numbers, it will show you options to sort from largest to smallest or smallest to largest.
Right-click options
Apart from using the dialog box, using right-click is another method that I sometimes use (it’s also super fast as it only takes two clicks). When you have a dataset that you want to sort, right-click on any cell and it will show you the sorting options.
Note that you see some options that you don’t see in the ribbon or in the Filter options. While there is the usual sort by value and custom sort option (which opens the Sort dialog box), you can also see options such as Put selected Cell color/Font color/Formatting icon on the top. I find this option quite useful as it allows me to quickly get all the colored cells (or cells with a different font color) together at the top. I often have the monthly expense data that I go through and highlight some cells manually. I can then use this option to quickly get all these cells together at the top. Now that I have covered all the ways to access sorting options in Excel, let’s see how to use these to sort data in different scenarios.
Sorting Data in Excel (Text, Numbers, Dates)
Based on the type of data you have, you can use the sorting options in Excel.
Sorting by Text
Suppose you have a dataset as shown below and you want to sort all these records based on the name of the student in alphabetical order.
Below are the steps to sort this text data in alphabetical order: The above steps would sort the entire dataset and give the result as shown below.
Why not just use the buttons in the ribbon? The above method of sorting data in Excel may look like a lot of steps, as compared to just clicking the sort icon in the ribbon. And this is true. The above method is longer, but there is no chance of any error. When you use the sort buttons in the ribbon, there are a few things that can go wrong (and this could be hard to spot when you have a large data set. While I discuss the drawbacks of using the buttons later in this tutorial, let me quickly show you what can go wrong. In the below example, since Excel cannot recognize that there is a header row, it sorts the entire dataset, including the header.
This issue is avoided with the sort dialog box as it explicitly gives you the option to specify whether your data has headers or not.
Sorting by Numbers
Now, I am assuming you have already gotten a hang of how text sorting works (covered above this section). The other types of sorting (such as based on numbers or dates or color) is going to use almost the same steps with minor variations. Suppose you have a dataset as shown below and you want to sort this data based on the marks scored by each student.
Below are the steps to sort this data based on numbers: The above steps would sort the entire dataset and give the result as shown below.
Sorting by Date/Time
While the date and time may look different, they are nothing but numbers. For example, in Excel, the number 44196 would be the date value for 31 December 2020. You can format this number to look like a date, but in the backend in Excel, it still remains a number. This also allows you to treat dates as numbers. So you can add 10 to a cell with date, and it will give you the number for the date 10 days later. And the same goes for the time in Excel. For example, the number 44196.125 represents 3 AM on 31st December 2020. While the integer part of the number represents a full day, the decimal part would give you the time. And since both date and time are numbers, you can sort these like numbers. Suppose you have a dataset as shown below and you want to sort this data based on the project submission date.
Below are the steps to sort this data based on the dates: The above steps would sort the entire dataset and give the result as shown below.
Sorting by Cell Color / Font Color
This option is amazing and I use it all the time (maybe a tad bit too much). I often have data sets that I analyze manually and highlight cells while I am doing it. For example, I was going through a list of articles on this blog (which I have in an Excel sheet) and I highlighted the ones which I needed to improve. And once I am done, I can quickly sort this data based on the cell colors. This helps me in getting all these highlighted cells/rows together at the top. And to add to the awesomeness, you can sort based on multiple colors. So if I highlight cells with article names which need immediate attention in red and some which can be dealt with later with yellow, I can sort the data to show all the red rows first followed by the yellow. If you’re interested in learning more, I recently wrote this article on sorting based on multiple colors. In this section, I will quickly show you how to sort based on one color only Suppose you have a dataset as shown below and you want to sort by color and get all the red cells at the top.
Below are the steps to sort by color: The above steps would sort your dataset by color and you will get the result as shown below.
Multiple Level Data Sorting
In reality, datasets are rarely as simple as the one that I am using in this tutorial. Yours may extend to thousands of rows and hundreds of columns. And when you have datasets so big, there is also a need for more data slice and dice. Multiple level data sorting is one of the things you may need when you have a large data set. Multiple level data sorting means that you can sort the dataset based on values in one column and then sort it again based on values in another column(s). For example, suppose you have the dataset as shown below and you want to sort this data based on two criteria:
The output of this sorting based on the above two criteria will give you a dataset as shown below. In the above example, we have the data first sorted by the regions and then within each region, the data is further sorted by the sales value. This quickly allows us to see which sales reps are doing great in each region or which ones are doing poorly. Below are the steps to sort the data based on multiple columns: Below is a video where I show how to do a multi-level sorting in Excel:
Sorting Based on a Custom List
While Excel already has some common sorting criteria (such as sorting alphabetically with text, smallest to largest or largest to smallest with numbers, oldest to newest or newest to oldest with dates), it may not be enough. To give you an example, suppose I have the following dataset:
Now, if I sort it based on the region alphabetically, I have two options – A to Z or Z to A. Below is what I get when I sort this data alphabetically from A to Z using the region column.
But what if I want this sort order to be East, West, North, South? You, of course, can rearrange the data after sorting, but that’s not the efficient way to do this. The right way to do this would be using custom lists. Some example where custom lists can be useful include:
Sorting the data based on region/city name Sorting based on T-shirt sizes – Small, Medium, Large, Extra Large Sorting based on survey responses – Strongly Agree, Agree, Neutral, Disagree Sorting based on probability – high, medium, low
The first step in trying to sort based on the custom criteria is to create the custom list.
Steps to create a custom list in Excel:
Once you have completed the above steps, Excel will create and store a custom list that you can use for sorting data. Note that the order of the items in the custom list is what determines how your list will be sorted.
Steps to sort using a custom list
Suppose you have the dataset as shown below and you want to sort it based on the regions (the sort order being East, West, North, and South) Since we have already created a custom list, we can use it to sort our data. Here are the steps to sort a dataset using a custom list: The above steps will sort your dataset based on the custom sort criteria.
Note: You don’t have to create a custom list before-hand to sort the data based on it. You can also create it while you’re in the Sort dialog box. When you click on Custom List (in step 7 above), it opens the custom list dialog box. You can create a custom list there as well. Custom lists are not case sensitive. In case you want to do a case-sensitive sorting, refer to this example.
Sorting from Left to Right
While in most cases you’ll likely sort based on the column values, sometimes, you may also have a need to sort based on the row values. For example, in the below dataset, I want to sort it based on the values in the Region row.
Although this type of data structure is not as common as the columnar data, I have still seen a lot of people working with this kind of construct. Excel has an in-built functionality that allows you to sort from left to right. Below are the steps to sort this data from left to right: The above steps would sort the data left to right based on Row 1 values.
Excel doesn’t recognize (or even allow you to specify) the headers when sorting from left to right. So you need to make sure your header cells are not selected when sorting the data. If you select headers cells as well, these will be sorted based on the value in it. Note: Another way of sorting the data from right to left could be to transpose the data and get it in a columnar form. Once you have it, you can use any of the sorting methods covered so far. Once the sorting is done, you can then copy the resulting data and paste it as transposed data. Below is a video where I show how to sort data from left to right in Excel
Case Sensitive Sorting in Excel
So far, in all the example above, the sorting has been case independent. But what if you want to make the sorting case-sensitive. Thankfully, Excel allows you to specify whether you want the sorting to be case-sensitive or not. Suppose you have a dataset as shown below and you want to sort this data based on the region column:
Below are the steps to sort data alphabetically as well as make it case sensitive: The above steps would not only sort the data alphabetically based on the region, but also make it case-sensitive. You’ll get the resulting data as shown below:
When you sort from A to Z, lower case text is placed above the upper case text.
Getting the Original Sort Order
Often when sorting data in Excel, you may want to go back to the earlier or the original sort order and start afresh, While you can use the undo functionality in Excel (using Control Z) to go back one step at a time, it may be confusing if you have already done multiple things after sorting the data. Also, undo works only till you have the workbook open, but when you save and close and it and open it later, you won’t be able to go back to the original sort order. Here are two simple ways to make sure you don’t lose the original sort order and get it back even after sorting the data: In this section, let me quickly show you what mean by adding a series and using it to get original sort order back. Suppose you have a dataset as shown below:
To make sure you have a way to get back this data after sorting it, add a helper column and have a series of numbers in it (as shown below).
When you have the helper column in place, make sure you include it while sorting this dataset. Suppose I sort this data based on the region and end up getting the data as shown below:
Now, if I want to go back to the original dataset, I can simply sort this data again, but based on the helper column (from low to high). Simple.. isn’t it? In case you don’t want the helper column to show, you can either hide it or create a backup and then delete it.
Some Common Issues While Sorting Data in Excel
At the beginning of this article, I showed you different ways to sort data in Excel (including sort buttons in the ribbon, right-click options, filter option, and the sort dialog box). And to reiterate, using the sort dialog box minimizes the chances or any issues or error that may arise. Now, let me show you what can go wrong when you use the sort button from the ribbon (the ones shown below)
Not Identifying the Column Headers
Suppose you have a dataset as shown below: This looks like a decently formatted data set with headers clearly formatted with cell color and bold font. So when you sort this data based on the names (using the sort buttons in the ribbon), you would expect the header to remain at the top and rest of the data to get sorted. But what happens when you do it – the header is also considered as normal data and gets sorted (as shown below).
While Excel is smart enough to recognize headers, in this example, it failed to do so. When using the sort icon buttons in the ribbon, there was no way for you to manually specify that this data has headers. Note: This issue occurs when I have the dataset and I add and format the headers and sort it. Normally, Excel would be smart enough to identify that there are headers in a dataset (especially when the data type of the header and the data in the column is different). But in this case, it failed to do so when I added the header and sorted right away. If I save this workbook, close it and reopen it, Excel somehow manages to identify the first row and header. While this may not be an issue in most cases, there is still a chance when you use the sort icons in the ribbon, Using a dialog box eliminates this issue as you can specify that you have headers.
Not Identifying Blank Rows/Columns
This sorting issue is a little more complex – and a lot more common than you would imagine. Suppose you have a dataset as shown below. Note that row number 6 is hidden (and it’s a blank row).
Now, if I select any cell in the first four rows of the data (the ones above the hidden blank row) and sort this data using the sort icon buttons, it would only sort the first four records on the data set (row 2 to 5). Similarly, if I select any cell in the dataset below the hidden blank row, it will only sort the last seven rows.
And unless you’re looking for it specifically, you’re likely to miss this horrible error. How to make sure you don’t end up making this sorting error? To make sure you don’t fall prey to this issue, you need to check your dataset before sorting it. Select the entire dataset before sorting the data. You can do that by selecting any cell in the dataset and then using the keyboard shortcut – Control + A. If there are blank rows/columns in your dataset, the entire dataset won’t get selected. You can identify this by quickly scanning the outline of the selection. If you see that there is some data left outside the selection, you can manually select it. To make sure this issue is avoided, it’s best to first unhide all hidden rows and columns and then proceed to sort the data by selecting the entire data set or by first deleting the blank rows.
Partial Sorting (Based on the Last Name)
Sometimes, you may have a dataset that you want to sort based on part of the text. For example, suppose I have the dataset as shown below and I want to sort this data based on the last name.
To do this, I need to separate this data so that I have only the last names in one column. When I have it, I can use that column to sort the data. Below is a formula that can give me the last name: This will give you a result as shown below.
Now you can use the Last Name column to sort this data. Once done, you can delete the last name column or hide it. This is just one example of sorting based on partial data. Other examples may include sorting based on cities in an address or employee ID based on department codes. Also, in case the text based on which you need to sort is at the beginning of the text, you can use the normal sort feature only.
Other Sorting Examples (Using Formula and VBA)
So far in this tutorial, I have covered the examples where we have used the in-built sorting functionality in Excel.
Automatically Sorting Using a Formula
When you use the in-built sort functionality and then make any change in the data set, you need to sort it again. The sort functionality is not dynamic. In case you want to get a dataset that sorts automatically whenever there is any change, you can use the formulas to do it. Note that to do this, you need to keep the original dataset and the sorted dataset separate (as shown below)
I have a detailed tutorial where I cover how to sort alphabetically using formulas. It shows two methods to do this – using helper columns and using an array formula. Excel has also introduced the SORT dynamic array formula which can easily do this (without helper column or a complicated array formula). But since this is quite new, you may not have access to it in your version of Excel.
Sorting Using VBA
And finally, if you want to bypass all the sorting dialog box or other sorting options, you can use VBA to sort your data. In the below example, I have a dataset that sorts the data when I double click on the column header. This makes it easy to sort and can be used in dashboards to make it more user-friendly.
Here is a detailed tutorial where I cover how to sort data using VBA and create something as shown above. I have tried to cover a lot of examples to show you different ways you can sort data in Excel and all the things you need to keep in mind when doing it. Hope you found this tutorial useful.
Search and Highlight Data Using Conditional Formatting How to Sort Worksheets in Excel using VBA How to Create a Data Entry Form in Excel How to Compare Two Columns in Excel Excel TRIM Function to Remove Spaces and Clean Data Flip Data in Excel | Reverse Order of Data in Column/Row How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas!
