How to rank data ignore zero values in Excel?
When working with datasets in Excel, it is common to encounter zero values within lists of numerical data. By default, Excel’s standard RANK functions include these zeros in the ranking process, which may not reflect real-world performance, such as sales, scores, or measurements, where a zero signifies absence rather than the lowest value. For truly meaningful analyses—such as leaderboard rankings, filtered performance assessments, or statistical reporting—you may need to rank only positive numbers, ignoring zeros entirely. The image below illustrates this need: only non-zero values are assigned ranks, while zeros remain unranked or blank for clarity.
Formula: Rank cells ignore zero values
Formula: Rank cells ignore zero values
To rank data in Excel while omitting zero values, you can use custom formulas that assign ranks only to cells containing non-zero numbers. This is practical for datasets where zeros represent missing or irrelevant information, ensuring your ranked results spotlight only valid entries. This approach works well for small-to-medium datasets and allows instant visual filtering by simply dragging the formula down alongside your data. However, manual formulas may require adjustments if your data range or ranking conditions change frequently.
Rank ignore zeros in ascending order
Enter the following formula in the cell adjacent to your first data value (for example, if your data starts at B2, enter this in C2):
=IF(B2=0,"",RANK(B2,IF($B$2:$B$12>0,$B$2:$B$12,0))+COUNTIF($B$2:$B2,$B2)-1)After inputting the formula in C2, press Enter. Drag the fill handle in the lower-right corner of C2 down to fill the formula for the entire column, covering all data rows. This formula checks for zero and leaves its rank cell blank. If you copy or move your range, double-check cell references to ensure they still match the intended data range.
Rank ignore zeros in descending order
For ranking in descending order while ignoring zeros, input the following formula (assuming your list starts at B2, enter this in C2):
=IF(B2,RANK(B2,B$2:B$12,1)-COUNTIF(B$2:B$12,0),"")Again, press Enter after inserting the formula, then drag the fill handle down to apply it for all rows. This method highlights only non-zero entries with appropriate ranks, skipping blank cells for any zero value.
In both formulas, B2 is your starting cell, and B2:B12 defines the range to be ranked. If your dataset has more or fewer rows, adjust the range references. Keep in mind: if your dataset is updated or expanded, formulas may need adjustment. Also, be cautious when copying formulas—the use of absolute references ($ signs) locks cells when filling down.
| Rank ignore zeros in ascending order |
 |
| Rank ignore zeros in descending order |
 |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in