Design an Invoice Form with Microsoft Excel
Today, most business computers are equipped with Microsoft Office which includes useful programs such as Word and Excel. If you are in need of a custom invoice form for your business, you already have the program that you need to design such a form. You may be a little surprised to learn that Microsoft Excel is a great tool for designing your own forms. If you think of it, most forms are simply composed of rows and columns that create boxes that can be filled in. Since Excel is a spreadsheet program, it can be relatively easy to set up the boxes needed to create your own form. Follow this thorough tutorial to learn the ins and outs of designing an invoice form from scratch. You may need a form that is a very basic or you may need a really complex form. Our goal is that by supplying this tutorial, you will get started in the right direction.
Step 1: Open Up Microsoft Excel
Look for Microsoft Excel’s Shortcut on your Desktop. If you find it, double-click the icon to start up the program. You will also be able to start Excel from the Windows button in the lower left corner of your taskbar.
Click on Windows Button > All Programs > Microsoft Office > Microsoft Excel.
Step 2: Setting Up “Margins” in a Spreadsheet Program
You are going to create a printable form in Excel. Since this is a spreadsheet program, it is hard to visualize how your form will look on a piece of paper. At this point, you can see dozens, if not hundreds of cells; how do you know what will fit on a single piece of paper? You definitely don’t want to create a design that will be bigger than the size of paper that you are considering using. It is time to create some boundaries.
Click on Page Layout at the top of your menu bar.
Click on the Size button. You will see a list of common paper sizes. Unfortunately, with Excel, you are limited to designing a form with one of these sizes.
Select Letter size.
You will now see dotted lines appear within the spreadsheet. These dotted lines show you which cells will print on a single letter sized paper in their current state. If you are sticking to a single page form, you want to keep your design within the very first box created by the horizontal and vertical lines.
Step 3: Your Company’s Information
Here is an example of an invoice form that we are going to re-create in this tutorial. If you follow this tutorial step by step, your finished form will look very similar. After the tutorial, you can make some necessary changes to this form to fit the needs of your business. If the form that we provided as an example doesn’t work for you, feel free to look for other layouts on the Internet. We are going to start designing the form by placing your company’s information at the top. This can be a personal choice; the company information can be placed on the left side, centered or on the right side of the form. We are going to place the information in the center so that there will be enough room to add a logo on the left.
Select cell E:1 and enter your company’s name.
Select cell E:2 and enter the street address.
Select cell E:3 and enter city, state and zip code.
Select cell E:4 and enter telephone number.
Select cell E:5 and enter fax number or website address.
If you enter in a website address, Excel will make into a hyperlink, the color will change and if clicked, it will open up in a web browser. To make the website address just regular text, right-click your mouse button and select Remove Hyperlink.
Step 4: Drawing Your Form with the Border Tool
Since the spreadsheet is composed of columns and rows, you can easily translate it into a form. With one tool, you can draw your form by simply changing the appearance of the pre-existing cells. Technically, you are not building a form from scratch; you are going to use the lines that Excel gives you to build your perfect form.
Select Column A by single-clicking your mouse on the gray box with “A” in it.
Right-click your mouse and select Column Width.
Type in 1 as your Column Width.
Click OK.
Make Column I the same width by repeating the steps above.
Figure out how many rows you will need for your form. Based on the form that we want to replicate, we are going to need 23 rows.
Click on the Home tab.
Click on the drop down arrow for the Borders tool.
Select the thickest line (last option) within Line Style.
Single-click your mouse button within cell B:8, continue to hold down your left mouse button.
Drag your mouse to cell H:30. You should now see a box with a thick border.
Click on the drop down arrow for the Borders tool.
Select the second thickest line within Line Style.
Your mouse icon should look like a little pencil. We are going to use this tool to draw some of the inner lines of the form.
Single-click your mouse in the lower left corner of cell B:8 and continue to hold down the mouse button.
Drag your mouse to the right staying along that bottom line until you have reached the lower right corner of cell H:8. You should now a solid vertical line (See Highlighted Line).
Repeat the same steps to draw an additional 4 vertical lines.
Skip the next 10 lines.
Draw the next two vertical lines starting at the lower left corner of Cell B:23 and B:24.
Draw two shorter lines. The first will start at the lower left corner of B:26 and end at E:26.
The second line will start at the lower left corner of B:27 and end at E:27.
Draw a full line starting at the lower left corner of cell B:28.
Draw a shorter line starting at the lower left corner of cell B:29 and end at the lower right corner of E:29.
Draw a vertical line on the left side of cells G:8, G:10 and G:11
Draw a vertical line on the right side of cells B:12 – B:23.
Draw a vertical line on the right side of cells E:12 – E:30, all the way down to the bottom of the form.
Draw a vertical line on the right side of cells F:12 – F:30.
Click on the Borders tool > Line Style > Dotted Line.
Draw a vertical dotted line on the right side of cells G:13 – G:30.
We are almost done; we just need to draw a few thinner lines.
Click on the Borders tool>Line Style>Thin Solid Line.
Beginning with the bottom of Row 13, draw 10 horizontal thin lines starting from the left side of the form to the right side.
Draw a vertical thin line on the bottom of cells F:25 – H:25.
Repeat the same lines for the bottom of cells F:26 – H:26 & F:27 – H:27.
You are now done with drawing your form. If you go to File > Print, you will see a preview of what your form will look like on a single sheet of paper. As of right now, your form does not take up an entire sheet of paper. By adjusting the height and width of the rows and columns, the form will have enough room for it to be filled out. In the Print Preview, you will only see the lines that you have drawn with the Border tool. The light gray lines that you see in the entire spreadsheet during the editing stages are not printed out.
Step 5: Adjusting Form for a Letter Sized Sheet of Paper
Single-click your left mouse button and hold it down on row 8 (in the gray box), drag your mouse until you have highlighted rows 8 – 30.
Right-click your mouse button and select Row Height.
Enter 22 as the Row Height then click OK.
Select Column B.
Right-click your mouse button and select Column Width.
Enter 12 as the Column Width.
Repeat the same steps to change the widths of the following columns:
Column Width of C – 17
Column Width of D – 7
Column Width of E – 18
Column Width of F – 16
Column Width of G – 14
Column Width of H – 7
You may notice that Column H is now outside of the dotted line that determines which cells will fit on a single sheet of letter size paper.
Click on Page Layout > Margins > Narrow.
This will change the right and left margins from .70″ to .25″, allowing your form to take up a little more room on the page. Refer to your printer settings to verify if a .25″ margin is allowable or do a test by printing the form out in its current state. If you see everything on your print-out then your printer can handle these margins. If your print-out doesn’t have a solid line on either of the sides, you may have to increase your margins. After increasing your margins, you may have to lessen the width of one or two of your columns until your form is within that dotted line.
Click on File>Print to see a preview of what your form looks like. You should see that the form now takes up most of the page. To go back to editing your form, click on the Home tab in the menu bar.
Step 6: Adding Text to Your Form
We are now going to add text to some of the cells. Before adding any text, you can select your preferred font; for this tutorial, we are going to stick with Calibri.
Type the following text in the listed cells:
Cell B:8 – NAME
Cell G:8 – DATE
Cell B:9 – ADDRESS
Cell G:10 – RESIDENTIAL PHONE #
Cell B:11 – LOCATION
Cell G:11 – BUSINESS PHONE #
Notice how especially in Cells G:10 and G:11, the text takes up the entire cell leaving almost no room to write anything in. We need to change the size as well as location of the text within these cells.
Select all of the cells that you just typed text in. Do so by single-clicking each cell with text while holding down the Shift button on your keyboard.
Right-click your mouse over one of the highlighted cells.
Select Format Cells.
In the Alignment tab click on the drop-down arrow for Vertical.
Select Top.
Click on the Font tab.
Type 7 in the Size field.
Click OK.
All of the text that we highlighted is now smaller and hovers at the top of cells. This allows more room to handwrite the customer’s information. To allow a little more room you can increase the height of Rows 8-11.
Single-click and hold your mouse button over Row 8 (the gray box) then drag your mouse down to Row 11 to highlight these four rows.
Right-click your mouse button and select Row Height.
Enter 30 as your Height and click OK.
You now have plenty of room to write in the customer’s information including the phone numbers.
Single-click and hold your mouse button while over Cell B:12, drag your mouse over to the right to highlight all of the cells in that row but do not go past your thick solid black line.
Click on the drop-down arrow for Fill Color.
Select one of the lighter grays.
While the cells are still selected, click on the Middle Align and Center Text buttons in the Alignment section of the Home tab. This will center your text within the cells both vertically and horizontally.
Type the following text in the listed cells:
Cell B:12 – QUANTITY
Cell C:12 – DESCRIPTION
Cell F:12 – PRICE
Cell G:12 – AMOUNT
Notice how DESCRIPTION and AMOUNT are technically centered within the cells but they are not centered between the solid lines.
Click on Cells C:12 – E:12 while holding down your Shift button.
Right-click your mouse over the highlighted area.
Select Format Cells.
In the Alignment tab, select Merge cells.
Click OK.
Now DESCRIPTION is centered properly. We combined the three selected cells to make one large cell. By doing this, it does not alter any of the cells in those same columns.
Repeat the same steps for Cells G:12 and H:12. AMOUNT will now be centered within the combined cells.
Type CUSTOMER’S SIGNATURE in Cell B:24.
Right-click over that cell.
Select Format Cells.
Select Center as your Vertical Text Alignment and click on the Wrap text box in the Alignment tab. If you are typing a lot of text in one cell, you should choose Wrap text. If you don’t, the text will just keep going to the right. Unfortunately the text will not be visible if you enter text in the adjacent cell.
Click on the Font tab and change the font size to 9.
Click OK.
Type X in Cell C:24.
Change the font size to 16 points.
Select Cells F:24 – F:29.
Right-click your mouse over the highlighted cells.
Select Format Cells.
Select Center for both Horizontal and Vertical Text Alignments.
Click OK.
Now type the following text in the listed cells:
Cell F:24 – TOTAL MATERIALS
Cell F:25 – TOTAL LABOR
Cell F:27 – SUBTOTAL
Cell F:28 – TAX
Cell F:29 – TOTAL
In Cell F:26 type in o SERVICE CHG.
Hit Alt+Enter on your keyboard which will allow you to enter text on the next line within that same cell.
Type in o TRIP CHG.
Highlight the “o” in front of SERVICE CHG.
Change the font to Wingdings and you should now see a box.
Highlight the “o” in front of TRIP CHG.
Change the font to Wingdings as well.
If Excel changed your lowercase “o” to an uppercase “O”, just re-type it.
Select Rows 24 – 30.
Right-click over the highlighted rows.
Select Row Height.
Enter 27 as the Row Height.
Select only Row 26.
Right-click and select Row Height.
Enter 40 as the Row Height.
Select Row 29.
Right-click and select Row Height.
Enter 15 as the Row Height.
Select Cells F:29 and F:30, do so by single-clicking each cell while holding down your Shift button.
Right-click and select Format Cells.
Select Merge Cells.
Click OK.
Type the following text in the listed cells:
Cell B:27 – SIGNATURE
Cell E:27 – DATE
Cell B:28 – ADDRESS
Cell B:29 – IF AUTOMOBILE
Cell B:30 – YEAR
Cell C:30 – MAKE
Cell D:30 – LICENSE/SERIAL NUMBER
Select Cells B:27, E:27, B:28, B:30, C:30 and D:30 (All of the recently entered text except for “IF AUTOMOBILE”).
Right-click your mouse over one of the selected cells and select Format Cells.
Select Top as the Vertical Text Alignment under the Alignment tab.
Click on the Font tab.
Change the font size to 7 points.
Click OK.
Select Cells B:29 – E:29.
Right-click and select Format Cells.
Under the Alignment tab, select Center as the Horizontal and Vertical Alignment.
Check off the Merge cells box.
Click OK.
With the “IF AUTOMOBILE” cell selected, change the font size to 9 points.
Select Cells B:25 – E:26
Merge the cells.
Type in the following text: “AUTHORIZATION FOR SECURITY/EMERGENCY SERVICES (Press Alt+Enter to start new line) I hereby certify that I have the authority to order the lock, key or security work designated above. Further, I agree to absolve the locksmith who bears this authorization from any and all claims arising from the performance of such work.”
Adjust the size of the text that you have just entered until you can see all of it. In this example, we have decreased the font from 11 points to 10 points.
Double-click your mouse within that same cell and highlight the first line “AUTHORIZATION FOR SECURITY/EMERGENCY SERVICES”. Bold the text so that it stands out.
Increase “TOTAL” in Cell F:30 to 16 points.
Now that we have the text where we need it in the lower left corner of the form, we can add some final lines.
Click on the Borders tool.
In Line Style, select the thin solid line.
Draw a line on the left side of Cells E:27, C:30 and D:30.
Step 7: Add Logo to Form
Select cells B:1 – C:5.
Merge the cells.
Click on the Insert tab in the top menu bar.
Click Picture.
Select the location of where you have your logo saved. The logo that we use in this tutorial is saved on the Desktop.
Select the logo to be inserted and click Insert.
You may need to resize your logo.
Single click your logo.
Click on the lower right anchor point of the logo, then drag your mouse to the right to increase the size. Try to keep your logo within the lines of cells that you merged together.
You can nudge your logo by using the directional keys (â,â,â,â) on the keyboard to adjust the location of your logo. We are going to nudge this logo down, almost centered with the company’s information.
Step 8: Final Touches
We are going to make the company’s name stand out a little more.
Select Cell E:1.
Increase the font size to 16 points.
Select Cells E:1 – E:5.
Center the text by clicking on the button circled in the image below.
Select Cells G:1 & H:1.
Merge the two together.
Type in “WORK ORDER”.
Select Cells G:2 & H:2.
Merge the two together.
Type in “INVOICE”.
Select both WORK ORDER and INVOICE.
Change the font size to 14 points.
Center both.
In Cell G:4 type in “No.”
If you plan on having a commercial printer print your forms, they can sequentially number your entire order. So leave this area as is. If you think you will have the Invoice Number handwritten in, select the Borders tool and draw a line on the bottom of cells G:4 – H:4.
Take a look at your finished form. Make any necessary changes if needed before going on to the next step.
Step 9: Saving Your Document
If you haven’t already, it is time to save your file. We suggest that you occassionally save your file as you are creating it. It would be a terrible thing to come so far only to lose your design due to a power outage or because your computer freezes up. Save your file as an excel spreadsheet document so that you can make changes to it at any point.
Click File>Save As.
Select the location on your computer where you want to save your form. A popular choice is to save it in your Documents folder.
Type the name that you wish to give your file in the File Name field.
Make sure that Excel Workbook is selected as the File Type.
Click Save.
If you want to be able to upload your form so that it can be professionally printed onto carbonless paper, you should save your file as a PDF. A PDF can be easily opened by just about any computer. You won’t have to worry about your form getting printed with an incorrect font. Since a PDF won’t be editable, the file can’t be altered accidentally before it gets printed.
Click File > Print.
Click on the Margins button and select Custom Margins.
Select Horizontally under Center on page. This will move your form perfectly in the middle.
Click OK.
Click Save As.
Select the location where you want to save the PDF file. We would suggest that you save it in the same folder and as the same name.
Select PDF as the File Type.
Make sure that the file is optimized for publishing online and printing by selecting the Standard option.
Click Save.
You are now done creating a form from scratch in Microsoft Excel. The beauty of using Excel for designing forms is that it provides a backbone for any form. A form is simply made of rows and columns and that is how this program operates. You can really build a simple form with Excel or something a little more advanced like the form in this tutorial. It is because of the tools that we were able to make some cells wider or taller by merging them. Drawing the lines to create a form is easy, again, because Excel is based on rows and columns; just follow the gray lines already given to you. We hope that you have found this tutorial useful. Come up with your own design and build it from scratch in Excel using the techniques learned from this tutorial. The benefit to designing your own form is that you can print it out yourself. This is perfect if you don’t need a large quantity. If you would like a larger quantity and possibly have your form printed into carbon-less sets, printers like Print It 4 Less will be glad to assist you with your printing needs; we can print your own customized form. We look forward to being your source for tutorials on how to set up forms and other printed materials that most businesses need.
Categorised in: Tutorials
This post was written by Progressive Printing Team
