Let’s get started.
Creating a Custom Invoice Template in Excel 2016: The Big Picture
We’ll be breaking down the construction of our invoice template in Excel 2016 into several different tasks:
Formatting the table in Excel: adding borders and sizing columnsAdding labels and formatting headersAdding formulas for totals and subtotalsFormatting numbersProtecting the spreadsheetSaving the spreadsheet as a template
That may seem complex overall, but the tasks themselves are simple if you approach them step-by-step.
Formatting an Invoice Table: Adding Borders and Sizing Columns
Formatting your invoice will require resizing columns and using the borders and the outline tool. The table you create will define the data entry points. Let’s practice a couple first. Start by resizing column A to 12 units. Select column A, then click Home, Format, Column Width and enter 12.
Next, create a block by selecting cell range A6:C10. Select the Home tab, click the Borders menu then select Thick Outside Borders. Create another box by selecting cell range A12:C16 then repeat the same steps to apply a thick outside border.
Now that you know how the border and outline tool works, we are going to create additional boxes within the spreadsheet for storing information. Some of these boxes will use different border styles and column sizes. Resize column E and F to 13 units. Resize column G to 15 units. Next, we’ll create a block from cell range F6:F13 and apply a thick border, but this time, only on the left edge. Select the cell range, then click the Borders menu, then Line Style and choose the thick line. Click the Borders menu and then choose Left Border.
We now know how to apply borders in different styles and layouts. Follow the instructions below to design the borders for the rest of the invoice.
Adding Labels and Formatting Headers
The next step is to enter the labels for each cell address with appropriate alignment and font size. Here are some sample labels. You can substitute each label with your own where appropriate. Create a block from cell range A18:F18. Under the Home tab, click the Cell Styles menu then choose the desired style for your headings. I chose the orange 60% – Accent 2.
Your invoice should look like the following at this point.
Adding Formulas for Total and Subtotal Calculations
Now it’s time to bring your invoice to life. This means adding formulas that can help you calculate the cost of your goods and services. Your invoice will likely require a conditional function based on the value of a product or service selected. The formula If(Condition, Value 1, Value 2) produces a value based on the condition. If the condition is true, then value 1 is produced. If the condition is false, then value 2 is produced. The first formula we will enter calculates the amount. For the itemized charges table, we want to calculate the amount by multiplying the quantity (A19) by the unit price (E19). But we only want to do this if the row is filled in. Otherwise, our formula will create an error. To avoid this, we assign the amount cell (F19) a blank value if the quantity cell is blank. In cell F19, enter =IF(A19=””,””,A19*E19). Proceed to fill the cell range F20:F28 with the formula.
Create the formula for the subtotal. In cell F29, enter =SUM(F19:F28)
To create the formula for tax, go to cell F30 and enter =F29*0.50. Swap out 0.50 for whatever your local tax rate is. Enter the formula to calculate “Pay This Amount” in cell F32. Enter =F29+F30+F31
Formatting Numbers
Next, you’ll want to format the right-hand column as currency. Select F19:F32, then click the $ (accounting number format) in the Home menu in the Number section.
Do the same for E19:E28.
Adding Protection to the Spreadsheet
Now that the format and formulas of your invoice are complete, you want to lock down certain cells so they aren’t inadvertently edited, thus breaking your invoice. You can do this by enabling protection on your spreadsheet. Before you enable protection, you need to unlock the cells where you want to enter data. Select cell ranges A17:E28. Go to the Home ribbon and click Format. Look for Lock Cell. If it’s enabled, go ahead and click it to disable it.
Repeat the same steps for the following cell addresses and ranges.
F6:F10F31A7:C10A13:C16
Click the Format menu, then Protect Sheet. By default, “Protect worksheet and contents of locked cells,” “Select locked cells,” and “Select unlocked cells” will be checked. Setting a password is optional and really not necessary in this case. Make sure these settings are selected and click OK.
Now, your spreadsheet will be locked down for editing except for the rows you’ve unlocked for entering data. If you ever want to make changes to your spreadsheet, click Format and then Unprotect Sheet…
All that’s left for you to do now is save your invoice as a template, then take it for a spin. Click File > Save as… > click in the type list box, then choose Excel Template (*.xltx), then click Save.
Every time you need to create a new invoice, you can just launch a template then enter your data. Of course, you might need to customize your invoice based on your specific needs and requirements, but doing so should be a breeze now that you know the basics of formatting and formulas. If you are new to Excel or need to brush up on the basic functions of the popular spreadsheet, be sure to check out our previous article detailing the basics every new Excel user should know. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
