ASM Arman
Sort button in Excel is a very powerful and useful feature that will let you sort the information in a large Excel worksheet without any hassle. Sort of the data depends on the type of values that are stored in the cells. Some common forms of sorting are Alphabetical sort (A-Z or Z-A), sort by Numerical Values (Ascending or Descending order), or sort by Year, Month, or Date. In this tutorial, I will show you how to add a sort button in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
7 Suitable Methods to How to Add Sort Button in Excel
Let’s assume a scenario where we have an Excel file that contains information about the employees of a company. The worksheet has the Name, Age, Gender, Date of Birth, States they come from, and their ID No. We will add a sort button to sort the information of the employees in several ways. The image below shows the Excel worksheet we are going to work with.
1. Add Level in Sort Option to Sort in Excel
You can add one of the columns in your data as a single level or different columns as multiple levels while sorting the information in an Excel worksheet.
Step 1:
- First, we will select all the cells in our data range including the column headers.
- Then, go to the Data tab and select the Sort option from the Sort & Filter.
- A new window titled Sort will appear. We will check the box just beside the My data has headers.
- We will then click on the Sort by drop-down menu and select the Name column from there.
- The default value for the Sort On drop-down is Cell Values and for A to Z for the Order. We will leave these in their default values. We are sorting the values or names of the employees in the Name column and we will sort the values or names in alphabetically ascending order. That is why we have selected Cell Values for the Sort On drop-down menu and A to Z for the Order drop-down menu.
- Finally, click on OK.
- Now, we will see all the names of the employees in the Name column have been sorted in alphabetically ascending order.
Step 2:
- We can also add multiple levels to sort the data. To do that, we will take a fresh copy of our worksheet or we can delete the existing level by clicking on Delete Level just beside the Add Level.
- We will check the box just beside the My data has headers.
- We will then select the Date of Birth from the Sort by drop-down menu.
- The Date of Birth column is our first level in sorting. So, our rows will be first sorted by the Date of Birth of the employees. Then it will be successively sorted based on the next levels. The Order of sorting for this column will be Oldest to Newest.
- We will then click on the Add Level button again to add the second level for sorting.
- We will select the Gender column from the Then by drop-down menu.
- Finally, we will click on Add Button again and enter Name as the third level to sort the data by.
- We will then click OK to sort the rows.
- We will now see that all of the rows in our data range have been sorted by Date of Birth first, and then they have been sorted based on the Genders of the employees, and finally by the Names of the employees.
Read More: How to Undo Sort in Excel (3 Methods)
2. Create a Custom Sort List in Excel
We can also add a custom sort list to sort the columns in an Excel worksheet. For this example, we will create a custom sort list based on the State column and use it to sort the data.
Step 1:
- First, we will select all the cells in our data range including the column headers.
- Then, go to the Data tab and select the Sort option from the Sort & Filter.
- We will now select the State column from the Sort by drop-down menu.
- Then, we will click on the Order drop-down and select Custom List.
- We will enter the following list of states separated by comma(,). This list will be used to sort the rows based on the States.
- We will then click on OK.
- Now, we will see that a list of states has been created.
- Click on the OK button to confirm the list.
Step 2:
- Now, we will see that the Order drop-down has an additional option containing the list we have just created. We will select the list if it is not selected.
- Finally, we will click on the OK.
- We will now see that all the rows of the data range have been sorted based on the list of states we have created.
Read More: How to Create Custom Sort List in Excel
3. Sort the Data Using the Filter Option
We can also apply the sort from the Filter option. We can do that following the below steps.
Step 1:
- First, we will select all the cells in our data range including the column headers.
- Then, go to the Data tab and select the Filter option from the Sort & Filter.
Step 2:
- We will see small downward arrows on the down-right corner of each column header. Click on the arrow on the Age A new window will appear.
- We will select the Sort Smallest to Largest option from that window.
- We will see that rows in the Age column are sorted in ascending order from lowest to greatest.
Read More: How to Sort and Filter Data in Excel (A Complete Guideline)
4. Sort the Data with the SORT Function in Excel
Excel 365 has a built-in SORT function that you can use to sort the data in a worksheet. For example, we will sort the ages of the employees in descending order using the SORT function in Excel 365. We can do this following the below steps.
Steps:
- First, we will create two columns with column headers Name and Sorted Age like below.
- We will then write the below formula in cell E5.
=SORT(B5:C14,2,-1)- The SORT function takes 3 arguments.
- B5:C14 is the cell range we want to sort.
- 2 indicates the second column or the Age column in the range.
- -1 means we want to sort the data in descending order.
- Finally, upon clicking the ENTER button, we will see that the ages of the employees have been sorted in descending order.
Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)
Similar Readings:
- How to Merge Cells Using Excel Formula (8 Simple Ways)
- VBA to Sort Table in Excel (4 Methods)
- How to Sort by Month in Excel (4 Methods)
- Sort IP Address in Excel (6 Methods)
- Random Sort in Excel (Formulas + VBA)
5. Sort the Data in a Row
Till now, we have sorted the data in a column or multiple columns. But Excel also has the feature to sort the data in a row. Suppose we have the sales volumes of each month from January to May of every employee. We will sort the rows to reorder the total sales volume in ascending order.
Step 1:
- First, we will select all the cells in our data range except the Name.
- Then, go to the Data tab and select the Filter option from the Sort & Filter.
- We will now select the Options from the Sort.
Step 2:
- A new window titled Sort Options will appear. Then, we will select Sort left to right from there.
- Next, we will click OK.
Step 3:
- If we now click on the Sort by drop-down menu, we will see that it is not showing column titles anymore. Rather it is showing Rows. But the rows do not have any title rather they have numbers like Row 4, Row 5,
- As we will sort the Total sales volume which is Row 15, we will select Row 15.
- Then we will click OK.
- Now, we will see that Total sales volumes in the worksheet have been sorted in ascending order.
Read More: How to Sort Rows in Excel (2 Simple Methods)
6. Sort the Data in Column by Cell Icons
We can use Conditional Formatting to insert icons on cells based on their values and then use these icons to sort the cells. For example, we will sort the rows based on the ID No column using this method.
Step 1:
- First, we will select the Conditional Formatting from the Styles section under the Home.
- A drop-down menu will appear. Now, we will select Icon Sets from that list.
- Another list with different sets of shapes will appear. We have selected a set of shapes like the image below.
- The cells in the ID No column now have different types of shapes besides their values based on the range of values.
Step 2:
- We will now sort all the cells with red circles. To do that we will click on such a cell and right-click on that.
- A window will appear. We will select Sort from that window.
- Now, another list will appear which has different options to sort the data. Now, we will select the Put Selected Formatting Icon On Top option from that list.
- We will now see that all the cells with the red circle shapes are now on the top of the column.
Read More: Sorting Columns in Excel While Keeping Rows Together
7. Add Sort Button to Quick Access Toolbar in Excel
If you have to use sort frequently, you can add it to the Quick Access Toolbar. Adding the sort to the Quick Access Toolbar will let you access the sorting facility very easily and quickly.
Steps:
- We will go to the Data tab and then right-click on the Sort. A window will appear. Then we will click on the Add to Quick Access Toolbar from that window.
- Now, we will see that Sort is added to the Quick Access Toolbar.
Related Content: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
Things to Remember
- SORT is an exclusive function only to be used in Microsoft Excel 365. So you will need Excel 365 to use the SORTÂ function.
- You should always check the My Data has headers option except for sorting the rows. This option will be disabled when sorting the rows.
Conclusion
In this article, we have learned how to add a sort button in Excel and sort the data in different ways. I hope from now on you can sort the data in Excel very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
