How-To

How to Create Your Own Invoice from Scratch in Excel 2016

invoice-excel-template

Microsoft Excel templates letting you down? Make your own invoice from scratch using these simple steps.

Microsoft Excel 2016 comes with a plethora of templates that are ready for you to use at  the click of a button. So why make your own invoice from scratch? For one, creating your own custom invoice in Excel 2016 lets you modify and tweak the invoice to your particular needs. Second, it’s incredibly easy to do once you learn the basics of formatting and formulas. After we walk you through this tutorial on building an invoice in Excel 2016, you’ll have the skills you need to create invoices that do what you need them to do. It only takes a few minutes.

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 columns
  • Adding labels and formatting headers
  • Adding formulas for totals and subtotals
  • Formatting numbers
  • Protecting the spreadsheet
  • Saving 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 HomeFormatColumn Width and enter 12.

excel-column-width

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.

step-1a

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.

left-border-and-shading-2Click the Borders menu and then choose Left Border.

excel-create-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.

Block Cell Ranges

Border Style

A18:F28Click the Border menu, choose Thick Outside Borders
B18:B28Click the Border menu, choose Left
F18:F28Click the Border menu, choose Left
E18:E28Click the Border menu, choose Left
A29:F31Click the Border menu, choose Thick Outside Borders
F29:F31Click the Border menu, choose Left and Right
F32:F34Click the Border menu, choose Thick Outside Borders

After applying the appropriate style, your invoice should start to look like the following.

excel 2016 invoice tutorial

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.

Labels

Cell Address

Alignment

Font Size

Groovy ServicesA1Left14
123 Groove StreetA3Left12
Beverley Hills, CA, 90210A4Left12
Sold to:A6Left12
Shipped to:A12Left12
Invoice NumberE6Right12
Invoice DateE7Right12
TermsE8Right12
Sales RepE9Right12
Debit or CreditE10Right12
QuantityA18Center10
DescriptionB18Centered across columns; select B18:D18 and click Merge and Center.10
Unit PriceE18Center10
AmountF18Center10
SubtotalE29Left10
TaxE30Left10
FreightE31Left10
Pay ThisF33Center10
AmountF34Center10
Make Checks Payable toC33Left12
Thank you for shopping at Groovy ServicesB37Left12
Groovy Services LLCC34Left10
InvoiceF1Left14

Create a block from cell range A18:F18. Under the Home tab, click the Cell Styles menu then choose a desired style for your headings. I chose the orange 60% – Accent 2.

format-cell

Your invoice should look like the following at this point.

excel-2016-invoice-tutorial-formatted-again

 

 

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.

formula-amount

Create the formula for the subtotal. In cell F29, enter =SUM(F19:F28)

sub-total

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

invoice-structure-2

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.

excel-number-formatting

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.

cell-protection-2

Repeat the same steps for the following cell addresses and ranges.

  • F6:F10
  • F31
  • A7:C10
  • A13: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.

locked-cells

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…

excel-spreadsheet-invoice-example

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.

excel-template

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.


2 Comments

2 Comments

  1. Hardeep Singh  

    Sounds good

  2. Richard  

    GREAT POST! EXACTLY WHAT I NEEDED…..MANY THANKS!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top