Auto Invoice Number in Excel: Step-by-Step

This guide shows you how to automatically generate invoice numbers in Excel, so every new invoice gets a unique, sequential number without manual typing. You’ll set up a simple formula and a small settings area that you update once per invoice. No macros or programming knowledge is needed.

What You’ll Need

Microsoft Excel (2010 or newer; the steps work in Excel for Microsoft 365, Excel 2016, 2019, and 2021).
An invoice workbook where you want the numbers to appear.
Basic familiarity with entering formulas and cell references.

Step-by-Step

Decide on your invoice number format. Common formats include:
- Simple sequential: INV-001, INV-002, …
- Date-based: 2025-001, 2025-002, …
- With customer or project code: ACME-001, ACME-002, …
For this guide, we’ll use INV-001, INV-002, etc., but you can adapt the formula.
Create a hidden or separate “Settings” area. Pick a cell that won’t be printed, for example, cell Z1 on the same sheet, or a new sheet named “Settings”. In cell Z1, type Last Invoice Number and in cell Z2, type 0. This cell will store the last used number. You’ll update it manually (or with a simple macro if you want full automation).
Write the invoice number formula. In the cell where you want the invoice number to appear (for example, cell B1 on your invoice template), enter:
```
=TEXT(Z2+1,"000")
```
This displays the next number as a three-digit text (e.g., 001). To include a prefix, use:
```
="INV-"&TEXT(Z2+1,"000")
```
If you want a date-based number, use:
```
=TEXT(TODAY(),"YYYY")&"-"&TEXT(Z2+1,"000")
```
Copy the formula to every invoice in your workbook. If you use a template sheet that you duplicate for each new invoice, the formula will automatically reference the same Z2 cell. If you have multiple invoice sheets, you need to update Z2 on each sheet, or better, use a single “Settings” sheet and reference it like =Settings!$Z$2.
Update the “Last Invoice Number” after each invoice. After you finalize an invoice, go to cell Z2 and change the value to the number you just used. For example, if the invoice shows INV-001, set Z2 to 1. The next time you open a new invoice, the formula will show INV-002.
Optional: Automate the update with a simple macro. If you want the number to update automatically when you open a new invoice, you can use a tiny VBA macro. Press Alt+F11 to open the VBA editor, insert a new module, and paste:
```
Sub NextInvoice()
    Range("Z2").Value = Range("Z2").Value + 1
End Sub
```
Then assign this macro to a button on your invoice template. When you click it, the number increments and the formula updates. (Note: You’ll need to save the workbook as a macro-enabled file, .xlsm.)
Test your setup. Enter a few test invoices to confirm the numbers increment correctly. Check that the prefix and leading zeros appear as expected.

Common Mistakes

Forgetting to update the “Last Invoice Number” cell. If you don’t increment it, you’ll get duplicate invoice numbers.
Using a formula that references a cell that gets overwritten. If you copy a new invoice over the settings cell, the formula breaks.
Not using absolute references. When copying the formula to other cells, use $Z$2 to keep the reference fixed.
Mixing text and numbers incorrectly. The TEXT function converts numbers to text, so you can’t perform arithmetic on the result. That’s fine for display, but don’t try to add to it.
Using a format that doesn’t match your needs. If you use a date-based number, ensure the year updates correctly. The TODAY() function updates every day, so an invoice created on Jan 1 will have a different number than one created on Dec 31.

Tips

Use a dedicated “Settings” sheet to keep your invoice template clean. Put the last number in a fixed cell, e.g., Settings!$B$2.
Add data validation to the settings cell to prevent accidental text entry (Data tab > Data Validation > Whole number > minimum 0).
If you need to track multiple series (e.g., different clients), use separate settings cells for each series and reference them accordingly.
For a more robust solution, consider using a table with a “Last Number” column and a formula that references the previous row’s number.
Always keep a backup of your workbook before adding macros.

Troubleshooting

Invoice number shows as “#VALUE!” – This usually means the settings cell contains text or is empty. Check that Z2 (or your settings cell) contains a number.
Number doesn’t increment. Make sure you’ve updated the settings cell after each invoice. If you’re using a macro, verify the macro is assigned to the button and that macros are enabled.
Leading zeros disappear. If you see 1 instead of 001, your formula might not be using the TEXT function. Use =TEXT(Z2+1,"000") to force three digits.
Formula works on one sheet but not another. Check that the reference to the settings cell is correct. If you’re referencing a different sheet, use =Settings!$Z$2.
Macro doesn’t run. Ensure the workbook is saved as .xlsm and that you’ve enabled macros in the Trust Center settings.

Frequently Asked Questions

Can I use this method without macros?

Yes, the core method uses only a formula and a manual update to the settings cell. Macros are optional for full automation.

How do I reset the invoice numbers for a new year?

Simply change the settings cell back to 0 on January 1. If you want the year in the number, the TODAY() function will automatically use the new year.

What if I need to skip a number or use a custom sequence?

You can manually edit the settings cell to any number you want. The formula will always show that number plus one.

Can I use letters or special characters in the prefix?

Yes, you can include any text in the formula. For example, ="INV-"&TEXT(Z2+1,"000") works fine. Just be careful with quotes – use double quotes around text.

Will the formula work if I share the workbook with others?

Yes, as long as the settings cell is updated correctly. If multiple people edit the same file simultaneously, there could be conflicts, so it’s best to have one person manage the numbering.

Is there a way to prevent duplicate numbers if two people create invoices at the same time?

Not with a simple formula. For multi-user environments, consider using a shared database or a more advanced system. In Excel, you could use a macro that locks the file while updating the number, but it’s not foolproof.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →