Formula Errors

How to Number Repeated Values Automatically in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to generate a running count sequence in a new column that increments every time a specific value repeats in a reference column (e.g., A 1, A 2, B 1, C 1, A 3).

Product: Excel
Device & OS: not provided
Scenario: Tracking and categorizing recurring items or phases in a dataset where each unique entry needs its own independent sequence number based on its occurrence frequency.
Observed behavior: The goal is to automatically calculate the occurrence instance of each value dynamically as the list goes down, rather than generating a static total count.

Before you start

Ensure your dataset is organized in a clear, continuous column without merged cells, and identify an empty adjacent column to host your new sequence numbers.

Solution 1Recommended

Use an Expanding Range with the COUNTIF Function

The most efficient way to number repeated values is by applying the COUNTIF function with a mixed reference, creating an expanding range.

By anchoring the first cell reference while leaving the second relative, the formula range expands as you drag it down. This dynamically counts how many times a value has appeared up to the current row, effectively restarting the sequence for new values.

Select the starting cell

Click on the cell where you want the first sequence number to appear (for example, cell B2), assuming your reference data starts in cell A2.

Enter the COUNTIF formula

Type the formula =COUNTIF($A$2:A2, A2) into the selected cell. The absolute reference ($A$2) locks the starting point, while the relative reference (A2) allows the range to expand.

Apply the formula

Press the Enter key. The cell should return '1' for the first occurrence of that item.

Copy the formula down the column

Click the fill handle (the small square at the bottom-right corner of the cell) and drag it down to apply the formula to the rest of your data. The sequence will now increment for repeated values.

Shortcut for multiple cells: To apply this to a large dataset instantly, select the entire range in your destination column, type =COUNTIF($A$2:A2, A2), and press Ctrl+Enter to fill all selected cells at once.

Work with Data Easier

Easily Manage Data and Formulas with WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced formulas like COUNTIF and offers an intuitive interface for managing large datasets. It flawlessly handles Excel formulas and allows you to automate sequences without complicated workarounds.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Input the formula: Select the empty cell next to your first entry and type =COUNTIF($A$2:A2, A2).
3. Auto-fill the column: Press Enter, then simply double-click the green fill handle at the corner of the cell to instantly populate the formula down to the end of your data.

100% compatibility with Microsoft Excel (.xlsx) formats and functions.Built-in smart fill features to quickly apply formulas across thousands of rows.Free, lightweight, and easy to use on both Windows and Mac.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIF formula return the same number for every row?

This usually happens if you locked both parts of the range reference (e.g., $A$2:$A$100). Ensure the second part of the range is relative (e.g., $A$2:A2) so the range can expand as the formula is copied down.

Can I combine the repeated value text with the sequence number in the same cell?

Yes. You can use the ampersand (&) operator to combine them. For example, entering =A2 & " " & COUNTIF($A$2:A2, A2) will output combined results like 'A 1' or 'B 2'.

Will this formula work if my list is not sorted?

Yes, the expanding range COUNTIF formula calculates the running count strictly based on the order of appearance. It works perfectly even if the repeated values are scattered randomly throughout the column.

What happens if I insert a new row in the middle of my data?

If you insert a new row, simply copy the formula into the new blank cell. The running counts for the rows below will automatically recalculate to include the newly inserted entry.

Excel & Data Problems