How to Make Invoice Number Change Automatically in Excel

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’s what we’ll cover:

• Steps to Create an Invoice Number Automatically in Excel
• What Is an Invoice Number?
• What Is the Best Way to Number Invoices?
• How Do I Write a Basic Invoice?

Steps to Create an Invoice Number Automatically in Excel

Here are the steps to create an invoice number automatically in excel:

1. Create Your Invoice in Excel

There are plenty of free Excel invoice templates online, or you can create your own using our guide on how to make an invoice in Excel. 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
End Sub

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.

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.

How Do I Write a Basic Invoice?

For tips on what you should include in your invoice, please consult How To Send an Invoice.

---

RELATED ARTICLES