Abrar
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.
- 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.
📕 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
Range("F16:H17").ClearContents
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.
- 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.
- 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.
📕 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.
Conclusion
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
- 5 Ways to Generate Random Number Between 0 and 1 in Excel
- 6 Ways to Generate Random 10-Digit Number in Excel
- 4 Ways to Introduce Random Number in Excel with Decimals
