I need to invoice someone each week. I would like to create a template as 90% of the information on each invoice is the same, and the remaining 10% follows simple rules.

Each invoice has a number that is 1 more than the previous one. So in Excel is there a way that I could have it auto-increment between instances of the template? For example if the last template had the value 1 for the invoice section, how can the next automatically be 2?

Also how can dates be increased by 7 days?

I'm thinking that a template could somehow remember the previous one and then open it up with the updated information and save it under a new name.

  • Excel stores dates in units of days. To increase a date by 7 days, just add 7 to it (the readable date appearance is just formatting). You will probably need VBA for the serial number. Store the current number. Trigger incrementing it automatically by an action like printing it, or add a macro to manually click a button to increment it when you want. There have been a number of questions about similar serialization. A quick search should give you some sample solutions. – fixer1234 Apr 17 '18 at 9:20
  • Check out superuser.com/questions/1313372/… – fixer1234 Apr 17 '18 at 9:25

You can do a pretty good hack without writing any VBA. I used the TODAY() function to get the current date. I then subtracted the first invoice date and divided by 7 (as @fixer1234 said, Excel stores dates in days, so 1 week = 7). I also added 1 so the first invoice is invoice #1.

Excel invoice

The only caveat with this approach is that if you don't invoice exactly weekly, or skip a week, it throws out your invoice numbers. It becomes a lot easier if your requirement for invoice numbers is simply that they be unique instead of sequential.


An alternative solution that may work even better is to split the invoice data from the template. Here I put the data into a sheet called data, and formatted it as a table. This is necessary because it makes it a LOT easier for the template to always grab the latest data. After adding new data you sort by invoice number descending. Or if you want to re-print an old invoice, you can filter by invoice number.
Invoice data

Then on the template sheet, you simply refer to the top row of the data table for your values: Invoice template

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.