Function Problems

How to Automatically Generate a Yearly Resetting Sequence in Excel

Camila Milosovich Oct 8, 2026 869 views

Question details

The user needs to automatically generate sequential numbers that reset annually (e.g., 001-24, 002-24, 001-25) when data is entered into an adjacent column.

How to Automatically Generate a Yearly Resetting Sequence in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating automated invoice, document, or record numbers that reset automatically at the start of a new year based on a populated adjacent cell.
Observed behavior: The goal is for the spreadsheet to automatically produce a formatted string (like 001-24) based on the entry date and data presence, seamlessly resetting the count when the year changes.

Before you start

Ensure you have a column dedicated to storing the date (e.g., Column A) and another column indicating the presence of a record (e.g., Column B) to trigger the sequence.

Solution 1Recommended

Use SUMPRODUCT and TEXT Functions to Create the Sequence

This method combines IF, TEXT, SUMPRODUCT, and YEAR functions to count entries conditionally and format the sequence with a 2-digit year suffix.

This approach evaluates the date column to determine the current year and counts how many times a record has been entered for that specific year up to the current row.

Prepare your columns

Organize your data with dates in Column A and your data entries (or a check value) in Column B.

Enter the formula

Click on cell C2 (or your target sequence column) and enter the following formula: =IF(B2="","",TEXT(SUMPRODUCT(($B$2:B2<>"")*(YEAR($A$2:A2)=YEAR(A2))),"000")&"-"&TEXT(A2,"yy")).

Apply to the column

Press Enter, then click the fill handle in the bottom-right corner of cell C2 and drag it down to apply the formula to the rest of the column.

Use SUMPRODUCT and TEXT Functions to Create the Sequence

Formula Customization: If you want a 4-digit sequence (e.g., 0001), change the "000" in the TEXT function to "0000".

Efficient Spreadsheet Management

Create Advanced Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT and conditional formatting, allowing you to easily generate automated, resetting sequences exactly as you would in Microsoft Excel.

1. Download and Install: Download WPS Office from the official website and open WPS Spreadsheet.
2. Open Your Workbook: Create a new blank workbook or open your existing .xlsx file containing your dates and records.
3. Insert the Formula: Paste the combined SUMPRODUCT and TEXT formula into your target cell, and drag the fill handle to apply it across your dataset.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Intuitive interface for managing complex array formulas effortlessly.Free to use, lightweight, and fast on all major platforms.Built-in data validation and advanced text formatting functions.

microsoft office alternative - wps office

Frequently Asked Questions

Can I place the date in a different column?

Yes, you can adjust the formula by replacing all instances of 'A2' and '$A$2:A2' with the reference to your actual date column (e.g., 'D2' and '$D$2:D2').

How do I change the sequence format to show the year first?

Modify the formula to concatenate the year first. For example: =IF(B2="","",TEXT(A2,"yy")&"-"&TEXT(SUMPRODUCT(($B$2:B2<>"")*(YEAR($A$2:A2)=YEAR(A2))),"000")).

Why is the formula returning a #VALUE! error?

This usually happens if there is text in the date column instead of a valid date. Ensure your date column (Column A) contains proper date formats recognized by the spreadsheet software.

Excel & Data Problems