How to Create an Excel Invoice Number Counter

Updated April 17, 2017

Excel is commonly used to generate invoices for small businesses. Getting Excel to generate automatically incrementing invoice numbers requires digging into the Excel Visual Basic editor; this is often the very first macro a novice Excel user makes.

Create an invoice in Excel, and save the file as "Invoice.xls"

Enter your initial invoice number in cell A1. For example, if your initial invoice number is 300, you'd type 300 in cell A1.

Press the "Alt" and "F11" keys at the same time. This will open the Visual Basic editor.

Within the Visual Basic editor, press "Ctrl"+"R" to open the Project Explorer window.

Double click the line that says VBA(Invoice.xls). When the menu opens up, double-click "ThisWorkbook," which is a special function in Visual Basic detailing how operations on this workbook will operate. A pane will show up on the right.

Enter the following text, exactly as shown here, in the pane on the right, without the quotes. The line breaks are important -- this should show up on three lines in the window.

"Private Sub Workbook_Open()

Range("a1").Value = Range("a1").Value + 1

End Sub"

Save and reopen the file. Every time the Invoice.xls file is opened, the number in cell A1 will have 1 added to it.


If you're doing this in Excel 2007, the file name will be Invoice.xlsx, and should be referenced as Invoice.xlsx in Step 5 above.

Things You'll Need

  • Excel 1995 or later.
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.