creating unique invoice numbers automatically

Copper Contributor

I can't seem to find the correct formula to have my invoice numbers automatically entered and automatically add 1 to the previous invoice for the "new" invoice number. should be so simple!

2 Replies
This works best if your data are in a table (created using Insert > Table). Let's say the first invoice is in row 2 and you want to start with invoice number 1001. Enter the formula =ROW()+999 Excel will automatically propagate the formula to all existing and new rows in the table.


This code automatically generates unique invoice numbers. It keeps track of the most recent invoice number used and builds upon that to create the next one. The code utilizes a variable named last_invoice_number to store this information. A loop ensures the function keeps generating new numbers. Inside the loop, the last_invoice_number is incremented by 1 to provide a unique value for the new invoice. Finally, the function formats the invoice number as a string with a prefix ("INV-") and leading zeros to guarantee a consistent format (e.g., INV-00001, INV-00002). This approach ensures you have unique invoice numbers for each transaction.