How To Sort Data on Excel using Formula (No Macro, No Click A-Z Button )

sort data automatically excel

Most users doing sort data in excel using sort button (A-Z button). But, is it possible to sort data on excel automatically using formula? without click sort button? and the table will be refreshed automatically? Yes, you can do it!. At the beginning I am confused whether I can do it or can’t. Which excel formula does it take? Even I can’t do it when I use RANK formula. Because it will give me duplicate data if there are some people that have same value. But finally, I can make it!. Keep reading.

I tried search for the answer on google, but I didn’t find it. Most of the results are I have to click sort button to sort the data. But I got some useful formulas to combine. I can create auto sort table in excel using 5 formulas, these are LARGE, ROW, INDEX, MATCH and INDIRECT. I combine those formulas in a cell to get unique row position without repeats the data that contains same score. Unlike RANK that will give you duplicate rank position if there are people that have same score.

Notice: I use same sample table with this article 6 Excel Basic Formulas You Should Know. I have a table about people and score contains 10 rows.

This is a sample table
table excel

You can follow these steps below to sort table in excel automatically. But before continuing, you have to create new table beside it and add a column for reference (you can hide it after entering those formulas).

Sort the Score

You have to use LARGE formula to sort the score from the largest to the smallest. If you use the sample table, you can type formula below, in red marked area, in cell F2 exactly.

=LARGE($B$2:$B$11,D2) or =LARGE($B$2:$B$11;D2)

And then drag the formula until the last row.

large excel sort

Getting Num of Row

Second step is getting num of row. Num of Row is important to sort data in excel using formula. In column ROW, you have to write these combined formulas in in red marked area, in cell G2 exactly.

=IF(F2=F1,ROW(INDEX(INDIRECT("$B$"&G1+1):$B$11,MATCH(F2,INDIRECT("$B$"&G1+1):$B$11,0))),ROW(INDEX($B$2:$B$11,MATCH(F2,$B$2:$B$11,0))))

or

=IF(F2=F1;ROW(INDEX(INDIRECT("$B$"&G1+1):$B$11;MATCH(F2;INDIRECT("$B$"&G1+1):$B$11;0)));ROW(INDEX($B$2:$B$11;MATCH(F2;$B$2:$B$11;0))))

And then drag the formula until the last row.

excel get row

Getting Name from Row

In column NAME, write this formula in cell E2.

=INDIRECT("$A$"&G2)

And then drag the formula until the last row.

excel rank sort name

Sort Data Automatically using Formula

For column RANK, you just create number from 1 to 10.

Well done!. Autosort table was just made. Now you can sort data on excel automatically without click sort button (A-Z button)

sort data automatically excel

Try to change the score value of first table. Then you will see the second table sorted automatically. Good luck!

For further information about those formulas, you can learn them below:

LARGE function

ROW function

INDEX function

MATCH function

INDIRECT function

If you want to do it yourself, just click here to download the file on Google Drive. It works not only on Microsoft Excel, but also on Google Sheets.

I hope this help. 🙂