Function Problems

How to Create Sequential Table Numbers with Duplicate Rows in Excel

Huda QurayshiHuda Qurayshi Nov 11, 2026 869 views

Question details

The user needs to generate sequential labels (e.g., Table 1, Table 2) that increment for new items but repeat the same label when a table title or data spans multiple rows.

How to Create Sequential Table Numbers with Duplicate Rows in Excel
Product
Excel
Device & OS
not provided
Scenario
Organizing tabular data where grouped records span multiple rows and require a single, consistent sequential identifier for each group.
Observed behavior
Standard auto-fill increments the number on every row, failing to account for duplicate rows that belong to the same table or group.
Before you start

Ensure your dataset is sorted by the primary identifier column so that all rows belonging to the same table are grouped consecutively.

Solution 1Recommended

Use an IF and COUNTIF Formula in a Helper Column

Use a logical formula to check for changes in the identifier and apply a new or repeated label accordingly.

By utilizing a helper column, you can combine the IF and COUNTIF functions to evaluate whether a row belongs to a new table or is a continuation of the previous one. This dynamically assigns the correct sequential label.

1
Identify the source column

Locate the column containing your source identifiers or table titles. For this example, assume they are in Column A, starting from row 2.

2
Select a helper cell

Click on a blank cell in an adjacent helper column, such as B2, to begin entering your formula.

3
Enter the sequential formula

Type the formula =IF(A2<>A1,"Table "&COUNTIF($A$2:A2,A2),B1) and press Enter. This formula assigns a new sequential label when the identifier changes and repeats the previous label for continuation rows.

4
Apply to the entire dataset

Click the small square at the bottom-right corner of cell B2 and drag it down to the end of your dataset to apply the formula to all rows.

Use an IF and COUNTIF Formula in a Helper Column
Formula Tip: Ensure the absolute reference ($A$2) is used properly. This anchors the starting point of your count range so the formula functions correctly as it is dragged down.
Advanced Data Management

Easily Manage Sequential Numbering with WPS Spreadsheet

WPS Spreadsheet offers robust formula support, including advanced IF and COUNTIF functions, making it effortless to generate dynamic, sequential labels for multi-row data blocks.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your tables.
  2. 2. Add a helper column: Insert a new column next to your data to act as the destination for your sequential numbers.
  3. 3. Insert the logic formula: Type your IF and COUNTIF formula into the first cell of the new column and press Enter.
  4. 4. Auto-fill the rows: Double-click the fill handle in the bottom-right corner of the cell to automatically populate the rest of the column.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Free, lightweight, and optimized for fast processing of large datasets.Intuitive interface with powerful built-in data sorting and grouping tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my sequential number not increment properly?

Ensure that your absolute references (the dollar signs in $A$2) are correct. This locks the starting point of the range so the COUNTIF function can accurately evaluate the data up to the current row without shifting the start point.

Can I hide the original identifier column?

Yes. Once your sequential table numbers are successfully generated in the helper column, right-click the original column's letter header and select 'Hide' to keep your spreadsheet view clean.

How do I change 'Table' to another word in the formula?

You can easily customize the prefix by changing the text inside the quotation marks in the formula. For example, simply replace "Table " with "Group ", "Item ", or "Figure ".