Every invoice needs a unique number that follows a consistent format, never duplicates, and ideally includes useful context like the year or client code. Doing this manually leads to skipped numbers, duplicates, and formatting inconsistencies. Here’s how to build an automatic system that generates the next number every time.
Method 1: Simple Sequential Number
If your invoices are listed in rows, the simplest approach generates the next number based on the previous row:
First invoice (A2)
=1001
Start your sequence at any number. 1001 is common — it avoids single-digit numbers that look unprofessional.
Next invoices (A3 and below)
=A2+1
Method 2: Year-Prefix Format (INV-2025-0001)
Add the year and zero-padding for a professional, sortable format:
="INV-"&TEXT(YEAR(TODAY()),"0000")&"-"&TEXT(ROW()-1,"0000")
Generates “INV-2025-0001”, “INV-2025-0002”, etc. ROW()-1 auto-increments based on the row number (assuming data starts in row 2). TEXT with “0000” zero-pads to 4 digits.
Result
| A | B | C | |
|---|---|---|---|
| 1 | Invoice # | Client | Amount |
| 2 | INV-2025-0001 | Acme Corp | $4,200 |
| 3 | INV-2025-0002 | Bright LLC | $1,850 |
| 4 | INV-2025-0003 | Chen Industries | $6,400 |
Method 3: MAX-Based Counter (Gap-Proof)
If rows might be deleted or reordered, using ROW() creates gaps. Instead, find the highest existing number and add 1:
="INV-"&TEXT(YEAR(TODAY()),"0000")&"-"&TEXT(MAX(--MID(A$2:A$1000,10,4))+1,"0000")
Extracts the numeric portion from existing invoice numbers, finds the maximum, and adds 1. Enter with Ctrl+Shift+Enter in older Excel.
Simpler alternative
Keep a single “Next Number” cell (e.g., Z1) that stores the counter. Each new invoice references it: ="INV-"&TEXT(YEAR(TODAY()),"0000")&"-"&TEXT(Z1,"0000"). After creating an invoice, manually increment Z1 — or use VBA to auto-increment it.
Method 4: Date-Based with Daily Reset
For high-volume businesses that want the date embedded in the number:
="INV-"&TEXT(TODAY(),"YYYYMMDD")&"-"&TEXT(COUNTIF(B:B,TODAY())+1,"00")
Generates “INV-20250407-01”, “INV-20250407-02” for multiple invoices on the same day. COUNTIF counts how many invoices already exist for today’s date.
Formula-based limitations
All formula approaches have a fundamental problem: they recalculate. If you open the file tomorrow, TODAY() changes and any date-based numbers change too. For production invoice systems, you must convert formula results to static values (Paste Special → Values) after generating each invoice, or use VBA that writes static values directly.
When Formulas Aren’t Enough
Formula-based numbering works for simple tracking. But a real invoicing system needs:
- Guaranteed uniqueness — no possibility of duplicates, even with multiple users
- Static numbers — once generated, the number never changes
- Auto-generation on entry — the number appears automatically when a new invoice is started
- Format flexibility — different prefixes for different invoice types (INV, CRN, PRO)
- Year-end rollover — automatically resetting the counter and updating the year prefix on January 1st
- PDF generation — creating a formatted invoice document from the data with one click
A custom VBA invoice system generates unique numbers, builds formatted invoices, exports to PDF, tracks payment status, and handles all the edge cases formulas can’t.
“Highly recommended, David was excellent throughout the entire process, taking the time to understand the brief and delivered exactly what was required.”
Bespoke Excel Client · Upwork
Need a Complete Invoice System in Excel?
Tell us about your invoicing workflow and we’ll build a tool that handles it all.
Start a Project