Generate invoice numbers in Excel

Invoice templateIf you need a way to create invoices in Excel, feel free to use one of the many invoice templates on Office.com. While these templates are a great starting point, they won’t automatically generate unique invoice numbers—you need a macro for that.

Fortunately, Excel MVP Bill Jelen shows you how to create a couple of macros to address this issue. The first macro automatically increments the invoice number and then clears cells on the worksheet so you can start fresh on the next invoice. The second macro lets you save a copy of the invoice with a unique file name. Enjoy!

…(read more)

2 Responses to “Generate invoice numbers in Excel”

  1. Questionaire says:

    Is there a way to automatically generate Excel numbers?
    Is there a way to autonumber in Excel? For instance, on an invoice the number starts at 000001, how can I autonumber each invoice so it auto generates itself? Thank you experts!

  2. garbo7441 says:

    You can create a WorkBook_Open macro to create an invoice number in the cell you wish it to appear in. It can be something as simple as adding ’1′ to the value already in the cell. You could start with a base number in the cell, such as 000001. Then each time you open the workbook, the cell would increment. However, if you open the workbook and close it without completing an invoice, you will not have sequential invoice numbers. There will be gaps.

    Pesonally, I approach it differently. In my experience customers sometimes contact my company with questions about invoices that can be quite old. I like to create invoice numbers that can tell you something beyond just an invoice number.

    The following macro creates an invoice number that will tell you the year of the invoice, the month and day it was created and the time it was created. Cell A1 contains the invoice number. It will generate invoice numbers such as 10-01262025. This invoice would have been created on 1/26/2010 at 8:25PM.

    Change the "A1" reference in the macro to the cell you wish the invoice number to appear in and copy this macro to the clipboard:

    Private Sub Workbook_Open()
    Range("A1").Value = Right(Year(Date), 2) & "-" & _
    Format(Month(Date), "00") & _
    Format(Day(Date), "00") & Format(Hour(Time), "00") & _
    Format(Minute(Time), "00")
    End Sub

    Press ALT + F11

    Double click on ‘This Workbook’ iln the Microsoft Excel Objects in the upper left part of the VBE.

    Paste the macro into the WorkBook module to the right.

    Close the VBE and return to Excel. Save the workbook.

    Each time you reopen the workbook the invoice number will increment.

    If you just want the simple invoice number series starting with 000001, use this macro instead:

    Private Sub Workbook_Open()
    Range("A1").Value = Range("A1").Value + 1
    End Sub

    If you want leading zeros to show, you will have to format the invoice number cell as Custom and 000000
    References :

Leave a Reply