Rank in Excel Ignoring Blanks: Formula and Examples

Formulas & functions · Updated November 18, 2026

Yes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank cell blank when that row has no score, put this in C2, assuming scores are in B2:B20, then fill it down: =IF(B2="","",RANK.EQ(B2,$B$2:$B$20,0)). If B2 contains 98 and no score is higher, the formula returns 1.

What “ignoring blanks” means in a ranking

The reference range in a rank formula can include empty cells without making you remove them first. Excel ranks the numeric values it finds and does not count an empty cell as a score. That is useful when a list is still being filled in, when a table has unused rows, or when some records have no result yet.

There are two different situations that are easy to confuse. A blank in the source data is not the same as a blank-looking rank formula. If you rank every row without checking whether its source cell has a value, the formula may display a rank beside a row that appears empty. The IF test in the opening formula prevents that: when B2 is blank, it returns an empty text string instead of calculating a rank.

Also distinguish an empty cell from a numerical zero. Zero is a number, not a blank, so Excel includes it in the ranking. If zero means “no result” in your workbook, you need a separate rule to exclude zero; ordinary blank handling will not do that.

How RANK.EQ handles the reference range

RANK.EQ takes a value to rank, a reference range, and an optional order argument. It compares the value with the numeric values in that range. Empty cells and text entries in the reference range are not treated as numeric scores. The function can therefore use a range that includes empty cells, including rows that have not been completed.

For example, with scores in B2:B6, the values 98, a blank, 84, 84, and 70 rank as 1, blank, 3, 3, and 5 if you use the opening formula and fill it down. The blank row stays visually blank, while the two 84s tie for third place. The next score is fifth because RANK.EQ gives tied values the same rank and skips the following rank positions.

The final argument controls direction. An order of 0, or leaving the argument out, ranks from highest to lowest: the largest number gets rank 1. An order of 1 ranks from lowest to highest: the smallest number gets rank 1. Choose the direction that matches what “best” means in your data—for example, highest test score versus fewest minutes.

Enter a basic rank formula

If every row in the output should contain a rank, including rows whose source is blank, a basic formula is enough. With scores in B2:B20 and the rank to display in C2, enter =RANK.EQ(B2,$B$2:$B$20,0). When B2 contains 98 and it is the highest score in the range, this returns 1. The dollar signs keep the comparison range fixed when you copy the formula down.

For a list that may contain unfinished rows, the guarded version is usually more useful. With scores in B2:B20 and the formula starting in C2, =IF(B2="","",RANK.EQ(B2,$B$2:$B$20,0)) displays nothing for an empty source cell and ranks a populated one. For example, if B2 is empty, C2 displays blank; if B2 is 84 in the example list above, C2 returns 3. Copy the formula down through the rows you want to include.

In Excel for Microsoft 365 on Windows, enter the formula in the formula bar or directly into the cell, press Enter, then use the fill handle at the lower-right corner of C2 to copy it. The formulas work the same way in Excel for Mac and Excel for the web. Depending on regional settings, Excel may expect semicolons instead of commas as argument separators; use the separator configured for your installation if a pasted formula is rejected.

Handle cells that look blank

A cell can look empty without actually being an empty cell. It might contain a formula that returns "", a space character, or another text value. This distinction matters most in the row-level check. The test B2="" treats a genuinely empty cell and a formula result of an empty string as blank, so the guarded formula suppresses the displayed rank in either case.

A cell containing a space is different. It may look blank, but the space is text, not an empty value. Clean the source data by removing the spaces, or use an appropriate data-cleaning step before ranking. In Excel, you can inspect a suspicious cell by selecting it and checking the formula bar; a visible space or a formula can explain why it is not behaving like an empty cell.

Numbers stored as text can also cause unexpected results. A value such as the text string “84” may appear numeric but may not be treated like the number 84 in calculations or comparisons. If some values are excluded or ranked oddly, check whether they are stored as numbers. You can convert them using Excel’s available conversion options, such as the warning menu that appears for some numbers stored as text, or by correcting the source import. Do not simply change the number format: formatting changes appearance, not the underlying value.

Ties, ascending order, and average rank

By default, RANK.EQ assigns the same rank to equal values. If the sorted values are 98, 84, 84, and 70, the ranks are 1, 2, 2, and 4. This is sometimes called competition ranking because the tie occupies two positions and the next rank is skipped. Blank rows do not occupy positions in that sequence.

If you want ties to receive the average of their occupied positions, Excel provides RANK.AVG. With scores 98, 84, 84, and 70 in B2:B5, and the formula entered in C3 for the first 84, =RANK.AVG(B3,$B$2:$B$5,0) returns 2.5: the two tied values occupy ranks 2 and 3, whose average is 2.5. Like RANK.EQ, it ignores blank and nonnumeric entries in the reference range. Use the same IF guard if you want the output cell to stay blank when the source row is empty.

For a lowest-to-highest ranking, change the order argument to 1. With scores in B2:B20 and the formula in C2, =RANK.EQ(B2,$B$2:$B$20,1) gives the smallest score rank 1. For example, if B2 is 70 and it is the lowest value, the result is 1. The ranking direction changes, but blanks remain excluded from the comparison.

Common problems and how to fix them

  • A blank row shows a rank. Add an IF check around the rank calculation, as in the opening formula. This is about keeping the result cell blank, not excluding an empty score from the comparison range.
  • Zero is ranked. Zero is a valid numeric value. Decide whether it is a real score or a placeholder, then handle it explicitly in your data or formula logic. Do not expect blank-cell behavior to remove it.
  • The rank looks too low or too high. Check the order argument. Zero or an omitted argument ranks highest first; 1 ranks lowest first. Also check that the reference range covers the intended records.
  • Unexpected ties appear. The values may be equal even if their displayed decimal places differ, or they may have been rounded for display. Increase the displayed decimal places or inspect the underlying values before deciding that the rank is wrong.
  • Some scores do not seem to participate. Look for numbers stored as text, stray spaces, or other nonnumeric entries. Those are data-quality issues rather than blank cells, and they may need correction at the source.

When copying formulas, keep the source cell reference relative so it changes from B2 to B3 and onward. Keep the comparison range absolute, as in $B$2:$B$20, so it does not slide down with each copied formula. If you add data below row 20, extend the range or use a suitable Excel Table column reference so the new rows are included.

Frequently Asked Questions

Does RANK.EQ ignore blank cells?
Yes. Empty cells in the reference range are not ranked as numeric values. Add an IF check if you also want the rank output beside an empty source cell to remain blank.

Does Excel rank a blank cell as zero?
A truly empty cell in the reference range is ignored; it is not a zero score. A numeric zero is included. A formula that returns an empty string looks blank and is text, so it is not the same as a numeric zero either.

How do I keep the rank cell blank when the score is blank?
With scores in B2:B20 and the output formula in C2, use =IF(B2="","",RANK.EQ(B2,$B$2:$B$20,0)) and fill it down. It leaves C2 empty when B2 is empty and otherwise ranks the score from highest to lowest.

Does RANK.AVG ignore blanks too?
Yes. RANK.AVG also disregards blank and nonnumeric entries in its reference range. Its difference is how it handles ties: it returns the average of the positions tied values would occupy.

Why does my blank-looking cell still affect my results?
Check whether it contains a space, a text value, a formula, or a numeric zero. A space is not an empty cell, and zero is a number. Inspect the formula bar and correct the underlying data or apply a formula rule that matches what the cell represents.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.