Function Problems

Automatically Number Rows in WPS Spreadsheet When Column B Is Filled

Rana GarciaRana Garcia Nov 12, 2026 868 views

Question details

Create sequential numbers in one column only when the corresponding cells in another column contain entries.

Automatically Number Rows in WPS Spreadsheet When Another Column Is Filled
Product
WPS Spreadsheet
Device & OS
not provided
Scenario
The user enters data in column B and wants column A to number completed rows automatically while leaving empty rows blank.
Observed behavior
Numbering should remain consecutive across populated rows, even when blank rows appear between entries.
Before you start

Confirm that your data begins in row 1 and that column B is the column used to determine whether a row has an entry. Adjust the cell references in the formula if your data starts in a different row or uses another source column.

Solution 1Recommended

Use IF and COUNTIF to sequentially number only filled rows

Use this option when column A should show consecutive numbers for rows where column B contains a value, while blank rows in column B remain unnumbered.

The COUNTIF portion counts nonblank cells from the first data cell through the current row. The IF portion returns a blank result when the matching cell in column B is empty.

1
Select the first numbering cell

In WPS Spreadsheet, click cell A1, or click the first cell in your numbering column that aligns with the first data row in column B.

2
Enter the conditional numbering formula

Type =IF(B1<>"",COUNTIF($B$1:B1,"<>"),"") and press Enter. Replace B1 with the first cell in your data-entry column if needed.

3
Copy the formula down the column

Select the formula cell, then drag the fill handle from the lower-right corner down through the rows you want to number. WPS Spreadsheet updates each row reference automatically.

4
Add entries in column B

Enter a value in any cell in column B. The matching cell in column A displays the next sequential number; rows with empty column B cells stay blank.

Use IF and COUNTIF to sequentially number only filled rows
Sequential numbering is preserved: The formula counts completed entries rather than worksheet row positions, so later filled rows continue the sequence after blank rows.
WPS Spreadsheet formula tools

Create Conditional Row Numbers Easily in WPS Spreadsheet

WPS Spreadsheet lets you use IF, COUNTIF, and ROW formulas to number records automatically based on whether another column contains data. Enter the formula once and fill it down to keep your list updated as new entries are added.

  1. 1. Open your worksheet: Open the file in WPS Office and select WPS Spreadsheet from the workbook tabs.
  2. 2. Choose the numbering column: Click the first cell in the column where you want automatic numbers to appear, such as A1.
  3. 3. Enter the sequential formula: Enter =IF(B1<>"",COUNTIF($B$1:B1,"<>"),"") and press Enter.
  4. 4. Fill the formula down: Use the fill handle on the selected cell to copy the formula through the required rows.
  5. 5. Add or edit source entries: Type values in column B and verify that the corresponding cells in column A update with sequential numbers.
Automatically numbers only rows with entries in the selected source columnSupports IF, COUNTIF, and ROW formulas for conditional numberingCompatible with Microsoft Excel XLS and XLSX worksheet formatsUses a familiar spreadsheet interface for copying formulas and filling ranges
microsoft office alternative - wps office

Frequently Asked Questions

How do I number rows only when a cell in another column is not blank?

In the first numbering cell, enter =IF(B1<>"",COUNTIF($B$1:B1,"<>"),"") and fill the formula down. Change B to the column that contains your required entries.

Why does my automatic numbering skip blank rows but continue with the next number?

The COUNTIF formula counts only nonblank cells in the source range, from $B$1 to the current row. Empty source cells return a blank result and do not increase the count.

Can I show the worksheet row number instead of a sequential number?

Yes. Enter =IF(B1<>"",ROW(),"") in the target cell and fill it down. It displays the sheet row number whenever the corresponding column B cell has a value.

How do I start conditional numbering from row 2 instead of row 1?

In A2, use =IF(B2<>"",COUNTIF($B$2:B2,"<>"),"") and copy it down. Using $B$2 sets row 2 as the start of the sequence.