Excel Tutorial Creating an Invoice This tutorial will show you how to use Microsoft Excel to create a basic invoice, used in Ontario, Canada.
Creating an invoice template Saving a template for your invoice will allow you to create multiple invoices for a variety of different orders by just filling up the blanks. To create a template for your invoice: 1. 2. 3. 4.
Open a new Excel document. Click File > Save as. In the Save as type drop-down menu, select Excel Template Click Save.
Adding a header and a footer with your company information You can input your company logo, company name, contacts, copyrights and other information into a header and footer of the invoice. To create a header and a footer: 1. In the Insert tab, select Header & Footer. A header and footer appears in you Excel document.
2. Place the cursor where you want your logo in the header, and in the Design tab, click Picture and upload your company logo. Note: Before inserting an image of your company logo, resize it to fit the size of the header.
3. Click on the header section where you want the document name to appear, and type “Invoice”. 4. To add information into the footer, scroll down to the footer and enter the information in the footer section.
Adding billing information The upper part of an invoice typically contains billing information and the information about an invoice receiver. To add billing information: 1. Click the first cell under the header and type “From”. 2. In the cell to the right, enter your company name and address. Note: You can merge two or more cells together to allow more space for the company address. To merge cells: a. Highlight the cells you want to merge. b. Right click and select Merge. 3. To add the “To” section, click on the part of the document where you want it to appear and type “To”. 4. Merge the next two cells to the right from “To” and leave them blank for the name and the address of the company that receives your invoice. 5. Under the “To” cell, type “Invoice #” and leave the cell to the right blank for the invoice number. 6. Under the “Invoice #” cell, type “Invoice date” and leave the next cell blank for the invoice date.
7. Under the “Invoice date” cell, type “Due date” and leave the next cell blank for the due date.
Adding product list Creating a blank product list will allow you to fill up the invoice later with items ordered and automatically calculate the costs and the total.
Creating a header for the product list 1. 2. 3. 4. 5.
In the first column, under the basic billing information section, type “Item”. In the last column, type “Cost”. In the column before the “Cost”, type “Amount”. In the column before the “Amount”, type “Unit Price”. Merge the remaining cells in the row between “Item” and “Price” and enter “Product description”. You have the header for your product list.
Adding a body of the product list 1. To define your product list borders, highlight the area, where you want your product list to appear (including the product list header), click on the Frame button in the Home tab, and select the type of the frame you want.
2. Merge cells in the “Description” column for each row in the defined area.
Adding formula for calculating cost The cost for each item is calculated as the price multiplied by the quantity ordered. To set an automatic cost calculation for each item: 1. Click the cell under the “Cost”. This is where the cost for your first item in the product list will appear. In our example, it is cell I7. 2. Press “=”. Equal sign simultaneously appears in the formula line.
3. Click the cell under the Unit Price. This puts the cell coordinate into the formula line. In our example the cell coordinate is G7.
4. Press “*” and click the cell under the “Amount” (H7). The whole formula appears in the formula line.
5. Press Enter. The automatic cost calculation is set up.
6. Copy the formula into all the cells of your whole “Cost” column: a. Hover over the cell with the formula and place the courser in the bottom right corner of the cell. The courser should turn into a cross. b. Click and drag the cross down to the last cell of the “Cost” column. Your formula is copied into each cell in the “Cost” column.
Adding subtotal cost The subtotal cost is the sum of the costs of each item on the product list. To add subtotal cost: 1. Highlight all the cells of the “Cost” column, plus two extra cells. 2. In the Home tab, click AutoSum. The sum of all the values appears in the last highlighted cell.
3. In the cell to the right to your auto sum, type “Subtotal”.
Adding HST In Ontario HST is 13%, so we will calculate 13% of the subtotal. To add the auto calculation for HST: 1. Click on the cell you want to display HST and press “=”. 2. Click on the cell with your subtotal. The coordinates of the cell containing the subtotal value appear in the formula line. 3. Press “*”, followed by 13%.
Your formula appears in the formula line.
4. Press Enter. 5. In the cell to the left to the HST value, type “HST”.
Adding total cost Total cost is a sum of subtotal and HST. To add the auto calculation for the total cost:
1. Click the cell you want to display the total cost and press “=”. 2. Click the cell that contains your subtotal value, press “+” and then click the cell that contains your HST value. The formula for the total value appears in the formula line. In the cell before to the left to your total value, type “Total”.
Adding notes section The last section in the Invoice is a box for special notes. It appears as a blank box where you can later add textual comments. To create a notes section: 1. Highlight the area where you want your notes to appear and merge cells. Typically, it is the area between your product list and the footer. 2. Create a border around your notes section.
3. Type “Notes” in the newly created notes section and align the word to the right and to the top.
4. Save your changes to the Excel document.