Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to automatically generate invoice number in Excel?

As we know, invoice numbers are consecutive. If we input invoice numbers manually, we may forget the invoice number last time we used, and get overlapping number for the new invoice in Excel. In this article, I will introduce two methods to automatically generate invoice numbers in Excel easily.

Add a automatically invoice number generator in Excel

Add invoice number with KTE’s Insert Sequence Number feature

Easily find and insert all missing sequence numbers (invoice numbers) in a list in Excel

Insert unique and consecutive invoice numbers across multiple workbooks

Most of time, we can only insert consecutive invoice numbers by formulas or other methods in one sheet or one workbook. But, with powerful Insert Sequence Numbers feature of Kutools for Excel, you can easily insert unique and consecutive invoice numbers across multiple workbooks. Full Feature Free Trial 60-day!
ad insert sequence invoice number

arrow blue right 
bubbleAdd a automatically invoice number generator in Excel

1. Create a new workbook, or open the workbook you will add invoice number automatically.

2. Find a blank cell, and enter the beginning number of your invoice numbers. In our case, and enter 10000 into Cell D2.

3. Open the Microsoft Visual Basic for Applications dialog box with pressing the Alt + F11 keys simultaneously, then expand the VBAProject (current workbook), and double click the ThisWorkbook. See left screenshot:
Note: In our case, we double click the ThisWorkbook under the VBAProject (Book1).

4. Paste the following code into the opening window.

VBA: Voice Number Generator

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

Note: Change the D2 to the specified cell in which you entered the beginning number of invoice number in Step 2.

5. In addition to make whole numbers as invoice number, we can verify our invoice numbers with following formulas:

No. Formula Invoice Numbers
1 ="CompanyName"&TEXT(TODAY(),"yymmdd")&D2 CompanyName141209100000
2 ="CompanyName"&TEXT(TODAY(),"0")& D2 CompanyName41982100000
3 ="CompanyName"&TEXT(NOW(),"MMDDHHMMSS")& D2 CompanyName1209095819100000
4 ="CompanyName"&TEXT(NOW(),"0")& D2 CompanyName41982100000
5 ="CompanyName"&RANDBETWEEN(100000,999999)& D2 CompanyName448685100000
note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

(1) Change the D2 to the cell you entered the beginning number of invoice in above formulas, and change the CompanyName to the text you want to show in your invoice numbers.
(2) Enter one of above formulas into the cell you will get invoice number automatically, and press the Enter key.

6. Save current workbook:
A. In Excel 2013, click the File > Save (or Save As) > Computer> Browse;
B. In Excel 2007 and 2010, click the File / Office Button > Save (or Save As).

7. In the coming Save As dialog box, please do as follows:

(1) Enter a name for this workbook in the File name box;
(2) Click the Save as type box and specify the Excel Macro-Enabled Workbook (*.xlsm) from drop down list;
(3) Click to open a folder where you will save this workbook;
(4) Click the Save button.


From now on, every time you open the workbook of Invoice Number Generator, the invoice number is 1 bigger than the last time you open the workbook. Please note that you must save the workbook before closing it every time.

arrow blue right 
bubbleAdd invoice number with KTE’s Insert Sequence Number feature

You may notice that the first method requires you to get the invoice number only in a specified workbook. What’s worse, once you forget saving the specified workbook before closing, the invoice number will overlap.  But with Kutools for Excel’s Insert Sequences Numbers feature, you can insert unique and consecutive invoice number no matter which workbook you are using.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the cell you will get invoice number, and then click the Kutools > Insert > Insert Sequence Number.

2. In the Insert Sequence Number dialog box, click the New button, and then in the expanding Sequence number editing area please do as follows (see above screenshot):
(1) In the Sequence name box enter a name for the new sequence name, such as Invoice Number.
(2) In the Increment box, enter 1;
(3) In the Prefix (optional) box, enter you company name or other text you will show at the begging of invoice number;
(4) In the Start Number box enter the beginning number of your invoice number, such as 10000 or 1;
(5) In the No. of digits box, specify the digits, such as 6;
(6) Click the Add button.

3. Keep the new created Sequence selected, and click the Fill Range button, and then click the Close button.

No matter in which workbook you apply this Insert Sequence Number feature, it will generate an invoice number that is 1 bigger than the last time you apply this feature. Click to know more about this Insert Sequence Number feature of Kutools for Excel

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

arrow blue right bubbleDemo: Add invoice number with KTE’s Insert Sequence Number feature

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!

Easily find and insert all missing sequence numbers (invoice numbers) in a list in Excel

As we know, invoice numbers are consecutive normally. But sometimes some invoice numbers may be missing by mistakes. Kutools for Excel’s Find Missing Sequence Number utility can help you find and insert all missing invoice numbers, and highlight rows or insert blank rows when encountering missing invoice numbers as well. Full Feature Free Trial 60-day!
ad find missing sequence numbers 2

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Vipul Pandey · 2 years ago
    I am a cleark in a school. In excel sheet student list is their.
    I cut the bill student wise via mail merge and give bill no manually so [b] sometimes some bill no skipped[/b]. so I want a rule, when I skipped no give some instruction and stop.
    we use data validation, but its work only unique no or duplicate no.

    so please help us,

    Student name Bill no.
    Rahul 1
    shyam 4
    Shivani 2
    Pratima 3

    thank you.
  • To post as a guest, your comment is unpublished.
    James · 2 years ago
    How do I use this insert to auto generate the number without having to run it every time....Say, every time I open the Quote template it auto fills the cell that I inserted this number generator in?
  • To post as a guest, your comment is unpublished.
    Lynda Smith · 2 years ago
    Very helpful the automatic numbering of invoices. But how do i keep record of these invoices separately form my template ? I would appreciate your help. thanks L
  • To post as a guest, your comment is unpublished.
    nouman · 2 years ago
    jgljkasdf asjkfg asd asdf agh fajlsd fads ga df
  • To post as a guest, your comment is unpublished.
    greenbergmethew · 3 years ago
    this is also useful for me,
    but i need some modification in that
    XXX = number
    yy-yy = financial year
    LAA = Text

    Here Financial year from May-13 to April-14
    So need as per Example:
    For Financial Year 2013-2014

    And for next Financial Year 2014-2015
    Thanks a lot.
    [url=http://www.trainingintambaram.in/hadoop-training-in-chennai.html]hadoop training in chennai[/url] | [url=http://www.traininginsholinganallur.in/informatica-training-in-chennai.html]informatica training in chennai[/url]