Sorting Data in Excel helps streamline workflow and improve efficiency. There are a couple of tools to sort massive datasets in Excel. You can use the SORT function to create a dynamic table which will be automatically sorted every time you change the source data.
We are all aware of the Sort & Filter tool in Excel.
It is helpful in sorting the data manually. But what if your data table changes continuously?
In that case, you need to use the SORT function which creates a dynamic table that auto-updates whenever you add or delete new records.
Let’s learn how to automatically sort data in Excel automatically. We will discuss sorting in both ascending and descending order. Make sure you read the article to the end.
Explained: The SORT Function
The SORT function in Excel sorts a table range or array in ascending or descending order. It returns a dynamic array of results that updates automatically when new data is added or deleted.
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
- “array” – It needs to be replaced with the table range that you wish to sort
- “sort_index” – Here, you need to provide the column number where you wish to apply sorting
- “sort_order” – It allows you to decide the sorting order. Enter “1” to sort the column in ascending order and “-1” to sort it in descending order
- “by_col” – This is an optional argument used to indicate the direction of sorting. You need to enter “FALSE” to sort by row and “TRUE” to sort by column. The default value for this argument is “TRUE”
You can use this function with others to create complex formulas.
Download the Example Excel Sheet
Before we discuss the steps to automatically sort data, download the following Excel spreadsheet, which contains the table used in the upcoming sections.
Note that practice is the key to success. It will strengthen your complex dataset-sorting skills in Microsoft Excel and help you quickly organize them.
If you have your dataset ready, then skip downloading the above file.
How do you automatically sort data in Excel using the SORT function?
Two functions allow you to create dynamic tables that are automatically sorted whenever new rows or columns are added to the source table.
- SORT
- SORTBY
The first one lets you sort by a single column, and the second one allows you to sort the table by multiple columns.
In the following section, let us learn to use the SORT function to sort data from the given table range dynamically.
Step #1- Create a New Table
It all starts with creating a new table where we will insert the dynamic array created by the SORT function.
This way, you have two tables. The first one acts as the source data where you add or remove columns and rows. The second one will be automatically sorted for quick analysis and other purposes.
Consider the following example, where we have employee names and their department in columns A and B.
Here’s how the new table should look,
Make sure the cells of the newly created table are empty. Otherwise, Excel will return an error, preventing the SORT function from operating smoothly.
Step #2- Sort the Table Using the SORT Function
This is the crucial step in which we will insert the SORT function in the newly created table.
Let’s begin,
Click on the first cell of the newly created table
- Type “=SORT”
- Select the first option from the popup or press the “Tab” on your keyboard
- Replace the array argument with the cell reference “A3:B20”
(Make sure you exclude the headers while selecting the table range) - Now, press “,” on your keyboard to move to the next argument
- Put “2” as we wish to sort the second column of the table
- Press “,” on your keyboard to move to the next argument
- Next, let’s put “1” in the place of sort_order argument to sort the data in ascending order
- Close the parenthesis using “)” on your keyboard
- Press the “Enter” button
The final formula would be as follows,
=SORT(A3:B20,2,1)A dynamic table is instantly created, as shown in the GIF above.
PRECAUTION: The most common mistake while using the SORT function is not keeping the new table empty. If any cell of the new table contains text or numerical values, the SORT function will return a “#SPILL!” error. Refer to the following image.
How does automatic sorting work using the SORT function?
In this section, we will see how newly added data is automatically fetched and sorted using the SORT function.
Let’s consider the following example,
Here’s how the dynamic table looks using the SORT function.
Now, refer to the following GIF where we have added an entry at the end of the source table.
The newly added employee, John Jacobs, from the IT department, will quickly move to the middle of the table where all IT employees are sorted.
My Automatic Sorting Didn’t work!
Sometimes, the automatic sorting won’t work as expected when you try to add, remove, or update any record in the existing table.
It is mainly due to the selected range (such as “A3:B20”) being used in place of the array argument of the SORT function. Make sure you select the entire column instead of a limited number of rows from the table’s column.
You can use the keyboard shortcut “CTRL + SHIFT + Down Arrow” twice to select the entire column.
To Summarize: How To Automatically Sort Data In Excel
The SORT is the ultimate tool for sorting the data accurately and ensuring automatic updates with respect to changes made in the future.
If you are stuck somewhere or still have any doubts, feel free to comment below.
Also, make sure you explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.