How to Rank Values with the RANK Function

This guide explains how to use the RANK function in spreadsheet applications like Microsoft Excel and Google Sheets. You’ll learn the syntax, how to rank numbers in ascending or descending order, and how to handle ties. By the end, you’ll be able to rank any list of values confidently.

What You’ll Need

• A spreadsheet application (Excel, Google Sheets, LibreOffice Calc, etc.)
• A column or row of numeric values you want to rank
• Basic familiarity with entering formulas in a cell

Step-by-Step

1. Identify your data range. For example, suppose you have scores in cells B2 through B10. The range is B2:B10.
2. Choose the cell for the first rank. Click on an empty cell, typically next to the first value (e.g., C2).
3. Type the RANK function. Enter =RANK( to start the formula.
4. Reference the value to rank. Click on the cell containing the value you want to rank, or type its reference (e.g., B2).
5. Add the range. Type a comma, then select the entire range of values (e.g., B2:B10). Make sure to use absolute references (with dollar signs) if you plan to copy the formula down. For example: =RANK(B2, $B$2:$B$10).
6. Set the order (optional). Type a comma and then a number: 0 or omit for descending order (largest value gets rank 1); 1 for ascending order (smallest value gets rank 1). For example: =RANK(B2, $B$2:$B$10, 1).
7. Close the parentheses and press Enter. The rank of the first value appears.
8. Copy the formula down. Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to all values in your list.

Common Mistakes

• Forgetting absolute references. If you don’t use dollar signs (e.g., $B$2:$B$10), copying the formula down will shift the range, causing incorrect ranks.
• Using the wrong order argument. Many users assume 1 means largest first, but it actually means ascending order (smallest gets rank 1). Double-check your intent.
• Including the formula cell in the range. If your range includes the cell where you’re writing the formula, it can create a circular reference. Keep the range limited to the data values.
• Ranking text or blanks. The RANK function ignores text and empty cells, but if your range contains errors, the function may return an error.

Tips

• Use RANK.EQ for standard ranking. In modern Excel and Google Sheets, RANK.EQ is the recommended version; it behaves the same as RANK. The older RANK still works for compatibility.
• Handle ties deliberately. RANK gives tied values the same rank, then skips the next rank(s). For example, two values tied for 2nd place both get rank 2, and the next value gets rank 4. If you want no skipped ranks, use RANK.AVG which assigns the average rank to ties.
• Rank in reverse order easily. To rank from smallest to largest (e.g., ranking times in a race), use 1 as the third argument.
• Combine with other functions. You can use RANK inside IF or VLOOKUP to create dynamic dashboards.
• Use a table for dynamic ranges. If you convert your data to an Excel Table (Ctrl+T), the range will automatically expand as you add data, and your RANK formulas will update.

Troubleshooting

• #N/A error: This occurs if the value you’re ranking is not found in the range. Check that the value exists and that the range is correct.
• #VALUE! error: Usually means the order argument is not 0 or 1, or the value/range contains non-numeric data. Ensure all values are numbers.
• Ranks seem wrong (e.g., 1, 2, 2, 4): This is expected with ties. If you want consecutive ranks, switch to RANK.AVG or use a workaround like adding a tiny decimal to break ties.
• Ranks don’t update when data changes: This might happen if calculation is set to manual. Press F9 (Excel) to recalculate, or change calculation options to automatic.
• Formula works but returns 0 for all: Check if your range includes only one cell, or if the values are stored as text. Use the VALUE function to convert text to numbers.

Frequently Asked Questions

What is the difference between RANK.EQ and RANK.AVG?

RANK.EQ assigns the same rank to ties and skips subsequent ranks (e.g., 1, 2, 2, 4). RANK.AVG assigns the average of the ranks that would have been assigned (e.g., for two tied at 2nd and 3rd, both get 2.5).

Can I rank values in descending order (largest first)?

Yes, use 0 or omit the third argument. For example: =RANK(B2, $B$2:$B$10, 0) gives rank 1 to the largest value.

How do I rank a list without skipping numbers after ties?

Use RANK.AVG to get average ranks, or use a helper column with a tiebreaker (e.g., add a tiny decimal based on row number) and rank that.

Does RANK work with negative numbers?

Yes, RANK works with any numeric values, including negatives. The order argument determines whether the most negative or most positive gets rank 1.

Can I rank values across multiple columns?

Yes, you can reference a range that spans multiple columns. For example, =RANK(B2, $B$2:$C$10) ranks B2 among all values in B2:C10.

Why does my RANK formula return the same rank for all values?

This usually happens when the range is not absolute and the formula is copied down, causing the range to shrink. Check that the range has dollar signs (e.g., $B$2:$B$10).

Related

• Is 9anime.vc Safe? Risks, Legality, and Safer Options
• Best Games Releasing in August 2026
• Hello Kitty Party Land: Physical or Digital?