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

4848 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) 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. Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,