Share
[Social9_Share]

Ranking is one of the very common aspects of data analysis. In this post you’ll not only learn how to rank data in excel but also apply some variations to your ranking criteria.

Let take a look 3 tricks for ranking data in Excel

Note that as of now our data is sorted in descending order but our formulas will work even for unsorted data

**How do we do this** – by using the simple RANK function. Take a look

The Rank Function accepts 3 Inputs

**Number**– Which number do you want to rank**Reference**– List of numbers**Order**– 0 Stands for descending and 1 stands for ascending. By defaults excel picks up 0

The Rank function will automatically award the same rank to the ties and skip the rank for the largest next value.

The newer versions of Excel have the new RANK.EQ function which is exactly the same as RANK function. The only difference is that it is compatible with the newer version of Excel

For doing this we need to add a **COUNTIF function** along with the RANK Function

- The rank is doing the same work as seen in Trick 1
- But the
**COUNTIF**is checking that if the number has appeared in the range previous to the current cell or not - And adding that count to the current rank

This one gets a bit tricky. Let’s solve this in 3 Steps

**Step 1) Find the Unique Marks ***(i.e. none of the marks get repeated).*

- Note that this is an array formula and you must confirm the formula by pressing CTRL + SHIFT + ENTER and not the usual Enter key
- Related –
**How to extract unique values from a range**

**Step 2 – Arrange the unique marks in descending order** (largest to smallest)

- As of now it might look stupid to arrange the data in descending order since it is already in that order
- But remember the data could be in any order so I am adding this additional step
- I have used the LARGE and the
**ROWS formula**here

**Step 3 – Find the Rank using Match Function**

- The Match function will return the same rank for ties
- And the next rank (without skipping) for the next largest value
- Related –
**Learn more about the MATCH Function**

**DOWNLOAD THE EXCEL FILE WITH ALL TRICKS**

**Random Function Tricks****5 Extremely using Array Formulas****10 Autosum Formula Tricks****How to make your VLOOKUP Fast for a Large Dataset****25 Formula Hacks for Everyday Use**

- Learn 100 Excel Formulas
- Excel and PowerPoint Smart EBooks
- 5 Classic PowerPoint Animations – Free Course! (100th Blog)
- 10 Excel Habits that you must develop
- Free Pivot Table Course + A Sales Dashboard for YOU !
- A Human Resource Dashboard !
- The 3 musketeers in PowerPoint – CTRL, ALT & SHIFT
- Create a Map Chart for Plotting Cities
- A comprehensive guide to VLOOKUP + 5 crrraaazzy tricks !
- Financial Modeling – Getting Started !!