Excel Sort Formulas – How to Sort Dynamic Data

Key Takeaways 

• The Excel SORT function (available in Microsoft 365 and Excel 2021) lets you create a dynamic sort that automatically updates when your data changes. 
• For older Excel versions, you can build an excel sort formula using COUNTIF, INDEX, MATCH and ROWS to rank and reorder data. 
• Manual sorting and filtering work for quick, one-time analysis, but formula-based sorting is better for data that changes frequently. 
• Understanding common errors (#NAME, #SPILL, #VALUE) helps you troubleshoot when your sort formula isn't working. 

What Is the Excel SORT Function 

The SORT function is a dynamic array function that returns a sorted version of a range or array. Unlike manual sorting, it doesn't alter your original data. Instead, it outputs a new sorted array in a separate location and updates automatically whenever the source data changes. This makes it the most efficient way to sort data in Excel using a formula. 

Key characteristics of the SORT function: 

• It is a dynamic array function, meaning results automatically spill into adjacent cells 
• It is non-destructive and leaves your original data untouched 
• It supports both ascending and descending sort order 
• It can sort by rows or by columns 

If you're using an older version of Excel that doesn't support the SORT function, this article also covers a legacy formula approach using COUNTIF, INDEX, MATCH and ROWS. 

SORT Function Syntax 

The syntax for the SORT function is: 

• =SORT(array, [sort_index], [sort_order], [by_col]) 

For example, =SORT(A2:D11, 4, -1) sorts the range A2:D11 by the fourth column in descending order. 

Which Excel Versions Support the SORT Function 

The SORT function is not available in every version of Excel. Before building your formula, confirm your version supports it. 

Supported versions: 

• Microsoft 365 (Windows and Mac) 
• Excel 2021 
• Excel for the web 

Unsupported versions: 

• Excel 2019 
• Excel 2016 and earlier 

If you're working in an unsupported version, skip ahead to the Sort Dynamic Data section below, which walks through a workaround formula using COUNTIF, INDEX and MATCH. 

Apply a Normal Data Sort or Filter to Your Sheet

To quickly sort or filter your data for one-time analysis, use Excel's built-in sort and filter tools: 

• Click any cell in the column you want to filter.
• Add filters to your data. You can do this a couple different ways.
• On the Home tab, click the Sort & Filter button in the Editing group
• – OR –
• On the Data tab, click the Filter button in the Sort & Filter group

• Click the dropdown arrows that appear at the top of each column to reveal sorting and filtering choices for that column of data.

For example, if you uncheck the Select All box and check the box(es) for the 1123 Store number in the Store column, you can see only those sales that came from that store.

• Review your selection. A filter icon appears by the dropdown arrows that are currently being filtered. A sort arrow appears in the column that has been sorted.
• Click the Filter button again (see step 2) to turn filters off, or select Clear Filter From “…” in the column filter dropdown menu (see step 3). Note that the rows will remain in the order of the last sort, even after filter arrows have been removed.

This can provide you many ways to sort and view your data to help you make sense out of it. You can also apply conditional formatting to visually highlight key values as you sort and filter. But what if your data changes frequently? 

Sort Dynamic Data

If you don't have access to the SORT function, you can build a formula using COUNTIF, INDEX, MATCH and ROWS that sorts your data automatically. As with lots of Excel tips and tricks of this nature, what you gain in analysis you might lose in convenience. Or, in simplicity at least. 

Let’s look at one way to sort dynamic information and then explore the limitations of the results!

Our example will allow us to take a table of sales figures and generate a sorted list that shows us our top performers for the quarter. We could sort our report by Quarter Totals (Column E), but then the rest of our year would be out of order. We could only see sales ranking one quarter at a time. Because we receive our source data alphabetically by salesperson, we would also have to re-sort by salesperson each time we add another quarter.

This is what our table might look like as the year progresses…

Instead, we will use the COUNTIF, INDEX, MATCH, and ROWS functions to create a formula that will sort our sales figures and display them in a new table. This takes two steps.

Create a Helper Column to Calculate Relative Rank

We will first generate a 1-10 numerical ranking of the information we want sorted. This will not "rearrange" the information, but tell us its place in the list. The formula that goes in G2 is:

=COUNTIF(E$2:E$11,"<="&$E2)

• =COUNTIF() tells Excel to count the items in a range and under what conditions they are to be counted.
• E$2:E$11 describes the range we want counted.
• "<="&$E2 tell excel to check the relative rank of the value compared to the entire range.

Enter the formula, then drag to copy it into each cell in the column. The result should be a number from 1-10 in each row.

Reorder the Data based on its rank using INDEX, MATCH, and ROW

As we learned in Using the INDEX function in an Excel Formula, INDEX tells Excel to get a value in a specific row and column. We will use it here to help us match the correct rank with the correct sales total, and then return the list in ascending order. The sort formula we will put in H2 is:

=INDEX($E$2:$E$11,MATCH(ROWS($G$2:G2),$G$2:$G$11,0))

• =INDEX() tells excel to return information from a certain cell and column
• $E$2:$E$11 specifies the range to look in
• MATCH(ROWS($G$2:G2),$G$2:$G$11,0 – this function tells excel which row to look in
• MATCH() tells excel to choose the row the INDEX will use based on the value in the helper column
• ROWS($G$2:G2) tells the MATCH function what rank in the list should be matched by "counting down" from the first cell in the series. Note that the 2nd "G2" is not absolute, meaning it will change with the row when the formula is dragged and copied.

If we want to also display the names of the salespeople next to the sorted sales totals, we would simply add the same formula to the next column, but change the INDEX function to return values from the Salesperson column:

=INDEX($A$2:$A$11,MATCH(ROWS($G$2:G2),$G$2:$G$11,0))

Limitations of This Formula Approach 

Before we get too excited about this method for sorting data using an Excel function, there's a catch. Sadly, these formulas will NOT work if: 

• There are any duplicates in the data 
• There are any blank cells in the data 
• You have a mix of numbers and text. You can sort ALL text or ALL numbers, but a mixture of the two will generate errors. 

But, if your data is rarely likely to generate duplicates and is very consistent such as the above example, this technique can deliver a lot of value. If your data is more complex in the above ways, you will at least have a starting place to build a sort formula that can work with the exceptions. 

SORT vs. SORTBY: When to Use Each 

If you have access to Excel 365 or Excel 2021, you have two powerful dynamic array functions for sorting: SORT and SORTBY. They serve different purposes. 

The SORT function sorts an array by a column (or row) within that same array. You specify the column number to sort by. This works well when the sort key is part of the data you want returned. 

The SORTBY function sorts an array based on a separate range or array. Its syntax is: 

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...) 

Key differences between the two: 

• SORT references a column index number within the array. SORTBY references an external range, which can be outside the output array entirely. 
• SORTBY handles multi-level sorting more cleanly. You can add multiple by_array/sort_order pairs to sort by two or three columns in a single formula. 
• SORT is simpler for basic single-column sorts. SORTBY is preferable when sorting by a column you don't want included in the output or when you need to sort by multiple criteria. 

For example, if you have employee names in column A and performance scores in column B, and you only want to return the sorted names, =SORTBY(A2:A11, B2:B11, -1) returns names sorted by score in descending order without displaying the scores. 

Troubleshooting Common Excel Sort Formula Errors 

If your Excel sort formula isn't working, check for these common issues: 

• #NAME error: Your Excel version doesn't support the SORT or SORTBY function. This happens in Excel 2019 and earlier. Use the legacy COUNTIF/INDEX/MATCH method described in the Sort Dynamic Data section above. 
• #SPILL error: The cells where your formula needs to output results are not empty. Clear all cells below and beside your formula so the dynamic array has room to spill. 
• #VALUE error: One of the arguments in your formula is invalid. Common causes include entering text where a number is expected in the sort_index or sort_order arguments. 
• #REF error: The source data range has been deleted, the workbook containing the referenced data is closed or a structural change broke the reference. 
• Legacy formula errors (COUNTIF/INDEX/MATCH method): Duplicates in the helper column, blank cells in the data range or a mix of numbers and text will all cause incorrect results or errors. Refer to the Limitations of This Formula Approach section for details. 

Double-check your cell references and make sure you're using absolute references (with the $ symbol) where needed so your formulas don't break when copied across rows. 

Build Your Excel Skills with Pryor Learning 

Formula-based sorting is a powerful skill for anyone who works with data that changes regularly. Whether you're using the SORT function in Excel 365 or building legacy formulas in an older version, mastering these techniques saves time and reduces errors in your daily workflow. 

Pryor Learning offers hands-on Excel training courses designed for every skill level, from spreadsheet basics to advanced formulas and data analysis. Explore a PryorPlus subscription for unlimited access to On-Demand courses, or join a live seminar for guided, interactive learning.