Skip to main content

How to auto fill serial numbers and skip blanks in a list of cells?

If you have a list of data which contains some blank cells, now, you want to insert serial numbers for the data, but skip the blank cells as below screenshot shown. In this article, I will introduce a formula for solving this task in Excel.


Auto fill serial numbers and skip blank cells with formula

To skip the blank cells when filling serial numbers for the data, you can create a formula based on COUNTA, ISBLANK, and IF functions:

Step1: Enter the following formula into a blank cell where you want to insert the serial numbers:

=IF(ISBLANK(A2),"",COUNTA($A$2:A2))

Step2: Then, drag the fill handle down to fill this formula to other cells, and the serial numbers are filled into the cells but avoiding the blank cells, see screenshot:


The Best Office Productivity Tools


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations