Excel Sort Formulas – How to Sort Dynamic Data
Excel includes powerful sort functionality in the Sort & Filter group on the Data tab. But what if your data changes frequently, or if you’re simply tired of clicking through the Ribbon each time you generate a similar table? The answer might be to create a formula that does your sorting work for you. As with lots of Excel “tricks” of this nature, however, what you gain in analysis you might lose in convenience. Or, in simplicity at least.
This article looks 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.
Steps below apply to Excel 2007 and later. Images were taken using Excel 2013 on Windows 7 – specific steps may vary based on your version.
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.
Step One: Create a Helper Column to Calculate Relative Rank
To follow using our example, download Excel Sort Formula.xlsx; Sheet Q1
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 tells 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.
Step Two: Reorder the Data based on its rank using INDEX, MATCH, and ROW
As we learned in Excel’s INDEX formula – the Basics, 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))
The Rub
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.
