---
title: "RANK Function in Google Sheets: RANK, RANK.EQ and RANK.AVG"
canonical: "https://www.owox.com/blog/articles/rank-function-google-sheets"
updated: "2026-10-05"
---

# RANK function in Google Sheets: RANK, RANK.EQ and RANK.AVG

RANK returns the position of a value in a list of numbers: 1 for the largest by default. This guide shows the syntax, how RANK.EQ and RANK.AVG treat ties, ranking in ascending order, a rank without gaps, a unique rank for tied values, ranking within a group, and the absolute-reference mistake that silently gives wrong ranks.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 6 min read

![RANK function in Google Sheets: RANK, RANK.EQ and RANK.AVG](https://cdn.owox.com/www/content-marketing-article/rank-function-google-sheets/thumbnail/1-od0d5b.png/public)

The RANK function returns the position of a number in a list of numbers. The largest value gets rank 1:

> `=RANK(C3, C$3:C$12)`

Fill the formula down and every row shows its place in the list. For the smallest value to be rank 1, add 1 as the third argument: `=RANK(C3, C$3:C$12, 1)`.

Sheets has three ranking functions. They differ only in what they do with ties:

| Function | Two values tied for 4th place get | Next rank |
| --- | --- | --- |
| `RANK` | 4 and 4 | 6 |
| `RANK.EQ` | 4 and 4 | 6 |
| `RANK.AVG` | 4.5 and 4.5 | 6 |

RANK and RANK.EQ return the same results.

## RANK syntax

> `=RANK(value, data, [is_ascending])`

*   **value:** the number to rank.
*   **data:** the range that holds all the numbers, the value included.
*   **is\_ascending:** optional. 0 or omitted ranks the largest value first; 1 ranks the smallest first.

RANK.EQ and RANK.AVG take the same arguments.

> `=RANK(C3, C$3:C$12, 0)`

![Google Sheets with monthly sales of ten sales reps in column C and the formula =RANK(C3, C$3:C$12, 0) returning the rank of each rep, 1 for the highest sales of 60,700](https://cdn.owox.com/www/webflow/69413267251e344de756a3b0_cb904197.png/public)

The whole column can be ranked by one formula:

> `=ARRAYFORMULA(RANK(C3:C12, C3:C12, 0))`

![Google Sheets with the formula =ARRAYFORMULA(RANK.EQ(C3:C12, C3:C12, 0)) returning the rank of every sales rep at once](https://cdn.owox.com/www/webflow/69413267251e344de756a3b6_28fee504.png/public)

## Lock the range, or the ranks are wrong

The dollar signs in `C$3:C$12` keep the range in place when the formula is filled down. Without them the range moves with every row:

> `=RANK.EQ(C3, C3:C12, 0)`

![Google Sheets with the formula =RANK.EQ(C3, C3:C12, 0) filled down without dollar signs; Harper, with sales of 50,800, shows rank 2](https://cdn.owox.com/www/webflow/69413267251e344de756a3a7_c17efe80.png/public)

In row 10 the formula has become `=RANK.EQ(C10, C10:C19, 0)`: Harper is ranked only against the two rows below and shows rank 2, although three reps sold more. There is no error message, and the first rows look right. Compare with the correct ranks in the first screenshot, where Harper is 4th.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Ties: RANK.EQ and RANK.AVG

When two values are equal, RANK and RANK.EQ give both the higher place and skip the next one:

> `=RANK.EQ(C3, C$3:C$12)`

![Google Sheets with three pairs of equal sales amounts and the formula =RANK.EQ(C3, C$3:C$12) returning 1 and 1, 4 and 4, 7 and 7, with ranks 2, 5 and 8 skipped](https://cdn.owox.com/www/webflow/69413267251e344de756a390_5a19c049.png/public)

RANK.AVG gives tied values the average of the places they occupy. Two values that share places 4 and 5 both get 4.5:

> `=RANK.AVG(C3, C$3:C$12, 0)`

![Google Sheets with the same data and the formula =RANK.AVG(C3, C$3:C$12, 0) returning 1.5, 4.5 and 7.5 for the tied reps](https://cdn.owox.com/www/webflow/69413267251e344de756a3ad_c7ba731a.png/public)

The average rank is used in statistics, where the ranks have to add up to the same total with or without ties. For a leaderboard, RANK.EQ is what readers expect.

### A rank without gaps

RANK skips places after a tie: 1, 1, 3. To number the distinct values 1, 2, 3 with no gaps (a dense rank), count how many distinct values are larger:

> `=SUMPRODUCT((C$3:C$12 > C3) / COUNTIF(C$3:C$12, C$3:C$12)) + 1`

### A unique rank for tied values

To give tied values different ranks, in the order they appear in the list, add the number of earlier occurrences:

> `=RANK(C3, C$3:C$12) + COUNTIF(C$3:C3, C3) - 1`

The first of two tied values keeps the rank, the second gets the next one. This is the form to use when the rank feeds a lookup that needs every rank exactly once.

## Rank within a group

RANK has no condition argument. To rank each row among the rows of its own group, such as its region, count the rows of the same group with a larger value:

> `=COUNTIFS(B$3:B$12, B3, C$3:C$12, ">" & C3) + 1`

With the group in column B and the value in column C, the formula returns 1 for the largest value of each group. It treats ties as RANK does. See [COUNTIF and COUNTIFS](/blog/articles/countif-and-countifs-google-sheets).

## The top N rows

Often the rank is only a step towards a top list. These return it directly:

*   `=LARGE(C3:C12, 3)` returns the third largest value.
*   `=SORTN(A3:C12, 3, 0, 3, FALSE)` returns the three rows with the largest values in the third column. See [SORT and SORTN](/blog/articles/sort-and-sortn-functions-google-sheets).
*   `=QUERY(A2:C12, "select * order by C desc limit 3", 1)` does the same with [QUERY](/blog/articles/query-function-google-sheets).

To color the top three in place, use conditional formatting with the custom formula `=RANK($C3, $C$3:$C$12) <= 3`.

## Errors and wrong results

**⚠️ Error:** #N/A

✅ **Solution:** The value is not in the data range. RANK only ranks numbers that are in the list: check that the range covers the row of the formula.

**⚠️ Error:** #VALUE!

✅ **Solution:** The value is text. Numbers imported as text have to be converted with VALUE first; text cells inside the data range are ignored.

**⚠️ Error:** The ranks get smaller towards the bottom of the column and several rows show 1.

✅ **Solution:** The range is not locked. Write it as `C$3:C$12`.

**⚠️ Error:** A rank is missing: 1, 2, 2, 4.

✅ **Solution:** That is how ties are ranked. Use the dense-rank formula above for 1, 2, 2, 3.

**⚠️ Error:** The best result has the highest number.

✅ **Solution:** The third argument decides the direction. For values where smaller is better, such as cost or time, use 1.

## Related Google Sheets functions

*   [SORT and SORTN](/blog/articles/sort-and-sortn-functions-google-sheets): Order rows and take the top N.
*   [MAX, MIN and MEDIAN](/blog/articles/max-min-median-functions-google-sheets): The largest, smallest and middle values, with LARGE and SMALL.
*   [PERCENTRANK](/blog/articles/percentrank-function-google-sheets): The rank as a share of the list.
*   [PERCENTILE](/blog/articles/percentile-function-google-sheets) and [QUARTILE](/blog/articles/quartile-function-google-sheets): Cut points of a distribution.
*   [COUNTIF and COUNTIFS](/blog/articles/countif-and-countifs-google-sheets): The building blocks of conditional ranks.
*   [How to sort data](/blog/articles/how-to-sort-data-google-sheets): Sorting from the menu.
*   [Array formulas](/blog/articles/array-formulas-google-sheets): One formula for a whole column.

## A ranking is a snapshot of its rows

A rank says who is first among the rows in the range. Add last week of sales and the order changes; leave out a region by mistake and the leader of the list is not the leader. Rankings built on pasted exports are right for the day of the export, and the sheet gives no sign of how old that day is.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.com/www/content-marketing-article/rank-function-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. The ranking formulas then recalculate on every refresh, over the same complete set of rows for everyone. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I handle ties when using the RANK function?

To handle ties, use RANK.AVG or RANK.EQ. RANK.AVG assigns the average rank to tied values, while RANK.EQ assigns the same rank to tied values but skips subsequent ranks.

Can I combine the RANK function with conditional formatting?

Yes, you can apply conditional formatting to highlight top-ranked values, lowest-ranked items, or specific percentile ranges to improve data visualization and analysis.

How do I rank percentages or decimal values in Google Sheets?

The RANK function works with all numeric values, including percentages and decimals. Ensure your data is formatted correctly to avoid errors in ranking calculations.

How do I rank only specific values within a filtered dataset?

RANK has no condition argument. To rank a row among the rows of its own category, count the rows of that category with a larger value: COUNTIFS(B$3:B$12, B3, C$3:C$12, ">" & C3) + 1, with the category in column B and the value in column C. To rank only the rows that pass a filter, build the filtered list with FILTER first and rank inside it.

Can I rank values in ascending order instead of descending?

Yes, by setting the optional order argument to 1 in the RANK function, you can rank values in ascending order instead of the default descending order.

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

RANK and RANK.EQ return the same result: tied values share the higher place and the next place is skipped, for example 4, 4, 6. RANK.AVG gives tied values the average of the places they occupy, 4.5 and 4.5. All three take the same arguments: the value, the range and an optional 1 for ascending order.

What is the syntax for the RANK function in Google Sheets?

The syntax for the RANK function in Google Sheets is:

\=RANK(value, data, \[is\_ascending\])

Where value is what you're ranking, data is the range, and is\_ascending is optional for ranking in ascending or descending order.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.com/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · QUERY with UNIQUE in Google Sheets: Return Distinct Rows · October 2, 2026](/blog/articles/query-with-unique-google-sheets)

[Google Sheets Tips · QUERY vs VLOOKUP in Google Sheets: Which One to Use · October 2, 2026](/blog/articles/query-vs-vlookup-google-sheets)

[Google Sheets Tips · VLOOKUP in Google Sheets: Formula, Another Sheet and Examples · October 2, 2026](/blog/articles/vlookup-in-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

## Schedule in person meeting at Data Outpost San Francisco

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [COUNTIF and COUNTIFS](https://www.owox.com/blog/articles/countif-and-countifs-google-sheets) — /blog/articles/countif-and-countifs-google-sheets.md
- [SORT and SORTN](https://www.owox.com/blog/articles/sort-and-sortn-functions-google-sheets) — /blog/articles/sort-and-sortn-functions-google-sheets.md
- [QUERY](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md
- [MAX, MIN and MEDIAN](https://www.owox.com/blog/articles/max-min-median-functions-google-sheets) — /blog/articles/max-min-median-functions-google-sheets.md
- [PERCENTRANK](https://www.owox.com/blog/articles/percentrank-function-google-sheets) — /blog/articles/percentrank-function-google-sheets.md
- [PERCENTILE](https://www.owox.com/blog/articles/percentile-function-google-sheets) — /blog/articles/percentile-function-google-sheets.md
- [QUARTILE](https://www.owox.com/blog/articles/quartile-function-google-sheets) — /blog/articles/quartile-function-google-sheets.md
- [How to sort data](https://www.owox.com/blog/articles/how-to-sort-data-google-sheets) — /blog/articles/how-to-sort-data-google-sheets.md
- [Array formulas](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · QUERY with UNIQUE in Google Sheets: Return Distinct Rows · October 2, 2026](https://www.owox.com/blog/articles/query-with-unique-google-sheets) — /blog/articles/query-with-unique-google-sheets.md
- [Google Sheets Tips · QUERY vs VLOOKUP in Google Sheets: Which One to Use · October 2, 2026](https://www.owox.com/blog/articles/query-vs-vlookup-google-sheets) — /blog/articles/query-vs-vlookup-google-sheets.md
- [Google Sheets Tips · VLOOKUP in Google Sheets: Formula, Another Sheet and Examples · October 2, 2026](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md