If you are looking for an invoice template to download, you can download it for free on the download page here. But in this tutorial, I will show you how to create invoice in Excel.
As we all know, invoices are very important business documents that need more attention to ensure accuracy. Creating invoices can be a boring task if you are not using an accounting software for your business. However, with a good Excel invoice template, you can transform invoicing from boring to awesome.
Whether you are an accountant, a small business owner, or someone who wants to learn how to design and develop systems in Excel, this tutorial is a step by step guide that will guide you through the entire process.
Table of Contents
How to make invoice in excel – the Starting point
It is obvious that an average Excel user can create a simple table with columns that have all the details of a simple invoice. I guess this is not the type of invoice you want to create. A good invoice template is one that will help the end user to automate most of the job when generating invoices for customers.
There are two approaches to making this simple-to-use system. The first approach is to create a system that automatically retrieves item prices from the product catalog when a product is selected by using a dropdown list. (See screenshot)
The second approach will also retrieve an item from the product catalog based on the product ID. All these approaches prevent the end-user of the invoice from having to type out full product names. (See screenshot)
So to be able to design such a system, you need to employ the following techniques:
- Using VLOOKUP() function to look up information.
- Referencing data from another worksheet.
- Using Excel Data Validation tool to create a drop-down list of items.
- Using IF() and ISBLANK() functions to avoid calculations when data isn’t present.
- Using IFERROR() function to check expressions for any kind of error and take a decision afterward.
- Using the IFNA() to check for missing values.
If you are interested, let’s get started.
Step one: Create two worksheets
Rename one worksheet as Details and the other as Invoice.
The details worksheet will contain a table filled with your business products alongside with product prices. It’ll also contain another section for your company details and a place for recording customers’ information.
The picture below instance how the Detail sheet should look like:
Step two: Set table names and named ranges
Assign a name to the inventory list table like ProductsTable. This will be very helpful in the later part of this tutorial when you start writing functions.
To name a table, click anywhere in the table and navigate to the design tab. In the category group, you should see Table Name. Excel will automatically give it a name like Table1 or Table2. Just delete the name and type ProductsTable in the Table Name box then press the Enter key. (See screenshot)
Also, assign names to both the product column and the unit price column. Select the product column of the inventory table and name it Product. Do the same for the Unit price column and name it Price. This is very important and you’ll need to use these names later in the course of this tutorial. (See screenshot)
Step Three: Layout your invoice format
As we all know, every simple invoice template must include details like the invoice number, name and address of a customer, date of sale, details of goods and/or services, tax rate, discount rate, and the total amount due.
Now it’s time to show your designing skills. Build a worksheet that will actually make the invoice. Make sure to include space for entering all the important details of the invoice.
You can use a table to make the columns or use the Excel grid layout as your guide. You may also download this blank invoice template to get more ideas on how to layout your invoice format.
This empty invoice sample is what I will use throughout this tutorials. You can download it here.
You have to try as much as possible to make your invoice look great. It is not easy though. I spent more than 30 minutes just to make my templates look great. I am not the best designer, but I always do my best. So should you. You can, and you definitely should search the web for great looking invoices, copy their style, add yours, and come out with something great (that’s always the trick).
Step four: Link your worksheets
Now you’ll learn how to link the Invoice worksheet to the Details sheet by referencing. This will make it easy for anyone to use the template. The user just needs to enter his business name and address and that of the customer on the details sheet and everything will update automatically on the Invoice.
To reference from a different worksheet, just follow the following steps:
- On the Invoice sheet, activate the cell you want to reference.
- Type an equal sign and switch to the DETAILS worksheet.
- On the DETAILS worksheet, click on the source you want to reference from.
- Now press the enter key.
After following the above steps, you should see that the information or data on the DETAILS worksheet is now displayed on the Invoice. (See screenshot)
Now any information about your business or your customer, when entered on the DETAILS worksheet, will automatically show on the template in its appropriate place.
Step five: The description column
The description column of your template will make it easy for users to enter products by just clicking and selecting the products into the cells. I will show you how to do that using the Data Validation tool.
- Select the first cell under the description column.
- Go to Data→Data Validation (The Data Validation windows should pop out)
- Choose the Settings tab and then choose List from the Allow text box.
- In the source box, type =Product and click OK (You are now using the named ranges in step two, remember)
- This setting will only affect the selected cells. So copy the settings to the remaining cells by dragging the fill handle downwards.
Step six: Automate the Unit Price and the Amount column
At this step, you’ll set a formula to retrieve an item price from the product catalog based on the product selected. This reduces the possibility of assigning wrong prices to products. Therefore, you need to write a lookup function that will scan the product table for a matching product name. Here’s the lookup to perform this task:
Copy and paste this into the first cell under the Unit Price column and press Enter. Try changing products in the description column and you’ll see that the price too is changed. (See screenshot)
How to handle the #N/A error
But there’s still a problem with your LOOKUP function. Copy the formula to all the available cells in the Unit Price column and you’ll see that the value #N/A appears in each row where you haven’t entered a product name. This error message is because the VLOOKUP() function attempts to look up a product price with a product name of 0, which doesn’t exist in your Product Table.
This error message is a significant concern since it will never allow your invoice to calculate subtotals using the SUM() function. Attempt using the SUM() function to add a range of cells that includes a #N/A value, and it will return the same #N/A error code.
To make this problem disappear, you must use conditional logic to first of all check whether a product name has been entered. If it hasn’t, you can put a blank value in the price column. If it has, you can use the original VLOOKUP() to get the product price.
Now look at the formula:
=IF(ISBLANK(C16), “”, VLOOKUP(C16,ProductsTable,2,0)) – (this formula will display nothing when there is no product selected)
=IF(ISBLANK(C16), 0, VLOOKUP(C16,ProductsTable,2,0)) (this formula will display 0 when there is no product selected)
Handling incorrect Values
The above function makes sure that empty rows show the right information. But what if the user did not use the drop-down to select a product and mistyped the product description or name? This only means that the mistyped product name doesn’t exist in the product table and hence doesn’t have a price. In this case, the familiar #N/A error is the result. The ISBLANK() function cannot help us this time around.
Of course, this error will definitely disappear as soon as the user fixes the problem by typing a legitimate product name. However, you can tweak your formulas to avoid all kind of errors including lookup errors. One awesome solution is to use the IFERROR() function. This function will return a blank or 0 value wherever there’s an error in the function:
=IFERROR(VLOOKUP(C16,ProductsTable,2,0),””) (this formula will display nothing when there is no product selected)
=IFERROR(VLOOKUP(C16,ProductsTable,2,0),””) (this formula will display 0 when there is no product selected)
Step seven: Calculating the Amount column
The amount column is there to compute the total price of a product after the quantity and the unit price has been entered. The formula to calculate this column is very simple; just multiply the unit price by the quantity. (See formula below)
Step eight: Calculating the Amount Due
Now is the time to compute the amount payable to the customer. But only after sales tax and sales discounted has been added and deducted respectively from the subtotal if any. The formulas below will help you in the calculations.
Subtotal: =SUM(I16:I29) (This sums all the amount column of your invoice)
Discount Amount: =I32*I31 (This calculates the sales discount by multiplying the discount rate by subtotal)
Tax Amount: =I34*I31 (This calculates the sales tax of the customer by multiplying the tax rate by subtotal)
Total: =I31+I35-I33 (This calculates the total amount after adding and subtracting the sales tax and discount respectively. This can serve as the amount payable if there’s no part payment made by the customer.)
Amount paid: You don’t need a formula here. It has to be entered manually.
Amount Due: =I37-I38 (This is necessary if the customer made a part payment of the total amount. Formula subtracts the amount paid from the total.
NOTE: Remember to format the Discount and Tax rate with percentage formatting.
Step nine: Creating the Invoice Receipt Template
Now is the time to make your sales invoice template also a sales receipt template. This is will make it easy to issue sales receipts to customers immediately payments are made. (See screenshot)
To attach this receipt to your invoice follow the following steps:
- Click the Invoice to make the cell active.
- Go to DATA→Data Validation (The Data Validation windows should pop out)
- Choose the Settings tab and then choose List from the Allow text box.
- In the source box, type RECEIPT,INVOICE and click OK
You should be able to switch between the receipt and invoice text by now. Now follow the following steps to make the PAID stamp appear when the receipt option is selected.
- Select the area you want the PAID stamp to appear. (If you want to make the stamp big, you should select and merge several cells)
- In the cell or merged cells, make a formula that will print the word PAID based on the receipt or invoice dropdown cell. This is the formula if you are using my invoice template layout: =IF(H3=”RECEIPT”,”PAID”,””). But if you are using your own layout, then you may have a different cell reference other than the H3 in the formula.
You should see the word PAID when you switch the invoice to receipt.
At this point, your invoice template creation is complete. You can add colors of your choice to make your template look great. You can download the finished version of the template here.
Invoice with product ID column
If you remember at the beginning of this tutorial, I said you can also create an invoice template that retrieves items from the product catalog based on the product ID. With this kind of template, sales representatives can quickly create an invoice by typing the product ID.
To create such a template there is no need for starting a brand new worksheet. You can customize the template you just created to save time. Follow the following steps:
- Turn the table to a three column table. For Product ID, Product Name, and Price. You can start creating the table from scratch if you find it difficult to customize an existing table.
- Layout your invoice template format and include a product ID column. You can also download the example file here.
- Insert the formulas
You need a lookup function that will scan the product table for a matching ID. And because the invoice needs to record the product name and its price, two lookup functions are required of you.
Here is the lookup the retrieves a product name for a matching ID:
Here is the lookup the retrieves a product price for a matching ID:
=VLOOKUP(C16,ProductsTable, 3, FALSE)
Also, write a simple formula to calculate for the amount column.
REMEMBER: You should see the #N/A error if you copy the formula to all the available rows in the invoice. As said before, this is because the lookup function tries to look up a product with a product ID of 0, which does not exist, hence the error value. Use the IF() function and the ISBLANK function, or IFERROR() function to solve this issue (refer to the step 6)
Enter a product ID to see how fast you can create invoices with this template.