Mr Excel’s invoice numbering macro tip

Reglar readers of Excel Zone will remember “Mr Excel”, Bill Jelen from our 2008 Golden Cells online Excel video awards.

Bill experienced some unpleasantness with YouTube not long after being hailed on our pages, perhaps because his video tutorials might have been too successful for Google’s liking? But we’re glad to report he’s back in business and pumping out daily tutorials on his YouTube channel.

Thanks to a tip-off from the Microsoft Excel blog (where you can find the macro source code), we heard of a nifty trick this week for anyone who uses Excel to create invoices, and wants to increment the invoice number each time a new invoice is created.

A 2min Mr Excel video (see below) explains how it’s done, based on the following steps:

• Open an invoice template in Excel.
• Save the invoice template and then create a macro to increment the invoice number and clear the relevant worksheet cells so you can start fresh on the next invoice.
• Create a second macro to save a copy of the invoice as a macro-free file (.xslx) with a unique file name and save the master version of the workbook as a macro-enabled (.xslm) file.

It’s a great insight to how Excel handles a very common accounting task, but the one question Mr Excel doesn’t address is whether you should be using a spreadsheet as your main invoicing engine.

Even the cheapest accounting software can handle that for you, and retain the invoice data in your sales ledger.