Invoices are effective tools to use to record sales transactions. At the start of a new year when you sit down to prepare your annual taxes, invoice copies can be used to help validate number and currency amounts received from product and service sales. Using software packages like Microsoft Excel, Intuit QuickBooks or MyInvoices & Estimates, you can create your own invoices. If you don't have an invoice software package and want to create your own invoices using basic Excel, follow the below steps.
- Skill level:
Things you need
- Microsoft Excel
In a blank Excel document type "Invoice" in all caps across the top of the Excel worksheet in Column B and Row 1 (B1). Click "Enter" and type your company name, mailing address, telephone and fax numbers, e-mail address and website link in cell B2. For example, "ABC Company, 123 Main Street, City, State, Zip, 555-555-5555, email@example.com, www.www.www." To return to the next line within the same row, click "Alt" and "Enter."
Tab to cell E1 and type "Date." In cell E2 just below the date field, type "Invoice No."
In cell A6, type "Bill To." Populate this field with the customer's name and mailing address. Tab to cell E6 and type "Ship To." Use this field to populate the address to which the customer wants the products shipped.
Create Product and Pricing Fields. Type "Item Number" in cell A12. In cell B12 type "Description." Use these fields to populate the identification number and product type the customer purchased. For example, you could type "A105 Extra large white T-shirts."
In cell C12 type "Unit Price.." Use this field to populate the cost of each individual item. For example, if one extra large T-shirt cost £4.50, type that here. After "Unit Price" in cell D12 type "Quantity." Use this field to account for how many copies of the product the customer ordered.
Last, in cell E12, type "Line Total." Use this field to calculate and reflect the total cost of the T-shirts. For example, if the customer purchased 10 T-shirts at £4.50 the total cost would be £45.10.
Set column width. To broaden the fields to leave enough room to type in the exact information, highlight columns A through E. Click "Format," "Columns," then "Auto Fit Selection." If you want to widen the columns further, highlight the columns you want to widen then "Left Click" on your mouse and drag the columns over until the width meets your specifications. To bold or underline the column headings, highlight the column heading labels (Item No., Description) then click "Format," "Cells,","Font" and select "Bold" and "Underline."
At the bottom of the invoice in cell A35, type "Notes." Use this section to type special instructions that accompany the order such as "Full payment is due within 30 days of date of this invoice." Highlight columns A through C and Rows 35 through 40 by "Right Clicking" your mouse and dragging it across these columns. Then click "Format," "Cells," "Border" and "Outline."
Tab to cell D30 and type, "Subtotal." Type "Tax" in Row 31 just below "Subtotal." Type Shipping & Handling" in Row 32. Type "Total" in Row 33. Type "Total Due" in Row 34. These steps complete your invoice template. Next we will add formulas to your template to allow for automated calculating.
Add quick mathematic formulas. Insert formulas in your invoice in the "Line Total" cell (C13) by clicking the "equals" sign then click "" above the number 8 and "Unit Price" (cell D13). Hit the "Enter" key to set the formula. The string looks like "=C13D13". This will automatically calculate the cost of each Line Total by multiplying the quantity purchased times the unit price.
Copy and paste the formula down if the customer purchased more than one item. To do this right click your mouse and select "Copy." While continuing to "Right Click" your mouse, drag the mouse down Column E until all product lines are filled with the formula. Then click "Paste." This will populate the formula in the new cells in Column E.
Create a formula to calculate the tax by clicking your mouse in cell E31. Then click the "equal" sign and type "C30," the "" symbol above the number eight at the top of your computer keyboard. Next type ".06" which represents six per cent. If the tax in your state is not six per cent, type the exact amount of tax you are required to collect in place of the ".6". Click "Enter" to set the formula. The string looks like "=C30.06".
Finally, in cell E31 next to Subtotal, set a formula by clicking the "equal" sign. Highlight cells E31 through E33. Click the "colon" sign, which is next to the Backspace key. Click "Enter." This will add the subtotal, tax, shipping and handling fields and calculate the grand total the customer owes. The string looks like "=E31:E33"
Set Invoice for Printing. Click "File" and "Print" to send the invoice to the printer. To adjust margins click "File," "Page Setup" and adjust your margins to meet your personal specifications. Generally Excel documents start with one-inch margins at the top, bottom, left and right. Save the file on your personal computer. To create new invoices, right click on the worksheet "Tab" label, click "Move or Copy," click "Create a Copy" and move the copied invoice template to the second time. This will allow you to keep copies of all your invoices in one location.