Function Problems

How to Count and Number Blank Cells in Excel (Sequential Formula)

Phi Hung Vo Oct 22, 2026 869 views

Question details

The user needs to count the total number of blank cells in a data range and assign sequential, incremental numbers to consecutive empty cells, restarting the count when a non-blank cell appears.

How to Count and Number Blank Cells in an Excel Column

Product: Excel
Device & OS: not provided
Scenario: Analyzing intervals between occurrences in a dataset, such as counting the number of skipped lottery draws between identical numbers.
Observed behavior: The goal state is to have blank cells output an ascending numeric sequence that resets automatically when data is present in the reference column.

Before you start

Ensure your data does not contain hidden spaces or zero-length strings, as Excel formulas may not treat these as truly blank cells. Use the TRIM or CLEAN function if your data was imported from an external source.

Solution 1Recommended

Method 1: Number Consecutive Blank Cells Sequentially

Use a combination of IF functions to assign an incremental number to empty cells and reset the count when a non-blank cell appears.

This method uses a helper column to track the sequence. It checks if the adjacent cell is empty; if it is, it adds 1 to the count above it. If it contains data, it resets the counter.

Set up the helper column

Assuming your data is in column A starting at A2, click on cell B2 in an adjacent blank column. Ensure cell B1 is empty or contains a text header.

Enter the IF formula

Type the formula =IF(A2="", B1+1, 0) and press Enter. If you want non-blank cells to show empty instead of a zero, use =IF(A2="", B1+1, "").

Apply the formula down the column

Click on cell B2, then click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to apply the sequence to your entire dataset.

Formula explanation: The formula evaluates A2. If A2 is blank, it takes the value from B1 and adds 1. When the formula is copied down, it continues adding 1 until it hits a cell in column A with data, triggering the 'value if false' condition to reset the count to 0.

Solution 2

Method 2: Count Total Blank Cells Using COUNTBLANK

Use the built-in COUNTBLANK function to easily find the overall total number of empty cells in a selected range without sequential numbering.

Solution 3

Method 3: Calculate Total Blanks Using ROWS and COUNTA

An alternative approach to count empty cells by subtracting the number of non-empty cells from the total number of rows in the range.

Smart Data Management

Count and Number Blank Cells Easily in WPS Office

WPS Spreadsheet provides seamless support for all standard Excel functions including IF, COUNTBLANK, and ISBLANK. You can easily track sequences, manage lottery draws, or analyze data gaps using the exact same formulas in a lightweight, user-friendly interface.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data gaps.
2. Apply the sequence formula: In an adjacent blank column, enter =IF(A2="", B1+1, 0) to start numbering the blank cells.
3. Drag to fill: Double-click the fill handle at the bottom right of the cell to automatically apply the sequential formula down the entire column.

100% compatible with Microsoft Excel formulas and .xlsx file formatsLightweight software with rapid processing for large datasetsBuilt-in data analysis, helper columns, and conditional formatting toolsFree to use with a familiar tabbed interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why is COUNTBLANK returning an incorrect number for my empty cells?

Cells containing spaces, hidden characters, or formulas returning an empty string ("") are sometimes not treated identically by all spreadsheet functions. If COUNTBLANK is inaccurate, try using =COUNTIF(A2:A100, "") to capture cells containing empty strings.

How do I highlight the blank cells instead of numbering them?

You can use Conditional Formatting. Select your range, navigate to Conditional Formatting > New Rule, choose 'Format only cells that contain', set the condition to 'Blanks', and apply a fill color to make the empty cells stand out visually.

Can I restart the sequence from a specific number other than 1?

Yes. In the IF formula =IF(A2="", B1+1, X), you can change the starting logic or the 'value if false' condition (represented by X) to reset to any base number you prefer.

Excel & Data Problems