How to automatically generate invoice number in Excel?

Generating invoice numbers in Excel often poses a challenge: since invoice numbers are meant to be unique and sequential, manually entering them leaves room for errors such as forgetting the last used number or accidentally duplicating numbers. This can lead to confusion in invoice tracking, data inaccuracies, and potential issues with bookkeeping or compliance. Automating the generation of invoice numbers in Excel not only saves time but also ensures accuracy and consistency in your invoicing process.

This article explores three practical ways to generate automatic invoice numbers in Excel. Each solution offers different benefits and is suitable for specific scenarios, helping you streamline your workflow and minimize mistakes.

• Add a automatically invoice number generator in Excel
• Add invoice number with KTE’s Insert Sequence Number feature
• Use Excel formulas to generate sequential invoice numbers

Add a automatically invoice number generator in Excel

This solution uses VBA to automatically increment an invoice number each time you open the specified workbook. It suits cases where you create and track invoices within a single file. However, if you forget to save before closing, or work across multiple workbooks, there's a risk of duplication. Below is a step-by-step guide with details and important reminders.

1. Start by opening the workbook you want to use for your invoices, or create a new one if you are just beginning.

2. Select a blank cell to store your invoice starting number. For example, enter the starting invoice number, such as 10000, into cell D2. This cell will serve as a reference for the invoice numbers you generate.

3. Press Alt + F11 to open the Microsoft Visual Basic for Applications window. In the VBAProject pane (usually on the left), expand the list for your current workbook and double-click ThisWorkbook to open its code window. (Note: For example, if your file is named Book1, find ThisWorkbook under VBAProject (Book1).)

4. Copy and paste the following VBA code into the code window that appears.

VBA: Invoice Number Generator

Private Sub Workbook_Open()
Range("D2").Value = Range("D2").Value + 1
End Sub

Note: Be sure to adjust D2 in the code to match the cell you chose in step2 if you are using a different location for your starting number.

5. If you want to create more advanced or readable invoice numbers (for example, by adding dates, company names, or random elements), you can use Excel formulas instead of only whole numbers. Below are some commonly used formulas and their effects:

Practical Tips:
(1) Replace D2 in the above formulas with your actual starting number cell if it is different.
(2) Substitute CompanyName with your organization or business name, or any other desired prefix.
(3) Enter the formula in the cell where you want your invoice number to appear, then press Enter to generate an automatic invoice number.
(4) For batch creation, you can drag the fill handle to copy formulas and generate multiple unique invoice numbers quickly.

Precautions and Reminders:
- Ensure macros are enabled in your Excel settings, as the VBA code will not function otherwise.
- Remember to save your workbook every time before closing; otherwise, the next invoice number might not be updated correctly, leading to possible duplication.
- Storing your starting number cell in a hidden or protected sheet can further reduce the risk of accidental edits.

6. To preserve the functionality of the macro and avoid losing your setup, save the workbook as a macro-enabled file.
A. In Excel2013 or later: Click File > Save (or Save As) > Computer > Browse.
B. In Excel2007 or2010: Click the File menu or Office Button; then select Save (or Save As).

7. In the Save As dialog, please:

(1) Choose a file name in the File name box.
(2) Select Excel Macro-Enabled Workbook (*.xlsm) in the Save as type dropdown list.
(3) Decide on a storage location for your file.
(4) Click Save to complete.

With this setup finalized, each time you open your invoice workbook, the invoice number will automatically increase by1. Do ensure that you always save your workbook when finished to maintain correct sequence and prevent number overlap or reset. This method is best for single-workbook use; for operations across multiple files or by multiple users, consider alternative methods below.

Troubleshooting: If macro security warnings appear, check Excel's Trust Center settings to allow macros. If the invoice number doesn't increment as expected, ensure the code is in the right location and the reference cell matches.

Add invoice number with KTE’s Insert Sequence Number feature

While the above VBA method works well within a dedicated workbook, it can be limiting when you need to generate unique and consecutive invoice numbers across different files or share the process with colleagues. For greater flexibility and ease-of-use, consider the Kutools for Excel‘s Insert Sequence Number feature. This approach works seamlessly across multiple workbooks, and helps to avoid number overlaps even if you forget to save your file before closing – a common pitfall with basic VBA solutions.

1. Click on the cell where you want the invoice number to appear, then go to Kutools > Insert > Insert Sequence Number.

2. In the Insert Sequence Number dialog box, click New, then customize the sequence to meet your needs as follows (refer to the image above):
(1) Enter a name in Sequence name, such as Invoice Number.
(2) Set Increment to 1 to ensure numbers are consecutive.
(3) Optionally add your company name or preferred text in the Prefix (optional) field.
(4) Type the desired starting number, such as 10000 or 1, in Start Number.
(5) Specify the number of digits in No. of digits (for example, 6 for 000001 formatting).
(6) Click Add to save your new sequence for future use.

Tips: You can set suffixes, adjust digit width, and even create several different numbering schemes for different types of invoices or documents.

3. With your custom sequence highlighted, click Fill Range to insert the next unique number directly into your selected cell(s), then click Close.

Whichever workbook you're working in, using the Insert Sequence Number feature will always insert the next unique number in the sequence – no manual recordkeeping needed. This greatly reduces the chance of errors or duplication, especially for teams or frequent data entry. You can learn more about this feature here.

Other advantages include password-protection for your sequence settings and the ability to pre-define reusable formats for various document types or departments.

Automatically generate invoice numbers using Excel formulas

For straightforward invoice numbering without macros or add-ins, Excel formulas provide a quick way to generate sequential invoice numbers. This is most suitable when you need a simple, one-off list of invoice numbers for a table or log, rather than persistent auto-increment between sessions.

1. Click on the cell where you want the first invoice number to appear (e.g., A2) and enter the following formula for a sequence starting at 10000:

=10000 + ROW()-2

2. Press Enter. The cell will display 10000 for row 2. To generate the next numbers, drag the fill handle down the column. Each subsequent row increases by 1, so the next invoice will be 10001, then 10002, and so on.

Parameters explained: Replace 10000 with your desired starting number. Adjust ROW()-2 based on the starting row; for example, if you start in row 5, use ROW()-5.

Practical tip: For a more customized invoice number, try combining with dates or prefixes, such as:

="INV"&TEXT(TODAY(),"YYMMDD")&TEXT(ROW()-1,"000")

This will output invoice numbers like INV240614001, INV240614002, etc. Adjust as needed for your structure.

Note: This method is ideal for generating a batch of sequential numbers at once for printing or exporting, but it does not automatically increment on file reopening.

Demo: Add invoice number with KTE's Insert Sequence Number feature

Insert unique and consecutive invoice numbers across multiple workbooks

In most cases, worksheet-based formulas or simple methods will only generate invoice numbers in a single sheet or workbook. If you need to manage invoices for several projects, departments, or users across different workbooks, you may find it difficult to synchronize the sequence and avoid duplication. The Insert Sequence Numbers feature of Kutools for Excel is designed for this scenario, making it possible to insert unique, consecutive invoice numbers across multiple files and for multiple users with ease. This centralizes your numbering logic and further reduces risks in complex, collaborative workflows.

Get a 30-day full-featured free trial now!