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
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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.
