How to Create Sequential Table Numbers with Duplicate Rows in Excel
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.

- 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.
Ensure your dataset is sorted by the primary identifier column so that all rows belonging to the same table are grouped consecutively.
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.
Locate the column containing your source identifiers or table titles. For this example, assume they are in Column A, starting from row 2.
Click on a blank cell in an adjacent helper column, such as B2, to begin entering your 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.
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.

Alternative Method for Handling Duplicate Subtractions
A slightly modified formula setup to explicitly calculate and account for duplicate entries.
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. Open your dataset: Launch WPS Spreadsheet and open the document containing your tables.
- 2. Add a helper column: Insert a new column next to your data to act as the destination for your sequential numbers.
- 3. Insert the logic formula: Type your IF and COUNTIF formula into the first cell of the new column and press Enter.
- 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.

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 ".




