# 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 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)

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) 