Rank Numbers and Skip Blank Cells in Excel

Ranking numbers is a regular chore in Excel, whether you're working with a large dataset or simply need to organise your data. When working with blank cells, though, the process can become a little more difficult. But don't worry! In this video, we'll walk you through a quick and easy approach to rank numbers in Excel, skipping any blank cells you might come across along the way.

By the end of the tutorial, you will have learned how to use a combination of Excel functions and formulas to rank numbers in ascending or decreasing order, while also handling blank cells properly. This method saves you time and effort by providing a streamlined solution to this typical data manipulation chore. So, if you're ready to improve your Excel skills and master the art of ranking numbers while handling blank cells gracefully, let's get started!

Easily Rank Numbers Skip Blank Cells

Here we will first create two helper columns, then use the formula, and finally use the auto?fill handle to complete the task. So let us see a simple process to learn how you can easily rank numbers and skip blank cells in Excel.

Step 1

Consider an Excel sheet where you list scores similar to the below image.

First, to get the first helper, click on an empty cell and enter the formula as

=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()?ROW($B$2)))) and click enter, then drag down using the auto fill handle.

Empty cell > Formula > Enter > Drag.

Step 2

Then, to get the second helper, click on the empty cell and enter the formula as =SMALL($B$2:$B$10,ROW()?ROW($C$1)) and click enter, then drag down using the autofill handle.

Empty cell > Formula > Enter > Drag.

Step 3

Finally, to rank the cells, click on the empty cell and enter the formula

=IFERROR(MATCH($B2,$C$2:$C$10,0),"") and click enter, then drag to fill all the ranks.

Empty cell > Formula > Enter > Drag.

Note?

If you want to rank the scores in descending order, use the formula as

=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can easily rank numbers and skip blank cells in Excel to highlight a particular set of data.