How to Rank in Google Sheets

How to Rank in Google Sheets

Use RANK. To rank the score in B2 against every score in B2:B11, put this in C2 and copy it down.

The highest number gets rank 1. The dollar signs matter more than anything else in the formula. Without them the reference range slides down as you copy, so row 10 ends up ranked against a shorter list than row 2.

The full syntax is RANK(value, data, [order]). Leave the third argument off, or pass 0, and the largest value is rank 1. Pass any nonzero number and the smallest value is rank 1, which is what you want for finish times and cost.

That covers the plain case. Most people arrive here because of what happens next, so find your symptom below.

• Two rows tied and the next rank jumped from 2 to 4, see dense ranking.
• You want no ties at all, see unique ranks with COUNTIF.
• You need a separate 1, 2, 3 per region or team, see ranking within a category.
• Dragging the formula down 5,000 rows, see ranking a whole column with MAP.
• The column is names, not numbers, see ranking text alphabetically.
• Blank rows returned #N/A, see the blanks section.
• You want a live top 10, see SORTN.
• Sorting moved your notes onto the wrong rows, see the drift section.
• The thing you want to rank by is not a number yet, see scoring with =FITS().

RANK vs RANK.EQ vs RANK.AVG in Google Sheets

RANK and RANK.EQ return the same answer. RANK.EQ exists for compatibility with Excel, which split the original function in two. If you have a choice, use whichever your team reads more easily.

Both use competition ranking. Two values tied for second both get 2, and the value after them gets 4. Nobody is assigned rank 3.

RANK.AVG is the one that differs. It gives tied values the average of the ranks they occupy, so the same pair returns 2.5 each. That is the correct choice for statistics and for splitting a prize between joint runners-up. It is the wrong choice for a leaderboard, because 2.5 reads as an error to anyone who did not write the sheet.

Neither function has a mode that stops skipping numbers. For that you have to rank against a different list, which is the next section.

How to Rank Without Skipping Numbers After a Tie

Rank against the deduplicated list rather than the raw one. This is called dense ranking, and it produces 1, 2, 2, 3 where RANK produces 1, 2, 2, 4.

Read it inside out. FILTER drops the blanks, UNIQUE collapses the ties into one entry each, SORT puts them in descending order, and MATCH reports which position your value landed in. Position in a deduplicated sorted list is exactly what a dense rank is.

Swap the FALSE in SORT to TRUE when smallest should be rank 1. The IFERROR is there so blank rows stay blank instead of showing #N/A.

How to Give Every Row a Unique Rank With No Ties

Sometimes you need a strict 1 to N ordering, usually because the rank feeds a lookup or a numbered list. Count how many values beat this one, then add one.

On its own that reproduces RANK exactly, ties and all. To break the ties, add a count of the tied rows above the current one.

The second range is the trick. $B$2:B2 has a locked start and a relative end, so it grows one row at a time as you copy down. The first row of a tie sees one match, the second sees two, and the ranks come out 1, 2, 3, 4 with the earlier row winning.

How to Rank Within a Category or Group

RANK takes one range and no conditions, so it cannot rank sales reps inside their own region. COUNTIFS can, because it accepts as many criteria pairs as you need.

Column A holds the region and column B holds the score. The formula counts rows that share this row's region and carry a higher score, then adds one. Every region restarts at 1.

The same shape handles a second grouping level. Add another pair of arguments for quarter or product line, and the rank narrows to that slice. If your categories do not exist yet, classifying free text into categories gives you the column to group on.

How to Rank a Whole Column Without Dragging the Formula Down

The obvious attempt fails, and the reason is worth knowing.

RANK does not vectorize over its first argument. ARRAYFORMULA can only expand a function that already knows how to accept an array there, and RANK does not, so you get one repeated value instead of a column of ranks.

MAP with LAMBDA solves it by calling RANK once per cell, which is the thing ARRAYFORMULA could not force it to do.

One formula in C2 fills the entire column and keeps filling it as rows arrive. The ISNUMBER guard does double duty: it returns an empty string for blank and text rows, and it keeps those rows out of the reference range so they cannot distort the ranks.

How to Rank Text Alphabetically in Google Sheets

RANK rejects text. Feeding it a name returns #VALUE! with the message that parameter 1 expects number values. COUNTIF has no such restriction, because it compares text lexicographically.

That counts every name that sorts before this one and adds one, which is alphabetical rank. Flip the operator to ">" to rank Z to A. Note that this is a comparison of characters, not of meaning, so "10" sorts before "9" when your values are text rather than numbers.

Why RANK Returns #N/A on Blank Rows

Point RANK at an open range like B2:B and it evaluates every empty row underneath your data. An empty cell is not found in the reference range, so Sheets returns #N/A with a message that it did not find the value in RANK evaluation.

The same error appears with a full range when the value being ranked sits outside the range you are ranking against. That usually means the dollar signs are missing or the FILTER condition excluded the current row.

Guard the input and clean the reference range in the same formula. The IF stops blank and text rows from being ranked, and the FILTER stops them from counting toward anyone else's rank. Wrapping the whole thing in IFERROR hides the symptom but leaves a silently wrong reference range in place, so prefer the guard.

How to Build an Auto-Updating Top 10 With SORTN

SORT orders everything. SORTN orders it and hands back only the first n rows, which is what a leaderboard actually needs.

The third argument is the one that matters and the one most guides skip. It controls what happens at the cutoff when rows are tied.

• 0 returns exactly n rows. If two rows tie for tenth, one of them is dropped arbitrarily.
• 1 returns n rows plus every row tied with the nth. Use this for any leaderboard a human will read.
• 2 returns up to n rows after removing whole-row duplicates.
• 3 returns at most n unique values in the sort column, along with every row that matches them.

Mode 0 is the default, so a top 10 built without thinking about this argument will quietly hide a tied competitor. For a different slice of the same data, QUERY with an order by clause does the same job with SQL-style syntax.

Why Sorting Your Sheet Moved Your Notes to the Wrong Rows

This is the most expensive mistake in the whole cluster, because it corrupts data without showing an error.

A SORT output is a read-only array. You cannot type into the cells it occupies. So people put the sorted formula in columns A to C and type their own comments into column D beside it.

Then a new row arrives and the ordering changes. Columns A to C recalculate and shift. Column D does not, because it is static text. Row 4 now shows one record next to a comment about a different one, and nothing on screen says so.

The rule is simple. Never put manually typed cells beside a dynamic array. Keep notes on the source table next to a stable ID, and pull them into the sorted view with a lookup so they travel with the record.

If a dynamic formula refuses to output at all, the cause is usually the mirror image of this problem. Sheets reports that the array result was not expanded because it would overwrite data, which means something is already sitting in the spill range. Clear the cells below and to the right and it fills in. The same constraint governs transposing rows and columns, which also needs an empty output area.

Why Google Sheets Will Not Auto-Sort as You Add Rows

There is no setting for it. Data then Sort range is a one-time action against the rows that exist when you click it. Filter views and slicers change what you see and leave the stored order untouched. A new row lands at the bottom and stays there.

The supported pattern is two tabs. Leave the source table in entry order and let a formula on a second tab hold the sorted view.

That sorts by column 3 descending and breaks ties with column 2 ascending. SORT accepts as many column and direction pairs as you want. The FILTER wrapper drops the trailing empty rows that an open range would otherwise sort to the top.

Everything recalculates on its own when a row is added. The alternative, an onEdit script that sorts the source in place, is what creates the notes drift described above, and it does not fire when a value changes because a formula recalculated rather than because someone typed. See auto-populating cells based on another cell for where that trigger gap bites hardest.

Why SORT and RANK Need a Number You May Not Have

Every formula above shares one assumption. Something in the row is already a number.

Ranking sales leads by how promising they sound gives you nothing to point RANK at. Neither does ranking support tickets by how angry they read, or ranking content ideas by how well they fit next quarter's strategy.

The usual workaround is a manual 1 to 5 column that somebody fills in by hand. It works for fifty rows and collapses at five hundred, and two people never score it the same way.

What is missing is not a better ranking formula. It is the number itself.

How to Score Rows With One =FITS() Formula, Then Rank Them

With FITS, you create the number that did not exist. Put the score in its own column, keyed to its own row.

Now SORT and RANK.EQ have something real to work on. Point them at the score column and the ordering maintains itself.

Because the score is a formula in the row, it travels with the row. Sorting the source data can no longer separate a record from its own score.

Ties are usually the next complaint. Ask for a tiebreaker in the same formula rather than adding a second sort key.

One number per row, generated from criteria you wrote in a sentence. Everything downstream is ordinary spreadsheet work.

Which Ranking Method Should You Use

If the column is already a clean number, RANK is free and instant. Reach for the COUNTIF and COUNTIFS variants when you need unique ranks or a rank per category, and for MAP when you are tired of dragging a formula down.

Reach for =FITS() only when the ranking criteria live in a sentence rather than a cell. Related jobs use the same pattern: highlighting the best row rather than the biggest number, classifying free text into categories, and building an editorial KPI dashboard. The full tour is in automating Google Sheets tasks you used to need regex for.

Frequently Asked Questions

What is the rank formula in Google Sheets?

=RANK(value, data, [order]). To rank cell B2 against the scores in B2:B11 with the largest value as rank 1, use =RANK(B2, $B$2:$B$11). The dollar signs lock the reference range so the formula still points at the whole list after you copy it down.

What is the difference between RANK and RANK.EQ in Google Sheets?

Nothing, in practice. RANK.EQ was added for compatibility with Excel and returns exactly what RANK returns, including the same tie behaviour. Two values tied for second both receive rank 2 and the next value receives rank 4. RANK.AVG is the one that behaves differently. It gives tied values the average of the ranks they span, so the same pair returns 2.5.

How do I rank in Google Sheets without skipping numbers after a tie?

RANK always skips. For dense ranking, where a tie for second is followed by third rather than fourth, rank against the deduplicated list instead: =IFERROR(MATCH(B2, SORT(UNIQUE(FILTER($B$2:$B, $B$2:$B<>"")), 1, FALSE), 0), ""). MATCH returns the position of the value in the sorted unique list, which is the dense rank.

How do I rank within a group or category in Google Sheets?

RANK cannot filter by a second column, so use COUNTIFS instead: =COUNTIFS($A$2:$A, A2, $B$2:$B, ">" & B2) + 1. It counts how many rows share this row's category and beat its score, then adds one. Each region, team or campaign gets its own 1, 2, 3 sequence.

Why does ARRAYFORMULA not work with RANK in Google Sheets?

RANK does not vectorize over its first argument, so ARRAYFORMULA has nothing to expand and you get one repeated value rather than a column of ranks. Use MAP and LAMBDA instead: =MAP(A2:A, LAMBDA(v, IF(NOT(ISNUMBER(v)), "", RANK(v, FILTER($A$2:$A, ISNUMBER($A$2:$A)))))). MAP calls RANK once per cell, which is what ARRAYFORMULA cannot make it do.

Can Google Sheets sort automatically when new rows are added?

Not in place. Data then Sort range is a one-time action, and filter views and slicers change only what you see, not the stored row order. The supported pattern is to leave the source table alone and put =SORT(FILTER(A2:D, A2:A<>""), 3, FALSE) on a second tab, which re-sorts itself every time the source changes.