ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Invoice Sample

Updated on March 24, 2014
Invoice Template
Invoice Template | Source

My wife decided that she wanted to stay home after the birth of our second son so we decided to Design Studio Creations, our home based business that specializes in customer invitations. One of the challenges to starting a business is all of the startup costs. One place that you can save money is to create your own invoices in Microsoft Excel, because billing software can be very expensive. Many invoice templates are available for download online; however, they are very basic and require more keystrokes to fill out each invoice. In addition, after you fill out each invoice, you will have to manually delete all of the data to use it again. This is the reason that I like to make my own templates so I can make them more efficient and useful. Add a few extra tabs and you can cut the data entry portion down, and, add a macro that automatically blanks out the invoice template so that it is ready the next time that you need it. Another reason that making your own template makes sense is that you can more easily customize it for what you want on it.

Have you ever considered starting your own business?

See results

How to Make an Invoice Template

Open up a new workbook in Excel. Save it as a macro-enabled workbook. Insert an additional four tabs into the workbook so that you have seven altogether. The six tabs to the right will hold data that will be pulled into the main tab.

Screenshot of the Control Tab
Screenshot of the Control Tab | Source

Control Tab

The “Control” tab will be home to all of the dropdown boxes’ cell links that controls what each dropdown box is displaying.

  • Right-click on the far right tab and rename it “Control” and hit enter.
  • In cell A1, label the worksheet as “Control Tab”.
  • In cell A4, type “Ship to Same Address”.
  • In cell A6, type “Customer Name”.
  • In cell A8, type “Salesperson”.
  • In cell A10, type “Shipping Method”.
  • In cell A12, type “Sales Terms”.
  • In cell A14, type “Due Date”.
  • In cell A16, type “Sales Tax Code”.
  • In cells B4, B6, B8, B10, B12, B14, and B16, put a bold bolder around each of them and shade them light green to signify that these boxes will be driven automatically.

Screenshot of the Sales Tax Setup Tab
Screenshot of the Sales Tax Setup Tab | Source

Sales Tax Setup Tab

The “Sales Tax Setup” tab is a great way to set up all of the sales tax codes that you will need to use when you are creating an invoice for a customer. For Design Studio Creations, we only have one location and most of our sales are in Illinois, so we only need to setup one tax code. Many businesses will need to setup multiple tax codes. I setup all 50 states and 1 local tax code on this tab. However, I did make up all of the taxes rates; do not use any of the rates that I have entered. Check with your state department of revenue to see what the proper sales tax rate should be.

  • Right-click on the next tab and rename it “Sales Tax Setup” and hit enter.
  • In cell A1, label the sheet as “Sales Tax Rate Table”.
  • In cell A4, enter “No”. This is just a placeholder in the table to give us the ability to use the Vlookup function and pull data into the main tab.
  • In cell B4, enter “State Code”.
  • In cell C4, enter “Rate”.
  • In cell A5, enter a “1” and leave the rest of the row blank.
  • In cell A6, enter a “2”. Highlight A5:A6, click, and pull down on the small box on the right side of the highlighted area in order to number the column correctly. You only have to go down as many rows as you will have sales tax codes. I would advise to go one or two rows further in case you ever have to add more codes. It is better to insert just above the last row, and then add a new row at the very bottom in order to keep the formulas on other tabs correct.
  • Starting in B5 and C5, enter the sales tax code name and the rate, respectively.
  • Highlight A4:C5 and change the background color to a dark color. Also, change the text color to white or another light color. The reason that I am having you color row 5 is to remind you not to enter anything in that row.
  • Highlight A6 through column C on the last row on your tax table and shade it a light color. I choose light yellow to remind me that this is an area that data has to be keyed in manually. Remember to shade a few extra rows at the bottom to allow for future growth.
  • Also, place a bold border around the entire area.
  • Change the formatting in column C to percent with two decimals. If you are unsure how to do this, click on the “%” button on Excel’s “Home” menu and click the decimal button with the arrow pointing left twice.

Screenshot of the Sales Terms Tab
Screenshot of the Sales Terms Tab | Source
Sales Terms Tab

The “Sales Terms” tab is the place to define your invoicing terms. Businesses that choose to offer credit to their customers, typically offer them net 30-day terms. Some businesses offer a discount if you pay in 10 days of 1-2 percent. Manufacturing companies many times will have terms of 45-60 days from the date of the invoice. If you can get all of your customers to pay up front, that is the best way to go, however many times that is just not practical. In our wedding invitation business, my wife and I require a 50% down payment when you sign a contract with us, with the remaining balance being due when you pick up your order. This is also a great place to enter a discount code(s) if you plan to offer customers a discount if their order meets certain requirements.

  • Right-click on the next tab and rename it “Sales Terms”.
  • In cell A1, enter in “Sales Terms” as the title to the worksheet.
  • In cell A4, enter “No”. This is the same type of column as in the “Sales Tax Setup” tab.
  • In cell B4, enter “Sales Terms Description”.
  • In cell C4, enter “When Due”.
  • In cell D4, enter “Discount”.
  • In cell A5, enter a “1” and leave the rest of the row blank.
  • In cell A6, enter a “2”. Highlight A5:A6 and pull down on the little box on the right hand side of the highlighted area. Leave a few extra rows for future expansion.
  • Enter in all of the customer term information that you plan to use. See the photo of my spreadsheet to the right to see what terms that I used.
  • Highlight A4:D5, and color the background with a dark color. Change the font color to a lighter color, such as white. Change the format in cells A4:D4 to center format.
  • Highlight A6 through the bottom right hand corner of your table and change the background to a lighter color. Use a thin border around each column to make the table more attractive.
  • Change the format in column D to percent with two decimals by repeating the same steps as you did on the “Sales Tax Setup” tab.

Screenshot of the Shipping Methods Tab
Screenshot of the Shipping Methods Tab | Source

Shipping Method Tab

The “Shipping Method” tab may or may not be relevant to your business. Most of our customers pick up their invitations at our location; however, we do ship a few orders out a year. As our business grows, we intend for that trend to change and become more prevalent. If you know what your shipping costs will be, you could set them up in this tab and pull that information into the main tab. All you would have to do is to add a column for the shipping cost, which may require multiple codes for FedEx, UPS, and USPS etc. You would also need to add a column to the left of the shipping method description with numbers that count down the side. Then use a Vlookup on the main tab that looks at the shipping method and automatically adds the total to the invoice.

  • Right-click on the next tab and rename it “Shipping Methods”.
  • In cell A1, enter “Shipping Methods Table”.
  • In cell A4, enter “Shipping Methods”.
  • Starting in cell A6, enter all of the shipping methods that you wish to set up. Leave a few extra rows at the bottom for future expansion.
  • Change the background and font color in cells A5:A6 to a darker color with a lighter font.
  • Highlight from A6 down and change the background color to a lighter color.
  • Highlight the whole table and add a thin border around it.

Alternative Shipping Method Table

Control Number
Shipping Method
Cost
 
1
Cust. Pickup
Free
 
2
USPS
5.25
 
3
FedEx
7.50
 
4
UPS
7.75
 
5
Delivery
8.00
 
If you choose to use this, add a "Cost" heading in B4 and fill in the costs. Then use a Vlookup based on the control number (B10) on the "Control" tab and lookup the cost and pull it into the invoice tab.
Screenshot of the Salesperson List Tab
Screenshot of the Salesperson List Tab | Source

Salesperson Tab

The “Salesperson” tab is a list of all of your sales people. This is a great thing to include on all of your invoices so that you know who created the invoice if there are any future questions. This is an optional tab, especially for really small businesses that do not have any employees, but it is a good placeholder for growth down the road.

  • Right-click on the next tab and rename it “Salesperson”.
  • In cell A1, enter “Salesperson List”.
  • In cell A4, enter “Salesperson Name”.
  • Starting in cell A6, enter all of your sales people’s names into the list.
  • Same formatting as the other tabs, darker header with light text, and a light area where the data is to be keyed.

Screenshot of the Customer Setup Tab
Screenshot of the Customer Setup Tab | Source

Customer Setup Tab

The “Customer Setup” tab is a great idea for businesses that have repeat customers because all of their address information will be stored on this tab. On the main tab, there will be a drop down box that allows you to select the customer name and everything else will self-populate. You will be able to check or uncheck a box if the ship to address is the same as the customer address.

  • Right-click on the next tab and rename it “Customer Setup”.
  • In cell A1, enter “Customer Setup Table”.
  • In cell A4, enter “No”.
  • In cell B4, enter “Customer Name”.
  • In cell C4, enter “Address”.
  • In cell D4, enter “Address 1”.
  • In cell E4, enter “City”.
  • In cell F4, enter “State”.
  • In cell G4, enter “Zip”.
  • In cell H4, enter “Phone”.
  • Change the column width of column I to .5.
  • In cell J4, enter “Address”.
  • In cell K4, enter “Address 1”.
  • In cell L4, enter “City”.
  • In cell M4, enter “State”.
  • In cell N4, enter “Zip”.
  • In cell O4, enter “Phone”.
  • Highlight C3:H3 and click on the merge cells button. Enter “Customer Information”.
  • Highlight J3:O3 and click on the merge cells button. Enter “Ship To”.
  • Highlight A3:H3 and change the background to a dark red and the text color to white. Likewise, highlight J3:O3 and change the background to a dark blue and the text color to white.
  • In cell A5, enter “1”.
  • In cell A6, enter “2”.
  • Highlight A5:A6 and pull the small box on the right side of the selected area and pull it down a bunch of rows. I choose to go down to row 40, but as time goes on you will need to add more. Remember to leave a few extra rows at the bottom to make inserting more rows a snap.
  • Shade all of the numbers that you just pulled down a dark red with a white font color.
  • Black out B5:O5 to keep from entering data from there.
  • Starting in A6, enter all of the customer information that you have. If you do not have ship to information, leave that section blank. It is a good idea in cell C6 to put in a “Freeze Pane” to lock the column and row headings so that they are visible at all times.

Screen Shot of the Invoice Tab
Screen Shot of the Invoice Tab | Source

 

Invoice Tab

Finally, we have now made it to the Invoice tab. All we have left to do is to layout the invoice how we want it and add a few drop downs and lookups. How you want your invoice to look is up to you, it is totally customizable.

  • Rename the final tab as “Invoice”.
  • Highlight cells A1:J46 and change the background color to white. Add a thin border around the entire highlighted area.
  • In cell F1, enter “Invoice No:” and hit the “Increase Indent” button on Excel’s “Home” menu seven times. Change the font size to 20 and format it as bold. I changed to font to “Bookman Old”, but that it totally up to you.
  • In cell I1, enter your starting invoice number. Every time that you issue an invoice, you will need to change the invoice number. Another useful tab that you may want to add is an invoice register, where you keep track of all of your invoices.
  • In cell I2, enter the following formula: =today() which will show the current date.
  • If you want to insert your business’ logo on to the invoice, go to Excel’s “Insert” menu and click on the picture button. This will bring a window allowing you to browse to select your logo picture. Drag the picture so that it is center between the date and the bottom of row 11.
  • In cell B4, change the format to bold and then enter your company name. Enter your address, city, state, zip, phone number, email address, and website on the rows below your company name. Combine city, state, and zip in one row.
  • Highlight cells A1:J11 and change the background to whatever color that you like. Change the font color if necessary. Also, add a thin border around this section.
  • Highlight column A and change the column width to 2.5.
  • Highlight columns B and C and change their column width to 8.5.
  • Highlight column D and change the column width to 12.
  • Highlight column E and change the column width to 16.
  • Highlight column F and change the column width to 9.5.
  • Highlight column G and change the column width to 12.15.
  • Highlight column H and change the column width to 9.
  • Highlight column I and change the column width to 15.
  • Highlight column J and change the column width to 2.5.
  • In cell B13 change the format to bold and enter “To:”
  • In order to complete the next step, click on the Microsoft Office icon and select “Excel Options” from the bottom of the menu. A dialogue box will pop up, under the heading “Top options for working with Excel”, check the “Show Developer tab in the Ribbon” box. Now click on the developer tab. Click the drop down arrow on the folder icon with the tools lying across it. Click the “Insert Controls” button, select the combo box icon, which is the second icon from the left. Draw the combo box in cell B14. Make it the same size as the row and stretch it so that it goes into part of column D. Right-click on the combo box and select “Format Control”, which will bring up a dialogue box. For the input range, go to the “Customer Setup” tab and highlight B5 all the way down to the bottom of your list. In the cell link box, go to the “Control” tab and click B6. I always check the 3-D box because it looks cleaner and click okay.
  • In cell B15, enter the following formula:

=IF(Control!$B$6=1,"",VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,3,FALSE))

  • In cell B16, enter the following formula:

=IF(VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,4,FALSE) =0,"",VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,4,FALSE))

  • In cell B17, enter the following formula:

=IF(Control!$B$6=1,"",VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,5,FALSE)&", "&VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,6,FALSE)&" "&VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,7,FALSE))

  • In cell F13, change the format of the cell to bold and enter “Ship to:”
  • On Excel’s “Developer” tab, click on the “Insert Controls” icon and click on the checkmark box. Draw it in cell F12. Change the description to “Ship to Same”. Now we need to link the check box up with the “Control” tab, so right-click on the checkmark box and select “Format Control.” This will bring up a dialogue box that is looking for the “Cell Link”, which is cell B4 on the “Control” tab. Click the 3-D check box and click okay.
  • In cell F14, enter the following formula:

=IF(Control!$B$6=1,"",IF(Control!$B$4=FALSE,VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1605,2,FALSE),""))

  • In cell F15, enter the following formula:

=IF(Control!$B$6=1,"",IF(Control!$B$4=FALSE,VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1230,10,FALSE),""))

  • In cell F16, enter the following formula:

=IF(Control!$B$6=1,"",IF(Control!$B$4=FALSE,VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1230,11,FALSE),""))

  • In cell F17, enter the following formula:

=IF(Control!$B$6=1,"",IF(Control!$B$4=FALSE,VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1230,12,FALSE)&", "&VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1230,13,FALSE)&" "&VLOOKUP(Control!$B$6,'Customer Setup'!$A$5:$O$1230,14,FALSE),""))

  • Highlight B19:C19 and click on the “Merge & Center” button on Excel’s “Home” tab. Enter “Salesperson” into the cell.
  • In cell D19, enter “Ship Method”.
  • In cell E19, enter “Customer PO No”.
  • Highlight F19:G19 and click on the “Merge & Center” button on Excel’s “Home” tab. Enter “Payment Terms” into the cell.
  • Highlight H19:I19 and click on the “Merge & Center” button on Excel’s “Home” tab. Enter “Due Date” into the cell.
  • Highlight B19:I19 and change the background color to a dark color and the font color to a lighter color. Add a white thin border around each of the cells.
  • Highlight B20:I20 and change the background color to a lighter color.
  • In B20, insert a dropdown box and stretch it to match up with the heading above. Right-click on the dropdown box and select “Format Control” from the menu. The “Input Range” is A5 through the end of your list on the “Salesperson” tab. The “Cell Link” is cell B8 on the “Control” tab. Check the 3-D box and click Ok.
  • In D20, insert another dropdown box. The “Input Range” should be A5 through the end of your list on the “Shipping Methods” tab. The “Cell Link” is cell B10 on the “Control” tab. Check the 3-D box and click Ok.
  • In F20, insert another dropdown box. The “Input Range” should be B5 through the end of your list on the “Sales Terms” tab. The “Cell Link” is cell B12 on the “Control” tab. Check the 3-D box and click Ok.
  • In cell H20, enter the following formula:

=IF(TYPE(I2+Control!B14)=16,"",I2+Control!B14)

  • This will cause the due date to auto calculate based on what the sales terms are. Change the background color to be the same as you used at the top of the invoice.
  • In cell B22, enter “Qty”.
  • In cell C22, enter “Item No”.
  • Highlight D22:G22 and merge the cells together as one. Enter “Description” into the cell.
  • In cell H22, enter “Unit Price”.
  • In cell I22, enter “Line Total”.
  • Highlight cells B22:I22 and change the background color to a dark color and the font color to a lighter color. Also, add a white thin border around each cell. Change the format to bold.
  • Highlight cells D23:G23 and merge them together. Copy the cell and copy it down to cell D38.
  • In cell I23, enter the following formula and copy it all the way down to I38: =H23*B23
  • Highlight B23:H38 and change the background color to a lighter color. Also, add a thin border around each cell.
  • Highlight I23:I38 and change the background color to a light color but different from the one above. Also, add a thin border around each cell. Change the cell format to currency.
  • Highlight G39:H39, merge the cells together, and change the format to bold. In addition, change the cell alignment to right justified. Change the background of the cell to match whatever color you used to shade I23:I38. Copy this cell all the way down to G44.
  • In cell G39, enter “Subtotal”.
  • In cell G40, enter “Discount”.
  • In cell G41, enter “Taxable Total”.
  • In cell G42, enter “Sales Tax”. Add a dropdown box to the left side of the cell that has the “Input Range” of B5 all the way to the bottom of the list on the “Sales Tax Setup” tab. The “Cell Link” needs to be set to B16 on the “Control” tab.
  • In cell G43, enter “Shipping/Freight”.
  • In cell G44, enter “Total Due”.
  • Enter the following formula into cell I39: =Sum(I23:I38)
  • Enter the following formula into cell I40:

=VLOOKUP(Control!$B$12,'Sales Terms'!$A$5:$D$500,4,FALSE)*I39

  • This will do a look up on the “Sales Terms” tab, pull in the discount amount if there is one, and multiply it by the “Subtotal”.
  • Enter the following formula into cell I41 to determine the taxable total: =SUM(I39:I39)
  • Enter the following formula into cell I42 that will pull in the corresponding sales tax rate and come up with the dollar amount:

=IF(ISERROR(I41*VLOOKUP(Control!$B$16,'Sales Tax Setup'!$A$5:$C$1272,3,FALSE)), 0,I41*VLOOKUP(Control!$B$16,'Sales Tax Setup'!$A$5:$C$1272,3,FALSE))

  • Cell I43 is a cell that requires a manual input. Change the color of this cell to the same color that you used for the main body of the information detail.
  • Enter the following formula in cell I44 to determine the total amount due: =SUM(I41:I43)
  • Highlight I39:I42 and change the background color to match what you used above to determine the line total. In addition, change cell I44 to the same color.
  • Highlight I39:I44 and add a thin border around each cell and change the format to currency with two decimals.
  • Highlight A1:J46 and set the print area. Make sure that the invoice will print on a single page.

Finishing Touches for the Invoice Template

I strongly suggest adding a macro to clear out the “Invoice” tab so that it is ready for use the next time that you need it. On Excel’s “Developer” tab, click on the “Create Macro” button, and a dialogue box will open up. Name the macro “ClearInvoice” and click Ok. Click on the “Control” tab and type “False” in cell B4. In cells B6, B8, B10, B12, and B16, enter a “1” in each cell. This will reset the dropdown boxes so that they are blank. Go back to the “Invoice” and in cell, E20 hit the “Delete” key. Highlight B23:H38 and hit the “Delete” key. Click on cell I43 and hit the “Delete” key. Finally, click on cell N1 and hit the stop macro button. On the “Developer” tab in Excel, add a button by clicking on the “Insert Controls” button. Assign your macro to it and change the name on the button to “Clear Invoice”.

Setting up your Invoice Template

Once you have gone through each tab and added the necessary data, it is time to fill in your business’ information in the top section of the “Invoice” tab. Add your logo by inserting a picture and dragging it on to the top right of the invoice where the placeholder is. Assign your invoice a starting number and the form is ready to use.

Screenshot of Completed Invoice
Screenshot of Completed Invoice | Source

How to Write an Invoice

Creating an invoice is simple with your new invoice template. Select a customer from the dropdown box. If the customer needs to be setup, go to the “Customer Setup” tab and enter their information there. Check the “Ship to Same” address box if the customer’s ship to address is the same as their billing address. Select the appropriate salesperson, shipping method, enter the customer’s PO number, and select their payment terms. Next, enter the specifics of what you are billing the customer. Select the appropriate sales tax code and enter the cost of any freight that is to be billed to the customer. I suggest printing out two copies when you create an invoice, one for the customer and one to keep for your records. Also, it is a good idea to start an invoice log to keep track of what invoice number you used last. It will make calculating your sales at the end of each month much simpler.

When working in Excel, remember that a little bit of setup on the front end can save a lot of time and hassle on the back end. Feel free to add more to this template or simplify this template however, you wish.

Comments

    0 of 8192 characters used
    Post Comment

    • William Young profile image

      William Young 4 years ago from Eaglle Grove, Iowa

      Excellent and very useful information! Very informative!

    • Faith Reaper profile image

      Faith Reaper 4 years ago from southern USA

      Wow, very useful and detailed hub here. Voted Up In His Love, Faith Reaper