The SORT function in Excel is one of the most powerful array formulas. It offers numerous advantages, such as improved organization, analysis, and management. It is a key skill for anyone working with large datasets in Excel.
To use this formula, you can select any cell and start typing “=SORT”. The function allows you to sort the data by a single column in ascending or descending Order.
Another good news is that you can sort your workbook alphabetically, numerically, and even by color to organize and visualize data like a pro.
This is a quick guide to the SORT function in Excel. Using different examples, let’s learn the SORT function syntax and applications for vertical and horizontal sorting.
Whether you are a beginner or an advanced user, this article is all you need to master the SORT function. So, hold tight and read the article till the end.
Download the Example Excel
You can download the following example spreadsheet, which contains the datasets used in this article to demonstrate the SORT function.
Follow me along and practice the examples discussed below. If you have your own Excel sheet ready, skip downloading the above file.
Now, without any further ado, let’s begin.
Why use the SORT function?
The SORT function in Excel is a powerful tool for organizing huge datasets. It helps you sort your data in ascending and descending orders.
The SORT function updates automatically whenever the table data changes. This means your list will always be sorted correctly, and you don’t have to worry whenever a new entry is added.
Note that the function creates a new table, which is fully automated and changes based on the changes made to the source table.
Hmmm… But do I need a separate table?
Imagine you’ve sorted a table using the Sort tool. If you change a few cells or add new rows to the table, the sorting will instantly break, and everything will be messed up.
Now, consider a dynamic table created using the SORT function, which is capable of retaining the sorting order whenever new data is added to the source table.
Here is an example of how the changes made to the source table are instantly reflected in the newly created dynamic table.
Simply put, the SORT function’s dynamic table feature eliminates the need to sort the dataset each time the table data is altered.
Explained: The SORT Function In Excel
The SORT function in Excel sorts the contents of a range or array in ascending or descending Order. It returns a dynamic array of results that updates automatically when anything new is added or deleted to the original dataset.
The General Syntax of the SORT function is as follows,
=SORT(array, [sort_index], [sort_order], [by_col])
Let’s understand each argument of the formula in the following section
“range”
This argument represents the range or array of datasets that need to be sorted. You can enter either the column range or the entire table here.
To sort a single column, as shown in the screenshot below, you can use the formula
=SORT(A1:A)
“sort_index”
This argument plays a major role when you have a table containing multiple columns. Sort_index is the number representing the position of the column you want to sort.
For example, consider the table shown below where we want to sort the “Department” column.
As the “Department” column is in the first position, we need to replace the “sort_index” argument with column number “1”.
“Sort_order”
This argument decides the Order of sorting for the specified column. You can sort the column in ascending or descending order.
Here are the two values you can use in place of this argument:
- “1” – The function will sort the data in ascending order
- “-1” – The function will sort the data in descending Order
Note that if you don’t provide an input value for the “sort_order” argument, Excel will sort the data in ascending Order by default.
“by_col”
This 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.
How to access the SORT function in Excel
The SORT function in Excel can be accessed using two methods. One is by entering it manually, and the other is by inserting the function from the main menu.
In the first method, you can select the desired cell and start typing the formula followed by the “=” symbol.
But let’s have a look at how to insert the function from the main menu.
Here are the steps,
- Select any desired cell
- Go to the main menu
- Click on the “Formulas” tab
- Choose the “Insert formula” option from the list
- An “Insert function” window will be opened, as shown in the following image,
- Select the formula that you want to insert from the available options or search for it in the Search bar at the top
- Once you select the formula, you will be prompted to enter the argument values also, as shown below,
This method requires you to follow multiple steps compared to selecting the cell and typing the formula. The first one gets the job done quickly so that we will use that method throughout the article.
How to Sort Data in Excel using the SORT Function
Now, let us learn to use the SORT function using an example dataset. The steps are pretty straightforward, thanks to the function’s simple syntax.
Here’s the table we need to sort,
It contains company department details, including employee names and city. Our task is to sort the entire data range by the department.
Let’s begin,
STEP #1 – Create A New Table
The first thing to do while using the SORT function is to create a table in the same spreadsheet. The new table should have the same or similar headers to avoid confusion.
Here, we have created a new table in the same spreadsheet as shown below,
STEP #2 – Sort data using the SORT Function
It is the main step in which we will use the SORT function to sort the dataset.
Let’s get started,
- Click on the first cell right below the first header, as shown in the following image
- Type “=SORT”
- Choose the first option from the popup or press “Tab” on your keyboard
- Select the entire table in the place of the range argument
(Make sure you exclude the headers while selecting the table, otherwise; the function will include the header while sorting the table) - Press “,” on your keyboard to move to the next argument
- Next, replace the sort_index argument with the column number “1”
(We are sorting the table by Department column, which is in the first position) - Press “,” on your keyboard
- Enter the Order of sorting, either ascending or descending. We are putting the number “1” in the place of the sort_order argument
- Now, to complete the formula, close the parenthesis using the “)” symbol on your keyboard
- Press “Enter” to see the result
Here’s how the final formula looks after following the above steps,
=SORT(A3:C21,1,1)Refer to the above GIF to see how the sorted data gets populated in the new table. The new dynamic table is sorted by Department column in ascending Order.
Remember, the SORT function is an array formula, and the created table is a dynamic one. Once you try to make changes in the sorted dynamic table, the entire table will disappear within no time. So, avoid changing or adding records to the table.
For instance, if we try editing the name in cell F11 from “Alice Tran” to “John Jacobs”, then the whole new table will disappear, as shown below. To undo this, you need to remove the change you have made.
What is people’s most common mistake while using the Sort function?
The most common mistake people make when using the Sort function is not leaving enough empty space for the sorted results.
One of the cells contains the false entry “qwe$”, as shown in the following image.
If any cells have data in the range where the SORT function will output the results, as seen above, this can cause errors and prevent the function from working as expected.
Always make sure the destination area for the sorted data is empty.
The function will instantly return a “#SPILL!” error, as shown in the above image.
How can I Sort Data in Descending Order using the SORT function?
As discussed in the SORT function syntax section, the “sort_order” argument allows you to sort data in ascending or descending Order.
So, to sort the dataset in descending Order, you need to replace the “sort_order” with the input value “-1”.
Considering the example discussed previously, the final formula will become as follows:
=SORT(A3:C14,1,-1)How to Sort Multiple Columns in Excel using the SORT Function
For complex data analysis, you may wish to sort the data from multiple columns.
If you are looking for the quickest option to sort the dataset by multiple columns, then the Sort function is your way to go.
Here are the steps,
STEP #1 – Create A New Table
In contrast to the previous table, which we used to demonstrate sorting a single column using the Sort function, the following table contains numbers in the last column.
Let’s create a new table to populate with the sorted data. To avoid future confusion, keep the headers the same.
Our task is to sort the table by Department first and then by each employee’s age.
STEP #2 – Sort Multiple Columns using the SORT function
Sorting multiple columns using the SORT functions involves a slight change in the ‘sort_index’ and ‘sort_order’ arguments, which consist of an array of integers.
- The “sort_index” contains an array of integers indicating the columns to sort by. You can enter multiple column numbers for sorting.
- The “sort_order” consists of an array of integers specifying the sort order for each column (1 for ascending and “-1” for descending).
Let’s understand this with an example.
- Double-click on the first cell below the header
- Type “=sort”
- Select the first option from the popup or press “Tab” on your keyboard
- For the first argument, range, choose the entire table range and make sure you don’t include headers
- Press “,” on your keyboard to move to the next argument
- Press “{“ on your keyboard
- Enter the column numbers in the Order you want to sort. Here, we wish to sort the first and fourth columns. So, we will enter “1,4”
- Now, to complete the bracket, we will use the “}” on our keyboard
- Press “,” to move to the next argument
- Next, enter the sorting order in the array like {1,1}
- Complete the bracket using “)”
- Press the “Enter” button
Note that the sorting is applied to the Department column first and then to the Age column, as shown above.
How many columns can I sort using the SORT function?
You can sort by up to 64 columns in Microsoft Excel using the SORT function.
You can choose to sort any number of columns alphabetically and numerically in ascending and descending Order.
Can I Sort Data Horizontally in Excel using the SORT function?
Yes, you can sort data horizontally in Excel using the SORT function.
Generally, data is sorted vertically by rows, but the SORT function can also be used to sort data by columns.
We need to use the TRANSPOSE function, which converts the columns into rows and vice versa.
The general syntax is as follows,
=TRANSPOSE(SORT(TRANSPOSE(range,) sort_index, sort_order))We will use the TRANSPOSE function twice here. The first TRANSPOSE would change the rows into columns and tell the SORT function to sort the data, and the second TRANSPOSE function would flip the sorted data in horizontal format.
Most of the time, the dimensions are available across the columns and values across the rows. However, consider the following example, where departments and full names are entered across the first and second rows.
All the values (departments and employee names) are available from column “B” to “H.”
In other words, the data is available horizontally in the above example, and the SORT function is not directly applicable.
STEP #1 – Create A New Table
We can choose to create a new table in the same spreadsheet or a separate spreadsheet.
In our case, we have a short table with only two rows to sort. So, let us create a new table on the same sheet.
Refer to the following image.
Make sure to use similar headers as shown above to avoid future confusion.
STEP #2 – Sort data Horizontally using the SORT function
This is the important step, where we will use the combination of the TRANSPOSE and SORT functions to sort the data horizontally.
Select the first cell beside the first-row header of the new table
- Type “=TRANSPOSE”
- Click on the first option or press “Tab” on your keyboard
- Next, type “SORT”, to select the SORT function
- Press “Tab” on your keyboard or click on the first option
- Again, type “TRANSPOSE” and press “Tab” on your keyboard
- Select the table range and make sure you do not include headers, as shown below
- Close the bracket for the TRANSPOSE function that we used for table range by typing “)”
- Press “,” on your keyboard to move to the next argument of the SORT function
- Put “1” as we plan to sort the Department row
- Press “,” to move to the next argument
- To sort the Department columns in ascending Order, we will type “1”
(Note that you can type “-1” in case you wish to sort the data in descending Order) - Close the bracket for the SORT function using “)”
- Now, we also need to close the bracket for the Transpose function we are using to flip the sorting results by typing “)”
- Press “Enter” on your keyboard
The final formula will be as follows,
=TRANSPOSE(SORT(TRANSPOSE(B1:M2),1,1))Your horizontally sorted table is ready. Overall, the steps are similar and easy to follow if you use the Transpose function without error. Make sure to close the brackets at the end using the “)” twice.
To Summarize: The SORT Function in Excel
The SORT function in Excel is an essential tool for anyone working with large datasets. It helps you organize data quickly and efficiently, whether you’re sorting by single or multiple columns or even sorting data horizontally.
Its dynamic nature helps you keep the sorted data up-to-date, automatically adjusting whenever the source data changes.
Whether you are a beginner or an advanced user, understanding how to use the SORT function will help you handle your data like a pro.
If you are stuck somewhere or still have any doubts, feel free to comment below.
Also, make sure to explore our blog for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.