Auto Generate Invoice Number in Excel Template (4 Quick Steps)

Suppose we invoice a certain company repeatedly using an Excel Invoice Template. We want an auto generated invoice number every time we open the Template Workbook to generate a new Invoice for the customer.

In the Invoice Template below, in addition to auto-generating a new Invoice Number, we also want to clear the Item Description cells (B8:G10), while leaving the Bill To company credentials intact.

Dataset-Auto Generate Invoice Number in Excel

In this article, we’ll demonstrate how to use a VBA Macro to do this.

Step 1 – Selecting an Invoice Template in Excel

Making invoices can be done in three ways; Manually, Automatically by using Templates in Excel, or using third party Financial software. We’ll use Excel’s free Templates here.

  • Go to File > New.
  • Enter Invoice in the Search bar.

Excel offers numerous Templates to choose from.

Template-Auto Generate Invoice Number in Excel

  • Choose one of the Templates (here, Blue Invoice) and click on it.

Excel displays a Create button.

  • Click on it to download and be able to use the Template.


Step 2 – Saving Invoice Template in XLSM File Format

Excel by default saves files in XLSX formats. Since we are going to use VBA Macro to auto generate invoice numbers, we have to save the XLSX file in XLSM format instead.

  • Go to  File > Save As.
  • Select a convenient location and assign a name to the downloaded template.
  • Select Excel Macro-Enabled Workbook as Save as type.
  • Click Save.

File saved as Excel Macro-Enabled Workbook

Step 3 – Inserting Macro to Auto Generate Invoice Number

  • Use ALT+F11 to open the Microsoft Visual Basic window.
  • Double-click on ThisWorkbook under VBAProject.

Double click

A Workbook Open code window opens.

  • Copy and paste the following macro in the window:
Private Sub Workbook_Open()
Range("G4").Value = Range("G4").Value + 1
End Sub


The macro is a Private macro. Every time you open the xlsm file, the macro adds 1 to auto generate the invoice number in cell G4. It also clears the Item Description contents.

Step 4 – Auto Generated Invoice

  • After inserting the macro, exit from Excel and reopen the Excel File (here named Auto Generate Invoice Number).

Excel creates a new Invoice by adding 1 to the Invoice Number (now Invoice # 1001), and clearing all the Item Descriptions. All other fields and information are intact.

Auto generated Invoice Number-Auto Generate Invoice Number in Excel

If you open the same file again, the Invoice Number will be incremented to 1002 and the Item Descriptions will again be cleared.

Download Excel Workbook

Related Articles

<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF