How to Make Invoice Number Change Automatically in Excel. Step by Step Instructions
To make an invoice number change automatically in excel, you will need to add a macro to the document. A macro is a set of unique instructions that will tell the cell housing the invoice number to update to the next number, each time the invoice is opened.
Here are the steps to create an invoice number automatically in excel:
- Create Your Invoice in Excel
- Note the Cell Where Your Invoice Number Is
- Select ALT + F11
- Double-Click “This Workbook”
- Revise, Copy and Paste This Code
- Adjust Your Macro Settings
- Save Document as Macro-Enabled
- Restart Your Computer
- Start a New Invoice
1. Create Your Invoice in Excel
There are plenty of free excel invoice templates online you can use. Select one and build your invoice. Create your first invoice number, for instance, “1000”. This number should be put in a cell by itself, with no other information included with it.
2. Note the Cell Where Your Invoice Number Is
For instance, “C2”. You’ll need this location information for the macro.
3. Select ALT + F11
Hold both buttons down at the same time. You will be taken to the “Microsoft Visual Basic for Applications” screen.
4. Double-click “This Workbook”
You’ll see “This Workbook” on the left, under “Microsoft Excel Objects”.
5. Revise, Copy and Paste This Code
Copy the below code, after subbing in your cell number for C2. Paste it into the window to the right of the “Microsoft Visual Basic for Applications” screen (this window should be blank).
Private Sub Workbook_Open()
Range(“C2”).Value = Range(“C2”).Value + 1
6. Adjust Your Macro Settings
Go into “Developer” on your toolbar, and select “Macro Security”. Select the “Enable all macros” and “Trust access to the VBA project object model” options.
7. Save Document as Macro-Enabled
When you go to save your document, type in the name of your invoice and change the type to “Excel Macro-Enabled Template”. Then save it.
8. Restart Your Computer
Restart your computer, then click on the excel invoice. It should automatically update the invoice number, to the next number in the sequence.
9. Start a New Invoice
Once you have updated the information for your latest job, you will need to save it under a new name, for instance, “Invoice 1001”. Remember to choose “Excel Macro-Enabled Template” as the type.
You will need to create a name, and select the type, each time you create a new invoice.
It is very important that when creating a new invoice, you always open the last invoice saved, so that the invoice numbers stay in sequence.
Other Questions Related to How to Make Invoice Number Change Automatically in Excel:
What Is an Invoice Number?
An invoice number is an identifier. A company will assign each invoice a unique number, so that payment can be properly traced. This number generation can be done manually, automatically (as discussed above), or through accounting software that will also automatically generate a new number every time there is a need for a new invoice.
What Is the Best Way to Number Invoices?
The best way to number invoices is sequentially, and with an identifier.
For instance, Mitch has just started his own business, Mitch’s Motorcycles, which sells and services high-end motorcycles. His first invoice could look something like this: “MM001”. The numbering for his next invoice would be “MM002”, and onwards.