Qling | Resources | Excel - Sort

Sorting Data in Excel

While working with large set of data, you may need to sort data based on a single criteria or multiple ones.

On this page, we will look at multiple ways in which you can use the Sort utility of Excel.

Sorting Data in Excel

While working with large set of data, you may need to sort data based on a single criteria or multiple ones.

On this page, we will look at multiple ways in which you can use the Sort utility of Excel.

A desk with a keyboard, phone, and a chart

How to Sort Data in Excel?

Tables or ranges in Excel with multiple columns provide a lot of information. The data can be viewed from different dimensions by sorting data on a single column or by multiple columns. This ability to sort data in Excel helps in interpreting the data better. There are multiple ways of Sorting data. Let us look at a few of these.

Sorting options can be accessed from two ribbons in Excel. You can access the sorting options from the Home ribbon or the Data ribbon.

Option 1: Home Ribbon – Use the Sort & Filter drop-down in the Editing Group

Option 2: Data Ribbon – Use the Sort & Filter group

In both places, there are three sorting choices; two of them are quick sorting options where you can sort an entire table in ascending or descending based on a single column. The third option is Custom Sort, which gives you multiple options such as multiple column sort, sorting on color, creating your sorting order, and much more. Below is an image of the Sort & Filter Group as it appears in the Data Ribbon

Sort options are present in the Sort & Filter Group of Data Tab
Click on Advanced in the Sort & Filter Group in Data Tab

Sort on Basis of a Single Column

The table below captures the participant list for a webinar where some of the participants have completed the registration, while some others are yet to complete the registration process.

Click on any cell and then click on Sort to sort the entire table on basis of a single column

If you want to sort by Name, then you can select any cell in that column and click on 'A Z' or 'Z A' to sort the data in ascending or descending order.

In the above example let us assume we clicked on cell A9 (NAME) and clicked on 'A Z', the table gets sorted as below.

Click on any cell and then click on Sort to sort the entire table on basis of a single column

Sort Data of only a Single Column

In case there is a need to sort data of only one column, then select the entire data for that column and press 'A Z' or 'Z A' to sort the data.

Once you click on either of the options then a dialog box as shown below appears.

Options to sort a column or the entire table

If you select the option, "Expand the selection", then the entire table data gets selected and the data is sorted basis that column. However, if you select the "Continue with the current selection" option then the data of only that column is sorted.


Custom Sort - Sorting Table Based on Multiple Columns

Let us assume that in this example you want to sort the data based on the source and then by who all have completed the registration and finally by to whom the link has been sent. This can be achieved using the Custom Sort option. The steps are as below:

  1. Select the table data or select any cell in the table
  2. Click on Custom Sort
  3. In the Sort by drop-down select "Source"
  4. Click on Add Level button
  5. In the Then by drop-down select "Registration Complete". Select the order as Z to A in case you want all the "Yes" responses to appear before "No"
  6. Click on Add Level button
  7. In the Then by drop-down select "Link Sent". Select the order as Z to A in case you want all the "Yes" responses to appear before "No"
  8. Click OK
Click on Sort and then select the columns in the order you want to sort

The result is as below:

Data Sorted basis multiple columns using the custom sort feature

Custom Sort - Define the Order of Sorting

Excel provides you the flexibility to sort the data in a specific order other than ascending or descending order.

In the example, if you were to Sort on the "Month of Registration" in ascending order, then the data would be sorted as November, October and September in that order. This is because Excel will sort them alphabetically. However, this is not the sorting that we expected.

To sort in the ascending order of months, you can follow the following steps:

  1. Select the table data or select any cell in the table
  2. Click on Custom Sort
  3. In the Sort by drop-down select "Month of Registration"
  4. In the Order drop-down, select "Custom List…"
  5. Select the list, "January, February…, December
  6. Click OK on the Custom List dialog box
  7. Click OK on the Sort dialog box
Select Custom Lists to sort data on months

Your data will now be correctly sorted as per months:

Data Sorted basis months using custom sort lists

Custom Sort - Sort on Font Color, Cell Background, or Conditional Formatting Icons

In case some of the rows in your table are colored and signify a status, e.g. green for complete or red for delayed.

You can sort on the data basis the color of text or background or conditional formatting icons. You can do this by following these steps:

  1. Select the table data or select any cell in the table
  2. Click on Custom Sort
  3. In the Sort by drop-down select "Name"
  4. Sort On drop-down select "Cell Color"
  5. In the Order drop-down, select the green color and the option, "On Top"
  6. Click OK
Select Custom Sort to sort data on cell color

Your data will be sorted as per below:

Data Sorted basis cell color


LinkedIn Logo
Facebook Logo
Instagram Logo
Pinterest Logo