4 Steps to Auto Generate Invoice Number in Excel

Invoice numbers are identifiers that businesses mostly require to keep track of their money transactions. Since it deals with a large number of purchases, businesses often require auto generated invoice numbers to give an identifier to each purchase. We can do this easily with the help of Excel. This article will discuss the way we can auto generate invoice number in Excel.

πŸ“ Download Excel File

Download the practice workbook below.

Overview of Invoice Number

An invoice number is a unique number that is assigned every time we want to issue a new invoice. It is a sequential code that helps businesses keep track of their purchases. The invoice number can consist of just numbers or both numbers and letters combined together. It is very essential to avoid duplicate payments as well. Usually, these numbers are auto-generated due to handling a large volume of data.

Learn to Auto Generate Invoice Number in Excel with These 4 Suitable Steps

To demonstrate things, we will be taking a step-by-step approach to auto generate invoice number. We will take the help of an online template and use the cell references where we want to auto generate new values and remove certain values as well. The steps are briefly discussed below.


Step 1: Choosing Invoice Template from Excel Online

The first step in auto generating invoice numbers is to choose a convenient invoice template from the online database. We can also create them by our own, but for this instance, we can select a template by following these substeps.

  • At first, we move to File tab and select NewΒ option.
  • Then on the search bar, we search for the keyword invoice to look for invoice templates in Excel online.
  • After that, we choose a template. For instance, we select the template named Simple service invoice.

  • Then we press the Create button.

auto generate invoice number in excel template

  • The template will open itself in Excel File.

πŸ“• Read More: 8 Easy Tricks to Generate Random Data in Excel


Step 2: Saving Template as Macro Workbook

After downloading the template, we need to save it into a Macro workbook. To do so, we follow these procedures.

  • Firstly, we go to File tab and click on Save AsΒ option.
  • Then, a Save As box will appear. We write a File name and on the Save as type box, we click on Excel Macro Enabled Workbook.
  • Then we click on Save and save the template as a .xlsm file.

auto generate invoice number in excel saving as macro workbook

πŸ“• Read More: Generating Random Number Between Two Numbers in Excel


Step 3: Incorporating VBA Code to Auto Generate Invoice Number

Next step after opening the file is to incorporate a VBA Code that will help us generate an auto invoice number. To write the code, we follow these substeps.

  • At first, we press the shortcut key Alt+F11 in order to open the Visual BasicΒ window.
  • Then we double click This Workbook section and a blank window will appear on the right.
  • We write the following VBA Code on the right box and then close that window.
Private Sub Worksheet_Open()
Range("F5").Value = Range("F5").Value + 1
Range("F8").Value = Range("F8").Value + 1
End Sub

πŸ”¨ Code Breakdown

πŸ‘‰Β  Here, Range(β€œF5”).Value = Range(β€œF5”).Value + 1 increases the value of the cell F5 by 1 each time we reopen the file.

πŸ‘‰Β Β  Here, Range(β€œF8”).Value = Range(β€œF8”).Value + 1 increases the value of the cell F5, that is the Customer ID by 1 each time we reopen the file.

πŸ‘‰Β Β  Range(β€œF16:H17”).ClearContents clears the entries within the range F16:H17 each time when the file is reopened.

auto generate invoice number in excel incorporating macro to auto generate invoice number

  • Then we save the file by pressing Ctrl+S. A Microsoft Excel warning box will appear. We click on OKΒ button.

  • At last, we close Excel file.

πŸ“• Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel


Step 4: Starting Auto Generated New Invoice

The final step is to see how the auto generated invoice number gives a new sheet clearing the entries in the fee section and assigning a fresh invoice and customer id to the newly opened file. To do that, we follow these procedures.

  • First of all, we click and open the Excel file again.
  • We will notice that the invoice number as well as the customer ID has changed. Along with that, the entries in F16 to H17 will be removed to open a new sheet for another customer ID.

auto generate invoice number in excel output 1

  • Then we close the file again and this time, we click on the Save button in the popped-up box.

  • The Document Inspector warning box will come again, we press OK.

  • Then we reopen the file again. This way, each time we reopen the excel file, a new invoice number will be generated automatically.

auto generate invoice number in excel output 2

πŸ“• Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel

πŸ“„Β  Important Notes

πŸ–ŠοΈΒ  If we cannot open Macro Enabled Worksheets, we have to unblock it by right clicking and selecting properties.

πŸ–ŠοΈΒ  If the Macros are disabled in Excel, we can enable them by selecting Enable all macros from Macro Settings section and check the box named Trust access to the VBA project object model. We can find these options in the Developer Macro Settings from the Macro Security option present in the Developer tab.

πŸ“Β  Takeaways from This Article

You have taken the following summed-up inputs from the article:

πŸ“Œ Β We can auto generate invoice numbers in Excel by referencing desired cells with the help of VBA Code.

πŸ“ŒΒ  Additionally, we can also clear entries of certain cells each time we reopen an Excel file, that is unique to a certain issued invoice.


The various steps explained have hopefully helped you to better understand how we can auto generate invoice numbers in Excel. If you have any questions regarding the topic, feel free to let us know in the comment section. For more Excel related articles and solution, visit our site ExcelDen.

Related Articles

(Visited 48 times, 1 visits today)


Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply