Doing your invoices should be fun, but it can be super frustrating if you do it in Excel. Fret not. In this article, I’m going to show you how to make a smart invoice template in Excel so that it will be a 5-minute fun ride, and then you can go back to doing what you love.
Why doing your invoices in Excel?
There are many reasons to do invoices in Excel:
- Because Excel has been available on your computer since donkey’s age.
- You have already purchased an annual subscription for the Office 365 suite, so why not get your money’s worth?
- You’re not fit for enterprise solutions, and you’re not ready to add Quickbooks to your plate.
Whatever your reason is, it’s alright. We all need different tools at different phases of our career or lives.
The problem with doing your invoices in Excel
I often think of of Excel as a spreadsheet version of a Bullet Journal. It’s very agnostic in that you can do anything you want with it, and no two spreadsheet looks identical. But that’s also where the problem lies.
There are many free Excel invoice templates online, but they don’t offer an effective invoice management scheme. You have to do a lot of manual typing. It’s easy to input the wrong invoice number. And what do you do when you want to track an invoice issued years ago?
The good news is there are a lot of cool tricks you can implement to automate the invoicing process. I myself have wasted so many hours doing it the inefficient way, so I’m going to share with you all the secret sauces to save you time.
The 4-step method for creating a smarter invoice template in Excel
Note: There won’t be any formatting tips in this article. You can spend hours tweaking the format, or you can leave it as it is. I think it’s far more important to focus on setting up and automating our invoice system.
Step 1: Find and reuse an invoice template
There’s no nobility in reinventing the wheel, so we are going to find, download a premade invoice template in Excel and tweak it to our liking.
You can either Google it, or firing up Excel, and type “invoice template” in the search box. With hundreds of templates to choose from, you’re bound to find one that suit your business.
Below is what I settled on. A lovely, clean gray template named “Invoice with Finance Charge”.
Step 2: Create a drop-down list of our products or services
Now we’re going to do some fun things with our invoice. Who want to type product names and prices every time we issue an invoice? Not me, so I’m going to turn the “product description” field into a drop-down list.
Create a “Master Catalogue” worksheet for reference
Inside the workbook we’re working on, navigate to the bottom of the screen and click the plus button. Let’s create a new work sheet for a list of our products and their corresponding prices. Give it a meaningful name, like “Master Catalogue”.
Now fill in your product or services names and prices. Don’t worry, we only need to do it once.
By the way, “Finance Charge” is the name given to the invoice template worksheet. You can change it if you want, but I will still refer to it as “Finance Charge” throughout this post.
Define name for our products
Next, we need to cluster a group of products together so that we can access them programmatically from the “Finance Charge” worksheet. That means we need to define some ranges.
Start by clicking and dragging with your mouse to select the whole product list. Don’t select the prices, or “Product List” header.
From here, click the Formulas ribbon tab and select “Define Name” right in the middle of the screen. Enter the name for the product range in the pop-up dialogue box, and click OK.
Next, go back to the “Finance Charge” worksheet. Under the description field, we are going to create a drop-down list of all those product choices.
Click and drag to select all the cells under the “Description” column. Now, change to the “Data” ribbon tab, navigate to the “Data Tools” section and click on the “Data Validation” button.
In the “Settings” area, click on the drop-down list under “Allow” and choose “List”. In the “Source” area, type the equal sign and the word “Products”, which is the name of the range that we’ve just created.
Voila, look at this neat drop-down list. With little effort, it’s already so much easier to create invoices in Excel.
Step 3: Auto-fill prices
Having a drop-down list is cool, but we can take it a step further. Invoicing would be a breeze if you can select a product, and Excel auto-fills the prices. Oh yes, we can do that with something called Vlookup.
Everyone is intimidated by the name Vlookup until they see what it can do for them. In a nutshell, Vlookup looks at the value in one column and returns data in another column that you specify.
In this case, we can go to the “Master Catalogue” worksheet, look up the data under the “Products” column and return the corresponding prices. Sounds good?
Define name for both products and prices
In order to make that happen, let’s create one more name range. This time, click and drag to select both the products and the prices. Then go back to the “Formulas” tab and click “Define Name”. For this instance, I’ll call it ProductsPrices, and click OK.
Make use of VLOOKUP
Back to the “Finance Charge” worksheet, go to the first cell under the “Amount” column and enter the following formula:
=IFERROR(VLOOKUP(B16, ProductsPrices, 2, FALSE), ""
To explain the formula
- B16 is the corresponding cell under the “Description” column. Excel is going to look up whatever in this cell.
- ProductsPrices is the range we are going to look up B16 against.
- 2 is the order of the column in ProductsPrices, and it is the “Prices” column in this case.
- FALSE means we want an exact match of whatever is in cell B16.
- IFERROR is an error handling. We need to tell Excel what to do if there is an error. The double quotes means we are doing absolutely nothing i.e. leaving the cell blank should error occurs. Yay for no scary error alert.
Finally, we want to fill this VLOOKUP formula for subsequent cells under the “Amount” field. So hover the cursor over the bottom right corner of the current cell until it turns into a crosshair. Click and drag to the end of of table.
Step 4: Prep your drop-down list for expanding
In step 2, the drop-down list we created was static, which means it will always refer to the same cells. But what if you expand your line of products or services?
Do you want to update the range reference manually every time you add new product or service to the “Master Catalogue”? Or is there a smarter way to do that?
Here comes the OFFSET formula to the rescue, because you deserve to grow your business offerings without incurring more invoice pains. The OFFSET formula allows you to create dynamic names that accommodate expandable data sets.
Go back to the “Master Catalogue” sheet. This time, you don’t need to click and drag anything. Simply select “Define Name” under the “Data” ribbon.
In the new dialogue, select the “ProductsPrices” name on the left side. Then in the field under the “Select the range of cells” area on the right side, enter this new formula:
- ‘Master Catalogue’!$A$2 represents the reference cell. It is the absolute A2 cell in the “Master Catalogue” sheet. If your worksheet’s name has more than one word, you need to wrap it inside single quotes.
- 0, 0 refers to the rows and columns to offset. They are both 0 in this case because there’s nothing to offset.
- CountA($A$B) get the number of non-blank cells in the columns of interest, which are A for “Products” and B for “Prices” in this case.
- The number 2 at the end of the formula refers refers to the number of columns we need to
Our invoice template in Excel has become much easier to process thanks to define name, drop-down list and VLOOKUP. But what if I tell ya that you can still auto-increment an invoice number, and auto-add whatever you entered in the invoice template to an invoice tracker? That’s totally doable, and I’m going to walk you through that in the next article.