How to Sort Data in Reports Automatically Using Excel Formulas

You can sort data with formulas in two ways. One way requires Excel 2019 and above. This article explains the other way, which allows you to sort data in all versions of Excel.

113

You can sort data with formulas in two ways. One way requires Excel 2019 and above. This article explains how to sort data in all versions of Excel.Many Excel reports include tables that show sorted results. Usually, these tables were sorted manually in Excel, using the Data, Sort command. However, reports would be a lot easier to maintain and update if formulas (not macros) could sort the data automatically.

There’s a simple way to do this. But to make the method work reliably, you’ve got to fix the behavior of the RANK function.

This figure shows a partial solution, and illustrates the problem with RANK. Columns A, B, and G show actual data. Enter the following formulas for the cells shown, and copy the formulas down their columns as needed:

C3: =RANK(A3,$A$3:$A$6,0)
E3: =MATCH(G3,$C$3:$C$6,0)
H3: =INDEX($B$3:$B$6,E3)
I3: =INDEX($A$3:$A$6,E3)

Example of a failed sort in Excel
Sorting Excel data with formulas without fixing the RANK function.

The formulas in column C rank the sales values, where the largest value is ranked 1 and the smallest in the table is ranked 4.

The easiest way to understand column E is with an example. Look at cell G6. This marks the fourth sorted row in the table at the right. Cell E6 tells us that the values for this row can be found in the second row of the table at its left. The other values in column E provide similar information.

Columns H and I use the information in column E to return the appropriate values.

The reason row 4 of the spreadsheet has #N/A values is because the RANK function assigns identical rankings to identical values. This is a problem because Coats and Pants have identical sales this month. So the RANK function assigns the same #1 ranking to both products in column C, and assigns no #2 rank value at all. Therefore, when the formula in cell E4 looks for a #2 ranking, it returns #N/A.

This problem is easy to fix, however. Just insert a new column for an adjusted sales value and then rank those adjusted values. Here are the formulas for the cells shown:

B3: =A3+0.000001*ROW()
D3: =RANK(B3,$B$3:$B$6,0)

Sorting Excel data with formulas after fixing the RANK function.

Column B adds a tiny amount to each Sales value, an amount based on the number of each row in the table. This forces each value in Sales2 to be unique, which forces the Rank values in column D to be unique. And this gives us our automatically sorted values in columns H:J.

When you use this technique, be sure to estimate the maximum number of rows in your table when you choose the decimal fraction shown in the formula for cell B3. That is, if you’re going to have hundreds of thousands of rows of data (which New Excel allows) make sure that you still have a small number when you multiply your decimal fraction by a row number of about one million.