How to Automatically Create Sequential IDs (CR001, CR002) in Excel
Question details
The user wants to automatically generate sequential tracking IDs (such as CR001, CR002, CR003) in a specific column whenever a description or data is entered in an adjacent new row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a dynamic tracking or numbering system where row IDs automatically format and increment upon data entry.
- Observed behavior
- Instead of typing IDs manually, the goal state is to have Excel automatically display formatted sequential alphanumeric IDs as soon as adjacent cells are populated.
Determine whether your workflow requires a physical text string generated by a formula, or if changing the visual format of standard numbers is sufficient for your tracking system.
Use an IF and TEXT Formula for Dynamic IDs
This method automatically generates a text-based sequential ID only when the adjacent cell contains data, keeping unused rows completely blank.
By combining the IF, TEXT, and ROW functions, Excel can calculate the current row number and format it with your desired text prefix (e.g., "CR") and leading zeros.
Click on cell A2 (assuming your header is in row 1 and descriptions will be typed in column B).
Type the formula: `=IF(B2="","","CR"&TEXT(ROW()-ROW($A$1),"000"))` and press Enter. This tells Excel to leave the cell blank if B2 is empty, or to calculate the row offset and format it if B2 has text.
Click the small square at the bottom-right corner of cell A2 and drag it down to apply this formula to all the rows you plan to use.
Apply Custom Number Formatting
This approach is ideal if you want to type simple numbers (1, 2, 3) but have Excel display them with the prefix and leading zeros visually.
Use an Increment Formula from the Previous Row
Use this alternative formula if you want to explicitly parse and increment the alphanumeric ID from the row immediately above.
Automate Data Entry and Formatting in WPS Spreadsheet
WPS Spreadsheet provides robust support for logical functions, text manipulation, and custom cell formatting. You can effortlessly set up sequential alphanumeric tracking IDs using the exact same methods.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
- 2. Select your ID column: Click the cell where you want your first dynamic ID to appear.
- 3. Apply the formula: Paste the `=IF()` and `TEXT()` formula into the formula bar.
- 4. Drag to automate: Use the fill handle to copy the formula down, instantly automating your data tracking.
Frequently Asked Questions
Can I change the 'CR' prefix to a different word or abbreviation?
Yes. In the formula `=IF(B2="","","CR"&TEXT(ROW()-ROW($A$1),"000"))`, simply replace "CR" with your preferred text, such as "ID" or "INV". The same logic applies to the custom number format; you can change `"CR"000` to `"INV"000`.
How do I change the number of digits to four (e.g., CR0001)?
To display four digits, update the TEXT function to include four zeros. Your formula should look like `=IF(B2="","","CR"&TEXT(ROW()-ROW($A$1),"0000"))`. For custom formatting, use the format code `"CR"0000`.
What happens if I delete a row using the ROW() formula method?
The ROW() function calculates the result dynamically based on the current row's physical location. If you delete a row, the formulas in the rows below will automatically recalculate and close the numbering gap, ensuring your sequential IDs remain perfectly in order.
