Articles

Sort in Excel: A Complete Guide

Pinterest LinkedIn Tumblr

Sorting our life might be difficult, but sorting an Excel sheet is a piece of cake. We always like to be sorted and not cluttered, so is it with data. Here are a few interesting concepts about ‘Sort’. Happy Sorting!

full guide to sort feature in excel

Sort in MS Excel

Sorting refers to presenting the data in a defined order. For example, let’s take a list of names such as Ram, Shyam, Aravind, and Shrey and we want them in ascending order. We can achieve the same using Sort in Excel, the result would be Aravind, Ram, Shrey, and Shyam i.e. a sorted list. Sorting can be done for a single column or multiple columns. Moreover, you can sort data based on a single criterion or multiple criteria. Sorting helps to analyze and detect duplicate values.

How to sort in excel?

Below are the two most common ways to sort data in excel. One from the Home Tab and other from the Data Tab.

1.Click on Home Tab >>Sort & Filter.

sort & filter option in home tab

2. Under Data Tab>>Sort & Filter

ascending order and descending order features in sort filter

The A to Z represents sorting in Ascending order and Z to A represents sorting in Descending order.

Now let us understand the different types of sorting.

Sorting Based on Criteria

Single Criteria Sorting-

This is the normal type of sorting, i.e. based on one criterion. In simple terms, the data is sorted based on one condition. Remember while sorting in ascending order numbers come first and then letters.

Let’s look at an example. Considering the below data, we wish to sort the data based on the Name in alphabetical order.

single criteria sorting example

Step 1 – Select any cell in the Name column.

Step 2– Go to Data tab in the ribbon>>Sort AZ .

sorting az from data tab

Below is an image showing the sorted data. We have highlighted the Name column for ease of identification.

sorted data output

Now, what if we wish to sort the data based on some other field/criteria. Say for the example Date and not Name. Simply select any cell in the Date column and then click on AZ or ZA ↓. Now your data will be sorted based on Date.

In the above example, we sorted the data based on one criterion. What if we wish to sort the data based on more than one condition? Let’s understand how can we achieve it using Multiple Criteria Sorting.

Multiple Criteria Sorting-

As the name suggests, our data is sorted based on multiple criteria.

Consider the same data set as above. Now let’s sort the data by Date and then by the Amount. Follow the steps as below:

Step 1– Select the data and then click on Data Tab>> Sort.

sort option in data tab

On clicking Sort, a dialogue box appears as below.

putting value in dialogue box

Step 2– Under Sort by Select ‘Date’ and under Sort On select Cell Values. Then under Order select Oldest to Newest.

  1. Sort by: Based on which field you want to sort. In this case, we firstly want to sort the data by Date.
  2. Sort On: Based on what are we going to sort. There are various options like Cell Colour, Font Colour, etc but for our example, we select Cell Values.
  3. Order: There are various options like A to Z or Z to A for text, Smallest to Largest or Largest to Smallest for numbers, Oldest to Newest or Newest to Oldest for a date. For this example, we select the Oldest to Newest.

Step 3 – Click on Add Level to apply the next criteria. Now select Amount under Sort by, Cell Values under Sort On and lastly Smallest to Largest in the Order.

applying criteria in dialogue box

Step 4– Click OK.

Below is the result. Firstly the data is sorted based on ‘Date’ in ascending order and then by the ‘Amount’ in smallest to largest order.

multiple criteria sorting result

The first criteria entered is called the primary criteria. In the above example, Date is the primary criteria followed by Amount. The data is first sorted based on primary criteria followed by other criteria in sequence.

Sort by column (top to bottom)

In all the above examples we had sorted our data in column order. Let’s look at an example. Considering the same data set as above, follow the steps to sort by column.

Step 1– Select the data and then click on Data Tab>> Sort.

Step 2– Select the Options as shown in the below image.

options tab in sort tab

On clicking the Options button we get the following pop up.

sort options dialogue box

Notice by default the sorting was done based on column (top to bottom).

Step 3– Under Sort by select ‘Amount’ and under Sort On select Cell Values. Then under order select Smallest to Largest. In summary, we are sorting the data based on Amount in ascending order.

Bellow image shows the sorted data. We have highlighted using a colour.

top to bottom sorted data

Sort by Row (Left to right)

Consider the below dataset. Let’s sort the data based on 11th row from Smallest to Largest.

sort left to right

Step 1– Select any cell within the data and click on Data>> Sort.

Step 2 – Go to Options>> Sort left to right.

sort options dialogue box left to right

Step 3– Under Sort by select the row you want to sort. In our case, we select the 11th row.

Step 4– Under Sort On select Cell Values.

Step 5– Under Order of sorting, select Smallest to Largest.

Sort dialogue box, row, sort on, order

Step 6 – Click OK.

Below image shows the result. We have highlighted the 11th row for easier understanding.

left to right sorted data result

Case sensitive-When you click on Options, Case sensitive option is displayed on the top.

case sensitive option in sort options

If your data has both upper and lower case, you need to be careful while sorting. If you want the data to be sorted based on case sensitivity, then you can select the case sensitive box. For example, if you have one item which u write both in upper and lower cases such as ‘Cash’ and ‘cash’. Then it will sort the item with a Lower Case first and then the item with Upper Case will appear next.

My data has headers

sort data has headers

The dialog box displays ‘My data has headers’ in the rightmost corner. If our table as headers than we can select the checkbox or else if there are no headers you can uncheck the box. What happens if your table has headers and you choose not to checkmark the box? This will sort your heading along with your data.

Let us understand the SORT ON options in detail. Why so? Because it will help us sort based on Cell Colour, Font Colour, Conditional Formatting Icon and more.

The Sort On has the following options:

  1. Cell Values– Sorts based on the values in the cell.
  2. Cell Colour– Sorts based on colour preferences.
  3. Font Colour– Sorts based on font colour.
  4. Conditional Formation Icon– Sorts based on conditions provided.
Sort on values

Sort based on Cell Values

Let us consider the following table and let’s say we want to sort the data based on Cell Values. What this means is the values in the cell are considered for sorting.

sort based on cell value

Sorting by Cell Value facilitates sorting a list of texts, numbers, or a date-time format. You can choose to sort the Cell Values either in ascending or descending order or based on custom lists.

Sort based on Cell Colour

Below is a data set with one column having coloured cells i.e. the Payment Method column.

Sort based on Cell Colour

Follow the steps to Sort based on Colour.

Step 1– Go to Data Tab>>Sort option.

Step 2– Under Sort by select Payment Method and under Sort On select Cell Colour.

Step 3– A new tab will appear towards the right. This allows you to select the first colour you want to retrieve based on the sorting. After which you can select whether the colour is to be on top or at the bottom.

Step 4– You can continue adding more levels deciding the order of colour based upon which the data will be sorted.

sort value by cell colour

Step 5– Click OK. And the data will be sorted based on Cell Colour.

sort results based on cell colours

Sort based On Font Colour

Below is a data set in which the text colour is different under the Payment Method. To sort the data based on the Font Colour follow the steps as below.

sort based on font colour

Step 1– Go to Data Tab>>Sort option.

Step 2– Under Sort by select the column. In this case, we select the Payment Method as the text under it has coloured font.

Step 3– In the dialogue box add the preference of the font colour you want.

dialogue box preference of font colour

Step 4– And click OK. Your data will be sorted based upon the Font Colour.

result based on sorted font colour

Sort based on Conditional Formatting Icon

Many a time datasets consist of icons produced by Conditional Formatting in Excel. You can sort the data based on these icons in a specific order.

The following data consists of icons and we wish to sort them in the order of red circle, orange circle and finally green.

sort based on conditional formatting icon

Step 1– Go to Data Tab>> Sort .

Step 2– A dialog box with multiple options will appear. Under Sort by select Icons and under Sort On select Conditional Formatting Icon. Now under the Order select the Icon based on your priority. In this case, we want the red circle to show first, so we select it and next to it select the option On top.

Step 3– Then you can add another level and mention the next Icon you want to sort. As per our example select the Yellow/orange and lastly Add another Level and let that be green.

add level in sort box

Click OK, the result will be displayed as:

result of sorted conditional formating icon

Sort Function and SortBy Function

Note: These functions are available only in Microsoft 365 and above.

Sort Function

The Sort Function sorts the data in a range or array. The result is a dynamic array of values.

Let’s look at an example to understand better. Below is our data.

sort function data in array

In this example, we want to sort the Name in ascending order. The steps are as follows:

  1. Type =SORT(
  2. Then select the range we want to sort. In our example, we will select the range as B8: B17 which is the Name column.
  3. Hit Enter and the data will be sorted in ascending order.
data sorted in ascending order

NOTE: If any changes are made to the source list after sorting, the same will reflect in the array of values.

SortBy Function

The SortBy Function allows you to sort the data based on the values in a corresponding array or range.

Let’s understand with an example. Consider the below data which we wish to first Sort by the ‘Date’ followed by ‘Amount’.

sort the data based on the values
  1. Type =SORTBY(
  2. The first argument is selecting the entire array or the data. In our case, it is B8: E16
  3. The next argument is to select the array or range to sort on. In this example, we wish to sort the Date first, so we select the range B8: B16
  4. The next argument is the sort order denoted by 1 or -1 for ascending or descending order.
  5. Similarly, we add the data range E8: E16 and sort it in ascending order by writing 1.
  6. Similarly, we can add more arguments.

The output is as below.

sortby function result

Sorting in Power Query

To sort the data in Power Query, firstly load the data into Power Query Editor.

Let look at an example. The below image shows data in the Power Query Editor and we will be sorting the Customer Name column in Descending order.

sorting in power query

To the right of the Name column, there is a dropdown box. Click on it and you will get various options. From them, you can select Sort Ascending or Sort Descending as per requirement.

Sort Ascending or Sort Descending

In our example, we select Descending. Below is the output.

power query result

To clear sort, you can click on ‘Clear Sort’ option from the drop down.

Sorting a Pivot Table

After creating a Pivot Table let’s say we want to sort the data in ascending order. You can find a dropdown box in the right corner of each column. If you click on the dropdown box you will be able to find the ‘Sort option’. You can select the basis you want to sort on as ascending or descending. To know more about the sorting option click here.

To get more insights on MS Excel, Subscribe to our Youtube channel.

Related Posts

Write A Comment