How to easily rank numbers skip blank cells in Excel?
When working with data in Excel, it's common to encounter lists that contain blank cells. If you use standard Excel ranking functions such as RANK or RANK.EQ on such lists, the blank cells typically display errors or unwanted results in the ranking output. This makes your data harder to interpret, especially if you want to keep blank cells empty instead of showing error values or arbitrary ranks. Efficiently ranking numbers while automatically leaving blank cells empty can enhance the clarity and usability of your results, making your spreadsheet more professional and easier to read.
In this article, you will find step-by-step instructions for using formulas and VBA macros to accomplish this task. The expanded solutions also include parameter explanations, practical tips, and troubleshooting suggestions to help you avoid common pitfalls.
- Rank values while skipping blank cells in ascending order using formulas
- Rank values while skipping blank cells in descending order using a formula
- Rank values while skipping blank cells using VBA
Rank values while skipping blank cells in ascending order using formulas
In situations where you need to assign rankings in ascending order but want to disregard blank cells, a common approach is to use multiple helper columns and formula logic to ensure that empty values are ignored in ranking.
Applicable scenario: Use this method when you want to produce incremental (low-to-high) rankings while maintaining the position of empty cells, especially for continuous data ranges where missing entries should not affect rank numbering.
To perform an ascending rank while skipping blank cells, follow these steps, involving two helper columns to build up the result:
1. Select a blank cell adjacent to your values - for example, cell B2 if your list starts at A2 - and enter the following formula:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))The formula returns a blank if A2 is empty; otherwise, it generates decimal numbers based on the value of A2, adding .0, .1, .2, .3, etc., as you drag the fill handle down to copy the formula for all rows containing data.
Parameter explanations and tips:
- $A2: The first cell to rank. Adjust if your list starts on a different row.
- $B$2: The cell where you enter this formula. Pay attention to the absolute references (e.g., $A2 and $B$2) to ensure the formula behaves correctly when filled down.
2. In the next column, for example C2, enter this formula to generate a sorted list of helper values:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))This formula sequentially extracts the smallest, next smallest, and following values from B2:B8 (note: change the range if your data extends further) as you copy the formula down.
Parameter explanations:
- $B$2:$B$8: The range where the previous (first) helper formula is used.
- $C$1: Cell immediately above where you’re entering the formula; this offset controls ranking order.
3. In cell D2, enter the following formula to assign ranks while leaving blanks untouched:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")This formula matches the value in B2 within the sorted results in C2:C8. If it matches, it outputs the rank; if not (say, for blanks), it outputs nothing, maintaining the empty appearance for blank cells. Drag fill handle down to apply to all relevant rows.
Parameters:
- $B2: The cell containing the helper value for ranking.
- $C$2:$C$8: The range of sorted helper values.
Precautions: If you add or remove data, remember to update all ranges in each formula to match your new data size. For very large lists, consider using dynamic ranges or Excel Tables to reduce manual range adjustments.
Troubleshooting: If ranks are missing or misaligned, check that all helper formula ranges line up correctly. Misalignment between columns will lead to incorrect rankings or unintentional errors.
Rank values while skipping blank cells in descending order using a formula
When you want to assign rankings in descending order (highest value gets rank 1), there is a faster approach that uses just one formula. This is especially handy for test scores, sales targets, and similar datasets where blank cells represent missing or unavailable information, and you don’t want them taking up rank positions or showing errors.
Select a cell on the same row as the first data entry, where you want the extracted result to appear, and enter:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))After entering the formula, use the fill handle to copy the formula down alongside your data. This formula checks if the RANK function returns an error (for example, when A2 is blank); if so, it leaves the result blank rather than displaying “#N/A”. If the cell contains a valid value, it displays the appropriate rank.
Parameters:
- A2: The cell to be ranked (adjust for your data range).
- A$2:A$8: The full range of your data (use absolute reference for copying).
Error reminders: If you still see “#N/A” errors, double-check that the formula references match your intended data range and that there are no non-numeric values in the cells being ranked.
Rank values while skipping blank cells using VBA
For users comfortable with macros and looking to automate ranking across a range that contains blank cells - whether in ascending or descending order - a custom VBA macro can greatly simplify the process by eliminating the need for multiple helper columns and ongoing formula maintenance.
How to use:
1. Go to the Developer tab and click Visual Basic to open the Microsoft Visual Basic for Applications editor. If the Developer tab isn’t visible, see this guide: Show the Developer tab in Excel.
2. In the new Microsoft Visual Basic for Applications window, click Insert > Module and paste either of the following codes into the Module window:
- To perform an ascending rank while skipping blank cells:
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - To perform an descending rank while skipping blank cells:
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3. Press F5 to run the macro. A dialog box will prompt you to select the range you want to rank. After that, another dialog box will ask you to choose the first cell where the ranking results should be placed. The macro will then output the ranks starting from your selected cell, and any blank cells in the source range will be left empty.
Tips:
- If nothing happens, check that macros are enabled and you have permission to run code in your workbook.
- Running a VBA macro cannot be undone. Make sure to copy or back up your data before executing the macro.
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