Function Problems

How to Generate Consecutive BI Serial Numbers in Excel

Maira Mehtab Sep 27, 2026 871 views

Question details

The user wants to create custom, sequential identifiers with a specific text prefix (such as BI-001) and repeat each sequential number across a defined group of rows.

Product: Excel
Device & OS: not provided
Scenario: Setting up a customized tracking list, inventory log, or billing sheet where specific prefixes and grouped row numbering are required.
Observed behavior: The user needs a dynamic or formula-based approach to output formatted strings like BI-001 and BI-002 that increment automatically based on group size.

Before you start

Determine the exact starting row for your data and decide on the prefix and numeric format (e.g., three digits '000' or four digits '0000') you need for your serial numbers.

Solution 1Recommended

Using the QUOTIENT and ROW Formula

Use a combination of ROW, QUOTIENT, and TEXT functions to generate custom sequential numbers that can repeat for a set number of rows.

This formula uses the current row number subtracted by the starting row to determine when to increment the sequence. The TEXT function is then used to format the output with the required leading zeros.

Select the starting cell

Click on the cell where you want the first serial number to appear (for example, A2).

Enter the sequence formula

Type the formula: ="BI-"&TEXT(QUOTIENT(ROW()-ROW($A$2),2)+1,"0000") into the formula bar.

Apply the formula to other rows

Press Enter, then click and drag the fill handle (the small square at the bottom-right corner of the cell) down to populate the rest of the column.

Adjusting Sequence Parameters: Replace '2' in the formula with your desired group size if you need the number to repeat across more rows. Change '0000' to '000' if you want a 3-digit number like BI-001.

Solution 2

Using Dynamic Array Formulas

Leverage modern dynamic array functions to spill the serial numbers automatically without needing to manually drag the fill handle.

WPS Spreadsheet Solution

Easily Generate Custom Serial Numbers in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text and math functions like ROW, QUOTIENT, TEXT, and SEQUENCE. You can effortlessly generate and format custom sequential numbers for your data tracking needs without compatibility issues.

1. Open your worksheet: Launch WPS Spreadsheet and open the document where you need to add custom serial numbers.
2. Enter the formula: Select your starting cell and type the formula ="BI-"&TEXT(QUOTIENT(ROW()-ROW($A$2),2)+1,"0000").
3. Apply to rows: Press Enter, then click and drag the small fill handle at the bottom-right of the cell down to fill the series automatically.

Fully compatible with Microsoft Excel formulas and functions.Lightweight software with fast processing for large datasets.Built-in data formatting tools for custom prefixes and sequences.Free to use with a familiar, easy-to-navigate user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the prefix from BI- to something else?

In the formula, simply replace the text inside the first quotation marks. For example, change it to ="INV-"&TEXT(...) to generate invoice numbers.

Why does my sequence start at 0 instead of 1?

This happens if the starting cell reference in the ROW function is incorrect. Ensure that $A$2 in the formula ROW()-ROW($A$2) precisely matches your first row of data.

Can I use auto-fill without complex formulas?

Yes, if you do not need numbers to repeat across groups of rows (e.g., 2 rows sharing the same number), you can simply type 'BI-001' in the first cell, select it, and drag the fill handle down. The spreadsheet will automatically increment the number.

Excel & Data Problems