To generate invoice numbers automatically in Excel, base the next number on the highest number already used rather than on the row count. A MAX-based counter does not depend on row position: the next number is always one more than the highest number still in the list, so sorting the rows or deleting an older invoice does not change it. Mark a canceled invoice void rather than deleting it, and no number is ever issued twice. For a formatted number like INV-2026-0001, combine the year with a zero-padded sequence using TEXT.
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.
Download the finished workbook (.xlsx, free, no sign-up)
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-2026-0001)
Add the year and zero-padding for a professional, sortable format:
="INV-"&TEXT(YEAR(TODAY()),"0000")&"-"&TEXT(ROW()-1,"0000")
Generates “INV-2026-0001”, “INV-2026-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.
What each method produces
| A | B | C | |
|---|---|---|---|
| 1 | Invoice # | Client | Amount |
| 2 | INV-2026-0001 | Acme Corp | $4,200 |
| 3 | INV-2026-0002 | Bright LLC | $1,850 |
| 4 | INV-2026-0003 | Chen Industries | $6,400 |
Method 3: MAX-Based Counter
If rows might be deleted or reordered, don’t number by ROW(): deleting a row renumbers every invoice below it, so the next one takes the deleted invoice’s number, and sorting hands out the numbers by position. Instead, find the highest existing number and add 1:
="INV-"&TEXT(YEAR(TODAY()),"0000")&"-"&TEXT(MAX(--("0"&MID(A$2:A$1000,10,4)))+1,"0000")
Takes the four-digit sequence from each invoice number in column A, finds the highest, and adds 1. The "0"& turns blank cells into 0, so the range can run past the last invoice. Enter with Ctrl+Shift+Enter in older Excel. It reads four digits, so after INV-2026-9999 it offers INV-2026-10000 and then keeps offering it; the count also carries on into a new year rather than starting over.
Put this formula in a cell outside column A, such as E1, and copy its result into each new invoice row as a value (Paste Special → Values). Inside column A it would refer to its own cell, a circular reference. One caution: if you delete the newest invoice, its number becomes the next number again, so keep a canceled invoice’s number in column A and mark it void instead of deleting the row.
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(D:D,TODAY())+1,"00")
Generates “INV-20260407-01”, “INV-20260407-02” for multiple invoices on the same day. COUNTIF counts the invoices already dated today, so it needs each invoice’s date in a column of its own, column D here (in the table above, column B holds the client).
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.
The number is one part of the invoice. For the lines on it, see how to invoice materials and labor in Excel, and how to charge sales tax on parts but not labor.
Common questions
Will my invoice numbers change if I sort or delete rows?
Yes, if the number is a live formula based on row position. A MAX-based counter avoids reuse as long as you mark a canceled invoice void instead of deleting it, but the safest approach is to paste the number as a value once the invoice is issued, so it can never recalculate.
How do I add leading zeros to the sequence?
Wrap the number in TEXT: =TEXT(A2, "0000") turns 47 into 0047. The result is text, not a number, which is correct for an invoice reference.
Can I restart the numbering each year?
Yes. Base the MAX on only the current year’s invoices, then combine it with the year prefix, so January starts again at 0001 without ever colliding with last year’s numbers.
Numbering invoices is the easy part
A MAX-based counter solves the numbering. What it does not solve is everything around it: keeping client and product details straight, working out the margin on a job, knowing which invoices are unpaid, and getting a tidy PDF to the customer.
EG Quote & Invoice does all of that in one workbook. Quote a job, convert it to an invoice, track payments and overdue balances, and send it as a PDF or email. The free edition has no limits and no expiry.
Read more: tracking invoices and payments in Excel, free quote and invoice software for Excel, and invoice templates by trade.
See EG Quote & InvoiceWhen 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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.