Excel sort

How to sort data in Excel

If you like this tutorial, please spread the word!

Sorting data in Excel is one of the most useful features provided by this software.

We already saw an overview of the Excel sort command in the Excel Beginner’s Guide, but here, in this tutorial, we’ll go deeper into details.

Excel offers options to sort data either numerically, alphabetically, by date, or by other options depending on the field type being sorted.

The Excel sort command is of two types: the simple sort and the custom sort.

A- Sort data in Excel using the Simple sort

To perform a simple Excel sort on a column, click on any cell within that column and navigate to the ribbon. The Excel sort commands are provided into two tabs of the ribbon: the “Home” tab and the “Data” tab.

So, to sort data in Excel, you’ll either:

  • Go to the “Home” tab of the ribbon, and in the “Editing” group of commands, click on the “Sort & Filter” drop-down list and choose one of the two first commands depending on the order in which you want the information displayed.
Sort text in Excel using Home tab of the ribbon
Sort text in Excel using Home tab of the ribbon

OR,

  • Go to the “Data” tab of the ribbon, and in the “Sort & Filter” group of commands, click on one of the two sort commands depending on the order you want your column sorted in.
Sort text in Excel using the Data tab of the ribbon
Sort text in Excel using the Data tab of the ribbon

Excel will then immediately adjust the data following the order you specified; and of course, it keeps consistent your data by moving the entire rows and not just the cells of the selected column.

Note about the labels of the Excel sort commands:

The labels of Excel sort commands differ depending on the data type of the column by which we are sorting our data: text, numbers, or dates.

  • If it is a column of text, Excel sort commands are labeled “Sort A to Z” and “Sort Z to A”.
Sort text in Excel
Sort text in Excel
  • If it is a column of numbers, Excel sort commands are labeled “Sort Smallest to Largest” and “Sort Largest to Smallest”.
Sort numbers in Excel
Sort numbers in Excel
  • If it is a column of dates, Excel sort commands are labeled “Sort Oldest to Newest” and “Sort Newest to Oldest”.
Sort dates in Excel
Sort dates in Excel

Let’s now apply all of this on an example containing the three types of data, text, numbers, and dates:

Data to sort in Excel
  • To sort our data by Vendor, in ascending alphabetical order:

We click on a cell from the column “Vendor” and click on the “Sort A to Z” command from “Home” tab or “Data” tab of the ribbon.

The result is illustrated in the following screenshot:

Sort text in Excel in ascending alphabetical order
  • To sort our data by Amount, in descending numerical order:

We click on a cell from the column “Amount” and click on the “Sort Largest to Smallest” command from “Home” tab or “Data” tab of the ribbon.

The result is illustrated in the following screenshot:

Sort numbers in Excel in descending order
  • To sort our data by Date, in descending order:

We click on a cell from the column “Date” and click on the “Sort Newest to Oldest” command from “Home” tab or “Data” tab of the ribbon.

The result is illustrated in the following screenshot:

Sort dates in Excel in descending order
Important note about the consistency of data after Excel sort:

Because there are adjacent cells with related information pertinent to the rows being sorted, these rows are sorted by default as well. So, be careful when using a simple sort, as Excel may not perform this action by default.
For example, if there was an empty column with no title separating Vendor and Expenditure, Excel would have assumed the information to the right of vendor (separated by a null column) to be a separate data set, and the information would not have been sorted.

Example before sorting by Vendor where there is a null column to the right of the Vendor column:

Excel range of cells with empty column
Excel range of cells with empty column

Example after sorting by Vendor (A to Z) where there is a null column to the right of the Vendor column:

False Excel sorting due to an empty column
False Excel sorting due to an empty column

Notice that the information in the columns for Expenditure and Amount were not adjusted, because of the existence of the empty column.

B- Sort data in Excel using the Custom Sort

The Custom sort option in Excel allows more flexibility than the simple Excel sort. It allows you to sort multiple columns in Excel, at the same time; to sort multiple rows; and to perform a case-sensitive sort.

The Excel custom sort command is available in two tabs of the ribbon: the “Home” tab and the “Data” tab.

So, to perform a custom sort in Excel, first click on any cell from the range of cells you want to sort its columns; then:

You can go into the “Home” tab to the group of commands “Editing” and in the “Sort & Filter” drop-down list, click on “Custom Sort”.

Excel custom sort via the Home tab

Or, go into the “Data” tab to the group of commands “Sort & Filter” and click on “Sort”.

Excel custom sort via the Data tab

The Sort dialog box will display.

This dialog box gives options for:

  • Which column or row to sort by: All adjacent columns or rows with headers should display in this dropdown list.
Sort by option in Excel custom sort
Note: Sorting by rows is available in the “Options” button on this dialog box, where you would need to check the “Sort left to right” option.
Of course, your data should be logically organized by rows for this option to work as expected.
  • Sort on: Options include:
    • Cell Values: This will function in the same way as the simple sort and sort either alphabetically, by date, or from smallest to largest number.
    • Cell Color: Sorts by fill color of the cells in the column being sorted.
    • Font Color: Sorts by color of the font of the cells in the column being sorted.
    • Conditional Formatting Icon: Sorts by conditional formatting applied to the cells.
Sort on option in Excel custom sort
  • Order: Choose here the order of the Excel sort you want.

Note: For some of the above formatting options, multiple levels will need to be added to achieve the desired formatting result.

1- How to sort multiple columns in Excel using the custom sort

To sort multiple columns in Excel, you need of course to have equal values in one or many subsequent column(s), so you can have multiple columns sorted at the same time.

An example is worth a thousand words:

Let’s take the above example and try to sort our data, first by “Vendor” in ascending order, then by “Amount” in descending order for same vendors:

So, we click on any cell from our range of data, then we go to the “Data” tab of the ribbon and click on the “Sort” command in the “Sort & Filter” group.

Excel displays the “Sort” dialog box as illustrated below, where we first sort by “Vendor”, on “Cell Values”, with an ascending alphabetical order.

Then, we click on the “Add level” button to add the second level of sorting, and we sort by “Amount”, on “Cell Values”, in descending numerical order.

Sort multiple columns in Excel

When we click on “OK”, Excel sorts our data first by “Vendor”, then for same vendors, sorts by “Amount”. I highlighted the relevant parts for more comprehension.

Example of sorting multiple columns in Excel

2- Excel sort by color using the custom sort

For this example, some of the Expenditure entries have been coded, to indicate their importance, either in green (less important), yellow (medium importance), or red (very important).

Data in Excel to sort by color
Data in Excel to sort by color

Suppose we want to see the most important (red) Expenditures listed at the top, then the least important (yellow, then green), and within this want to have the higher Amount items listed higher on the list.

For this, we use a Custom Sort that will allow us to sort by color and to sort multiple columns.

To do this, we click on any cell from our range of data, then navigate to Custom sort (in the “Home” tab or the “Data” tab of the ribbon) and add multiple levels in the “Sort” dialog box using the Add Level button.

Note that our range of data has header columns. If the range of data being sorted does not have a header column, uncheck the box “My data has headers”  to display the column letter in the “Sort by” field instead of displaying the value (header) from the first row of the data set.

Excel sort by color

From here we can click the “Add Level” button to create a new row within the dialog box and updating it with the next set of information. Although, the process can be made easier when creating multiple levels with similar options, by using the “Copy Level” button: Click on an existing level to highlight it. This will then copy the highlighted level (in this case the ones with cell color = red) and recreate it.

Copy level in custom sort dialog box

We can then simply change the color instead of having to re-update all the fields.

Example of Excel sort by color

Note that you can use the up and down buttons Up and down buttons in Excel custom sort to adjust the order of the entries. In our case, no need to use them as we entered the exact order we want.

For this example, we also wanted to sort by Amount, so we can add one additional level for this at the bottom of the list since more importantly we want to sort by color.

Excel sort by color and by multiple columns

The result of our Excel sort by color and by multiple columns is illustrated below:

Example of Excel sort by color and multiple columns

3- Additional options with the Custom sort

Notice that If we return to the Custom sort dialog by navigating to it in the ribbon, we’ll find that all the options previously selected are still there, so we can come back and edit the details if needed.

Excel sort by color and by multiple columns

Should we decide one of the rows is not required, the “Delete Level” button will remove the highlighted level record.

There are also additional options available by clicking the “Options” button.

This will display the “Options” dialog box which allows us the ability to have Excel differentiate based on capitalization (Case sensitive) and also allows us to change the orientation of the sort from the default of top to bottom applicable to columns, to instead sort by rows from left to right suitable for data organized by rows.

Case sensitive and sort rows with custom sort

Selecting these options impact all levels in the custom sort.


If you like this tutorial, please spread the word!

Leave a Reply

Your email address will not be published. Required fields are marked *