Sort Values in Excel and Google Sheets with Formulas!

/

Why would you want to filter with formulas?

Let’s say you have a list of employee’s bonus, which itself is a formula as it changes weekly.

Employee_Data_To_Rank.png

We can’t turn it into a table, due to a formula. We could potentially create a pivot table, but I hate pivot tables (another discussion entirely). Let’s do it with formulas!

How to sort in Excel with formulas

Helper columns is the short answer.

First of all we need to add a column, and round the bonuses to whatever decimal point is acceptable. Since this is wages, I have picked 4. Also add NUMBERVALUE and IFERROR for a more robust formula.

=IFERROR(NUMBERVALUE(ROUND(C2,4)),"")

Honestly, I am not sure why we need to do this step. I seem to have problems with the next step, unless this is done. If you know why, comment below!

We now add in a rank formula. =RANK(E2,$E$2:$E$21,0) Change 0 to 1 on end if you want it ascending instead of descending. However, there is a potential problem with this method.

It works, but I do not want duplicate ranks. I need it to be unique.
This is an easy fix, tack on =RANK(E3,$E$2:$E$21,0)+COUNTIF($E$2:E3,E3)-1

The COUNTIF returns 0, unless there is a duplicate. Then it is simply added to the rank!
Insert IFERROR for tidier data.

Excel_Unique_Rank_Formula.png

That’s all you need. You now can add in a table and use index match to see your results! The Rank is hard coded, while the other two columns are formulas. =INDEX($C$2:$C$21,MATCH(I2,$F$2:$F$21,0))

Sorted_List_Excel_Formulas.png

How to sort Google Sheets with Formulas

This is a lot easier than Excel. As you have a SORT formula in Google Sheets!
Which means all we need is =SORT(B2:C21,C2:C21,FALSE) You would change FALSE to TRUE if you wanted the data ascending.

It is also an array formula, so it automatically expands down to sort! Easy as that!
You can combine the SORT formula with the FILTER formula to refine the list as much as you want!

I can’t wait until Excel adds these functions!

Want to know more about Google Sheets? Check out my Macro and Scheduling tutorial for Google Sheets. If you do need the data in Excel, learn how to link google sheets to excel in real time!

Then why not send you awesome summary via an email with my excel macro to send email automatically!