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!
- 1 Sort in MS Excel
- 2 How to sort in excel?
- 3 Sorting Based on Criteria
- 4 Sort Function and SortBy Function
- 5 Sorting in Power Query
- 6 Sorting a Pivot Table
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.
2. Under Data Tab>>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.
Step 1 – Select any cell in the Name column.
Step 2– Go to Data tab in the ribbon>>Sort AZ ↓.
Below is an image showing the sorted data. We have highlighted the Name column for ease of identification.
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.
On clicking Sort, a dialogue box appears as below.
Step 2– Under Sort by Select ‘Date’ and under Sort On select Cell Values. Then under Order select Oldest to Newest.
- Sort by: Based on which field you want to sort. In this case, we firstly want to sort the data by Date.
- 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.
- 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.
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.
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.
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.
On clicking the Options button we get the following pop up.
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.
Sort by Row (Left to right)
Consider the below dataset. Let’s sort the data based on 11th row from Smallest to Largest.
Step 1– Select any cell within the data and click on Data>> Sort.
Step 2 – Go to Options>> Sort 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.
Step 6 – Click OK.
Below image shows the result. We have highlighted the 11th row for easier understanding.
Case sensitive-When you click on Options, Case sensitive option is displayed on the top.
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
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:
- Cell Values– Sorts based on the values in the cell.
- Cell Colour– Sorts based on colour preferences.
- Font Colour– Sorts based on font colour.
- Conditional Formation Icon– Sorts based on conditions provided.
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.
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.
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.
Step 5– Click OK. And the data will be sorted based on Cell Colour.
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.
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.
Step 4– And click OK. Your data will be sorted based upon the 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.
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.
Click OK, the result will be displayed as:
Sort Function and SortBy Function
Note: These functions are available only in Microsoft 365 and above.
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.
In this example, we want to sort the Name in ascending order. The steps are as follows:
- Type =SORT(
- Then select the range we want to sort. In our example, we will select the range as B8: B17 which is the Name column.
- Hit Enter and the data will be 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.
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’.
- Type =SORTBY(
- The first argument is selecting the entire array or the data. In our case, it is B8: E16
- 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
- The next argument is the sort order denoted by 1 or -1 for ascending or descending order.
- Similarly, we add the data range E8: E16 and sort it in ascending order by writing 1.
- Similarly, we can add more arguments.
The output is as below.
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.
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.
In our example, we select Descending. Below is the output.
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.