How to Generate Sequential Receipt Numbers in Excel
Question details
The user needs to automatically generate sequential numbers for receipts or raffles as new data records are entered into a worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a tracking sheet for receipts, invoices, or raffle tickets where a new sequential ID must appear automatically when a corresponding data cell is populated.
- Observed behavior
- The worksheet must begin numbering with a specific starting value and automatically generate subsequent numbers only when corresponding data cells in adjacent columns are filled.
Determine the starting number for your receipts or raffle tickets and identify which column will trigger the automatic numbering when data is entered.
Use the SEQUENCE Function for Automatic Numbering
This method uses a dynamic array formula to automatically populate sequential numbers as new records are added to adjacent columns, saving time on manual data entry.
The SEQUENCE function combined with COUNTA creates a dynamic array that expands automatically as you add new items to your tracker.
Type your initial receipt or raffle number in the first cell of your numbering column, for example, cell A2.
In the cell directly below it (A3), enter the following formula: =SEQUENCE(COUNTA(B2:B1000),1,A2+1). Do not manually drag or fill this formula down.
Begin entering your data in column B, starting from cell B2. As you populate each cell in column B, the next sequential number will automatically appear in column A.
Use the Fill Handle to Create a Number Series
A straightforward, manual method to generate sequential numbers by dragging the fill handle. This is best if you need a fixed number of receipts generated all at once.
Generate Sequential Numbers Easily with WPS Spreadsheet
You can seamlessly manage receipt numbering, generate dynamic sequence arrays, and track business data using WPS Spreadsheet, a highly compatible and free office suite.
- 1. Create a new workbook: Open WPS Spreadsheet and create a new blank workbook for your receipt tracker.
- 2. Set the starting number: Type your starting receipt number in cell A2.
- 3. Apply the auto-numbering formula: Enter the formula =SEQUENCE(COUNTA(B2:B1000),1,A2+1) in cell A3 to auto-generate sequence numbers as you fill out column B.
Frequently Asked Questions
Can I add a prefix like 'INV-' to my sequential receipt numbers?
Yes, you can use custom cell formatting to add a prefix without changing the actual numerical value. Select your numbering column, press Ctrl+1 to open Format Cells, go to Custom, and enter "INV-"0 as the type. The SEQUENCE formula will still work normally.
Why does the SEQUENCE function result in a #NAME? error?
The #NAME? error typically occurs if you are using an older version of Excel that does not support dynamic array functions. The SEQUENCE function is only available in Microsoft 365, Excel 2021, and newer versions, or modern alternatives like WPS Office.
How do I make the numbering stop or pause if there is a blank row in the data?
If you want numbering to depend on the exact adjacent cell rather than a total count of the range, you can use a traditional IF formula instead of SEQUENCE. In cell A2, type =IF(B2<>"", 1, ""), and in A3, type =IF(B3<>"", MAX($A$2:A2)+1, ""). Drag this formula down your column.
