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

When working with lists of data in Excel, it's common to encounter situations where not every cell is filled. For example, you may have imported information or collected responses that left some blank cells in between entries. In such cases, you might want to generate serial numbers for only the filled cells—omitting any blanks from your numbering system so that the sequence flows seamlessly without gaps. This can make your lists more organized and improve readability, especially when you need to cross-reference row numbers or track entries systematically.

Here is an example of a list with some blank rows. Our goal is to fill serial numbers only for cells containing data, automatically skipping any blanks, as illustrated below:

This article will introduce several practical methods to achieve this in Excel:

• Auto-fill serial numbers and skip blank cells with a formula
• VBA Code - Automatically generate and fill serial numbers while skipping blank cells
• Other Built-in Excel Methods - Use Power Query to add an index and filter blanks

If you want to easily number only the non-blank cells and skip any empty rows within your dataset, an efficient approach is to use an Excel formula combining COUNTA, ISBLANK, and IF functions. This method is suitable when your data resides in a single column and you wish to quickly generate a running serial number whenever a cell contains data.

This technique is particularly useful for moderate-sized datasets managed manually, or when you need a quick solution without using code or additional Excel tools.

To implement this solution, follow these steps:

1. Select the blank cell where you want the serial number to begin (for example, cell B2 if your data is in column A starting at A2). Enter the following formula:

Parameter explanations:

• A2: The cell being checked for blankness.
• $A$2:A2: The growing range as you fill down, used to count how many non-blank cells have appeared so far.
• The formula returns a serial number only if the corresponding cell in column A contains data; otherwise, it returns an empty string.

2. Confirm the formula by pressing Enter. Then, position your cursor over the bottom right corner of the cell to reveal the fill handle, and drag it down alongside your list. The formula will automatically update for each row, numbering only those with data present and leaving blank rows untouched.

For best results, check that your target cells do not contain any spaces or invisible characters. If you notice errors or unexpected blanks, clear unnecessary content from those cells. If your data range changes frequently, consider converting your range into a table for easier formula management.

Successfully applying the formula should give you a result similar to the screenshot below:

If you encounter calculation errors, check for improper range references or merged cells in your dataset, which can sometimes interfere with serial number generation.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

VBA Code - Automatically generate and fill serial numbers while skipping blank cells

For users who need a more automated process, especially for long lists or frequently changing data, a VBA macro can quickly assign serial numbers to non-blank cells while leaving blanks unchanged. This method is ideal if you want to handle large volumes, save time, or provide a custom solution that you can easily repeat.

Here is how to set up and use a VBA macro to achieve this:

1. Navigate to Developer Tools in the top Excel menu, then click Visual Basic. In the window that opens, select Insert > Module and paste the following code into the module:

Sub FillSerialNumbersSkipBlanks()
    Dim Rng As Range
    Dim cell As Range
    Dim SerialNum As Long
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set Rng = Application.Selection
    Set Rng = Application.InputBox("Select the data range based on which serial numbers will be filled", xTitleId, Rng.Address, Type:=8)
    SerialNum = 1
    For Each cell In Rng
        If Not IsEmpty(cell.Value) Then
            cell.Offset(0, 1).Value = SerialNum
            SerialNum = SerialNum + 1
        Else
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub

2. After pasting the code, return to Excel and click the Run button. A dialog box will prompt you to select the range of cells containing your data (for example, select A2:A20). The code will fill serial numbers into the adjacent column (B2:B20), skipping any blank cells in the original range.

If your selection contains merged cells or formulas displaying blanks, this may affect accuracy. It's recommended to use single, unmerged cells for reliable results. If the macro does not run as expected, ensure that macros are enabled in Excel and check that you have selected the correct data range.

This VBA solution automatically assigns serial numbers next to non-blank data and is reusable for different columns or ranges as needed. You can adjust the cell.Offset(0,1) reference if you wish to fill serial numbers into a different column.

Other Built-in Excel Methods - Use Power Query to add an index and filter blanks

Power Query offers a highly flexible way to process and transform your data, including generating serial numbers for only non-blank cells. This method is optimal for working with larger datasets, importing data from external sources, or when you want dynamic updates as your source information changes. The main advantages of this solution are repeatability, automation, and the ability to handle data cleansing together with numbering.

To use Power Query to fill serial numbers while skipping blanks:

• First, select your data range and then go to Data > From Table/Range to load your data into Power Query. If prompted, allow Excel to create a table around your data.
• Inside Power Query Editor, filter out any rows where your target column is blank. Click the dropdown arrow in the header of your column, uncheck (null), and click OK.
• Once blanks are excluded, go to Add Column > Index Column and choose From1 to start serial numbers from1, or choose From0 if you want zero-based indexing.
• Click Close & Load to return your fully numbered, blank-free data table back to Excel.

Power Query allows for easy re-refreshing as your source data changes, ensuring serial numbers automatically update. If your data structure is likely to change frequently, this approach provides stability and repeatability. However, setup may be more complex than formula or VBA methods for simple lists.

If you encounter issues, such as inability to load from a range, ensure that your data is organized without merged cells and formatted as a table. When using Power Query with very large amounts of data, performance is generally stable, but additional cleansing steps may be needed to prepare inconsistent input.

Each of the above methods can be selected according to your data volume and usage habits. The formula approach is quick and straightforward for small ranges, VBA fits bulk or repeated tasks, and Power Query excels for advanced users and changing datasets.

If you need further assistance, consider consulting Excel Help or the Kutools support documentation for more practical examples on numbering and data organization.