Sorting data is a crucial part of data analysis. Whether you want to arrange a list of names in alphabetical order, compile a list of products from highest to lowest, or even order rows or columns by color, you can do all of these in Excel.
Sorting helps you quickly visualize and analyze complex datasets. Excel makes your sorting task easier with built-in features and formulas.
That being said,
If you want to improve your data analysis capabilities using Excel, you have come to the right place.
This is a quick guide about how to sort by number in Excel. Make sure to read the article until the end, as it includes useful functions and tools that will help you in the future.
Here is a list of methods you can use to sort data numerically. Click on any click to jump to that particular section.
- Sort by Number using Filters
- Sort by Number using the SORT function
- Sort by Number using the Sort Tool
Download the Example Excel Sheet
Consider downloading the following Excel sheet to practice the different methods discussed in this article. It will help you better understand the formulas and functions discussed in the following sections.
It is okay if you have your own Excel sheet ready to sort data by numbers; skip downloading the above file.
Now, without any further ado, let’s start.
How to Sort by Number in Excel using Filters
It is the easiest and quickest of the two other methods discussed in the following sections.
We are all aware of the filters in Excel. They allow us to view and analyze datasets quickly and more effectively. They can also be used to sort data numerically.
Consider the following example where we have employee details, including their full name, department, and salary.
Task: To sort the above-shown data by the annual salary of the employees in ascending order.
Note that the table contains records for 20 employees. We need to sort the table by annual salary, making sure that the employee with the highest salary appears at the top.
Here are the steps,
- Open the desired Excel sheet
- Click on any cell of the table
- Navigate to the main menu and click on the “Data” tab
- Click on the “Filter” icon as shown below
- You can see a dropdown appearing beside each column header of the table
- Click on the dropdown beside the “Annual Salary” header to see the list of sorting options available in Excel
- Choose the first option, “Sort A to Z” to sort the annual salary of employees in ascending order
Excel quickly sorts the entire table in ascending order, as shown in the above GIF. To sort the annual salary in descending order, you can click on the “Sort Z to A” option.
SIDENOTE: Filters are the best option for quickly sorting your data, whether by alphabet or color.
How to Sort by Number in Excel using the SORT function
The SORT function in Excel sorts the data table or array in ascending or descending order. It also offers the flexibility to sort by multiple columns.
It returns a dynamic array of results, which updates automatically when you add a new entry or delete the existing one.
The General Syntax of the SORT function is as follows,
=SORT(array, [sort_index], [sort_order], [by_col])
Here is how to deal with the different arguments of the SORT function,
- “array” – It represents the range of data that is to be sorted.
- “sort_index” – Here, you need to enter the column number that is to be sorted from the array defined in the previous argument
- “sort_order” – This argument lets you decide the sorting order. Enter “1” to sort the array in ascending order and “-1” to sort it in descending order
- “by_col” – It is an optional argument that is used to indicate the direction of sorting. You need to enter “FALSE” to sort by row and “TRUE” to sort by column.
As you may have noticed, the SORT function is easy to use and gets the job done quickly. But make sure you replace the arguments with the proper information.
Now, let’s see how we can use the SORT function to sort by Number.
Note that we are referring to the same task and table we used in the first method.
- Open the desired Excel sheet
- Create a new table to populate the sorted dataset
(Make sure that the headers are similar to the source table) - Select the first cell right below the first header of the newly created table
- Type “=SORT”
- Choose the first option from the popup or press “Tab” on your keyboard
- Select the entire table range, but make sure you do not include the headers, as shown below
- Press “,” on your keyboard to enter the next argument
- The third column contains the “Annual Salary” which needs to be sorted. So, put “3” in the place of the sort_index argument
- Press “,” again
- Since we are planning to sort the data in ascending order, enter “1”
- End the formula with a closing parenthesis “)”
- Press “Enter” to see the result
This will create a new dynamic table, as shown in the GIF above.
As the SORT function is an array formula, do not edit or add any new record to the dynamic table because it will instantly make your dynamic table disappear. Whatever data you wish to add to the table, add it to the first or source table. New entries will automatically reflect in the dynamic table.
How to Sort by Number in Excel using the Sort Tool
This is the last option for sorting the data in Excel. If you do not wish to create a separate dynamic table, as discussed in method 2, you can use this built-in tool.
Here are the steps,
- Open the desired Excel spreadsheet
- Select the entire table and click on the “Data” tab from the main menu
- Now, select the “Sort” option
- A Sort dialog box will appear on the screen, as shown below
- If your dataset has headers, make sure “My data has headers” is selected in the dialog box
- In the Sort dialog box, choose the column “Annual Salary” as shown below
- Make sure the “Cell Values” is selected under “Sort On” dropdown
- Next, in the ”Order” dropdown, select “A to Z” to sort the data in ascending order
- Click on “OK” to see the result
You can use the same method to sort the dataset by multiple columns or criteria. All you need to do is add a new level, as shown in the following image.
Conclusion
That’s it. You have made it to the end.
I hope this article helped you learn how to sort by Number in Excel. It should greatly enhance your ability to analyze trends and help you make informed decisions based on your dataset.
But still, if you have any doubts or are stuck somewhere while using any of the methods discussed above to sort the dataset by numbers, feel free to comment below. I look forward to answering all your questions as soon as possible.
Also, make sure to refer to our blog section for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.