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

In business transactions, an invoice number is a crucial tool to trace money flow. Therefore, auto generate invoice number in Excel is an important way to use a similar bill format with the auto-changed invoice numbers.

Letโ€™s say, we have an Invoice to a certain company that repeatedly buys stuff from our company. In this case, we want an auto generated invoice number every time we open Workbook. Keep in mind that we want the Bill To companyโ€™s credentials intact but clear the itemsโ€™ description (i.e., B8:G10).

Dataset-Auto Generate Invoice Number in Excel

In this article, we demonstrate VBA Macro to auto generate invoice number in Excel every time we open the Excel Workbook.


Download Excel Workbook


Introducing Invoice Number

Invoice numbers are the identification tool to track transactions among parties. An itemized bill is assigned with an identifiable sequential number in businesses. The identification number works as an Invoice Number. For business usage, invoice numbers are crucial as they are used as a tool to send customers reminders, cross-checking the ledger.

Users can assign invoice numbers Manually, Automatically using VBA Macro or by professional Software offered by various companies. In this article, we discuss the thorough process to auto generate invoice numbers in Excel.


Steps to Auto Generate Invoice Number in Excel

๐Ÿ”„ Making Invoice Template to Auto Generate Number

We said earlier that making invoices can be done in three ways; Manually, Using Templates, and Financial software. We can build an Invoice from scratch according to your need or use Excel offered free Templates as your choice.

๐Ÿ”ผ To use Excel Templates Go to File > New > Type Invoice (in the Search option). Excel offers numerous Templates to choose from.

Template-Auto Generate Invoice Number in Excel

๐Ÿ”ผ Choose one of the Templates (i.e., Blue Invoice) then Click on it. Afterward, Excel displays the Create option. Click on it to download and be able to use the Template.

Create


๐Ÿ”„ 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. Otherwise desired outcome wonโ€™t be achieved.

๐Ÿ”ผ Go through File > Save As > Select convenient location and assign a name to the downloaded template > At last Select Save as type Excel Macro-Enabled Workbook.

File saved as Excel Macro-Enabled Workbook


๐Ÿ”„ Inserting Macro to Auto Generate Invoice Number

๐Ÿ”ผ After saving the file as Excel Macro-Enabled Workbook, Use ALT+F11 to open Microsoft Visual Basic window. Double-Click on ThisWorkbook under VBAProject.

Double click

๐Ÿ”ผ Double Clicking on ThisWorkbook brings out a Workbook Open code window. Paste the following macro in the window.

Private Sub Workbook_Open()
Range("G4").Value = Range("G4").Value + 1
Range("B8:G10").ClearContents
End Sub

Macro

The macro is a Private macro. Every time you open the xlsm file, the macro adds 1 to auto generate the invoice number that is in the G4 cell. Also, it clears Item Description Contents for a particular customer or company.


๐Ÿ”„ Auto Generated Invoice

๐Ÿ”ผ As you insert the macro, Exit from Excel. Reopen the Excel File (i.e., Auto Generate Invoice Number), you see Excel creates a new Invoice by adding 1 (i.e., Invoice Number: 1001) to the Invoice Number and Clearing all the Itemโ€™s Descriptions.

Auto generated Invoice Number-Auto Generate Invoice Number in Excel

You can also see the other entries are intact as you can use the same Invoice as a new one. If you open the same file again it increases the Invoice Number to 1002.


Conclusion

In this article, we demonstrate the step-by-step process to auto generate invoice number in Excel. Though you can manually copy any bill; then by changing the Invoice Number, you can generate a new Invoice. However, this isnโ€™t the topic discussed here. Using the same file after Printing or Saving it in other forms; you can use the same file with auto generated invoice numbers. Hope this article guide you toward your goal. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo