Design an Invoice Form with Microsoft Excel

October 31, 2012 10:56 am Published by Leave your thoughts

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.

 

Design Invoice In Excel - Microsoft Excel

 

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.

 

Design Invoice In Excel - Setting Up Document for Letter Size Paper

 

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.

 

Design Invoice In Excel - Boundaries for Letter Sized Form

 

Step 3: Your Company’s Information

 

Design Invoice In Excel - Form Example

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.

 

Design Invoice In Excel - Entering Company Information

 

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.

 

Design Invoice In Excel - 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.

 

Design Invoice In Excel - Select Column A

 

Right-click your mouse and select Column Width.
Type in 1 as your Column Width.
Click OK.

 

Design Invoice In Excel - Enter Column Width as 1

 

Make Column I the same width by repeating the steps above.

 

Design Invoice In Excel - Columns A & I With Width of 1

 

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.

 

Design Invoice In Excel - Thickest Line Style from Borders Button
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.
Design Invoice In Excel - Draw Outer Border for Form
Click on the drop down arrow for the Borders tool.
Select the second thickest line within Line Style.

 

Design Invoice In Excel - Select Medium Thick Line

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).

Design Invoice In Excel - Draw First Vertical Line
Repeat the same steps to draw an additional 4 vertical lines.

 

Design Invoice In Excel - Draw 4 Additional Lines for Customer Info

Skip the next 10 lines.
Draw the next two vertical lines starting at the lower left corner of Cell B:23 and B:24.

 

Design Invoice In Excel - Draw Lines Near Bottom of Form

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.

Design Invoice In Excel - Draw 2 Shorter Lines
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.

Design Invoice In Excel - Last 2 Medium Thick Horizontal Lines
Draw a vertical line on the left side of cells G:8, G:10 and G:11

Design Invoice In Excel - Medium Thick Vertical in Customer Info
Draw a vertical line on the right side of cells B:12 – B:23.

Design Invoice In Excel - Medium Thick Vertical Line for Qty Column
Draw a vertical line on the right side of cells E:12 – E:30, all the way down to the bottom of the form.

Design Invoice In Excel - Medium Thick Vertical Line for Description Column
Draw a vertical line on the right side of cells F:12 – F:30.

Design Invoice In Excel - Medium Thick Vertical Line for Price Column
Click on the Borders tool > Line Style > Dotted Line.

Design Invoice In Excel - Select Dotted Line
Draw a vertical dotted line on the right side of cells G:13 – G:30.

Design Invoice In Excel - Draw Dotted Line in Amount Column
We are almost done; we just need to draw a few thinner lines.

Click on the Borders tool>Line Style>Thin Solid Line.

Design Invoice In Excel - Select 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.

Design Invoice In Excel - Draw 10 Thin Lines
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.

Design Invoice In Excel - Draw 3 Thin Short Lines
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.

Design Invoice In Excel - Select Rows 8 - 30
Right-click your mouse button and select Row Height.
Design Invoice In Excel - Changing Row Height for Entire Form
Enter 22 as the Row Height then click OK.

Design Invoice In Excel - Enter 22 for Row Height
Select Column B.
Right-click your mouse button and select Column Width.

Design Invoice In Excel - 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

Design Invoice In Excel - All Column Widths Changed
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.

Design Invoice In Excel - Change Margins to 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 #

Design Invoice In Excel - Add Text in Customer Information Section
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.

Design Invoice In Excel - Select Cells with Text
Right-click your mouse over one of the highlighted cells.
Select Format Cells.
Design Invoice In Excel - Select Format Cells
In the Alignment tab click on the drop-down arrow for Vertical.
Select Top.

Design Invoice In Excel - Align Customer Info Text to the Top of Cell
Click on the Font tab.
Type 7 in the Size field.
Click OK.

Design Invoice In Excel - Formatting Text in Customer Information

Design Invoice In Excel - Smaller Text in Customer Information
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.

Design Invoice In Excel - Select Rows 8 - 11
Right-click your mouse button and select Row Height.
Enter 30 as your Height and click OK.

Design Invoice In Excel - Height of Customer Info Rows Increased
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.

Design Invoice In Excel - Select Row 12
Click on the drop-down arrow for Fill Color.
Select one of the lighter grays.

Design Invoice In Excel - Filling in Row 12 with a Light Gray
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.

Design Invoice In Excel - Aligning Text in Row 12
Type the following text in the listed cells:

Cell B:12 – QUANTITY
Cell C:12 – DESCRIPTION
Cell F:12 – PRICE
Cell G:12 – AMOUNT

Design Invoice In Excel - Text for Row 12
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.

Design Invoice In Excel - Merge Cells
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.

Design Invoice In Excel - Description and Amount Text Centered
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.

Design Invoice In Excel - Wrap Text in Customer Signature Cell
Click on the Font tab and change the font size to 9.
Click OK.

Design Invoice In Excel - Change Size of Customer's Signature Text
Design Invoice In Excel - Customer's Signature Cell
Type X in Cell C:24.
Change the font size to 16 points.
Select Cells F:24 – F:29.

Design Invoice In Excel - 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.

Design Invoice In Excel - Alignment for Cells F:24 - F:29
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

Design Invoice In Excel - Enter Text for Cells F:24 - F:29
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.

Design Invoice In Excel - Enter Text for Cell F:26
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.

Design Invoice In Excel - Use Wingdings Font for "o"
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.

Design Invoice In Excel - Increase Height for Rows 24 - 30
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.

Design Invoice In Excel - Change Height for Rows 26 & 29
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.

Design Invoice In Excel - Merge Cells F:29 & F:30
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

Design Invoice In Excel - Entering Text for Cells B:27 - D:30
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.

Design Invoice In Excel - Aligning Text for Cells B:27 - D:30
Click on the Font tab.
Change the font size to 7 points.
Click OK.
Design Invoice In Excel - Font Size for Cells B:27 - D:30
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.

Design Invoice In Excel - Alignment for Cells B:29 - E:29
With the “IF AUTOMOBILE” cell selected, change the font size to 9 points.
Select Cells B:25 – E:26

Design Invoice In Excel - Merge 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.

Design Invoice In Excel - Text for Merged Cells B:25 - E:26
Increase “TOTAL” in Cell F:30 to 16 points.

Design Invoice In Excel - Increase Size of TOTAL
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.

 

Design Invoice In Excel - Select Thin Solid Line

Draw a line on the left side of Cells E:27, C:30 and D:30.

Design Invoice In Excel - Draw Vertical Lines in Bottom Left Section

 

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.

Design Invoice In Excel - Select Cells B:1 - C:5 for Logo
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.

Design Invoice In Excel - Insert a Logo
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.
Design Invoice In Excel - Resize Logo
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.

Design Invoice In Excel - Center Logo to Company Information if Needed

 

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.

Design Invoice In Excel - Increase Size of Company Name
Select Cells E:1 – E:5.
Center the text by clicking on the button circled in the image below.

Design Invoice In Excel - Center Company Information
Select Cells G:1 & H:1.
Merge the two together.
Type in “WORK ORDER”.

Design Invoice In Excel - Work Order Cell
Select Cells G:2 & H:2.
Merge the two together.
Type in “INVOICE”.

Design Invoice In Excel - Invoice Cell
Select both WORK ORDER and INVOICE.
Change the font size to 14 points.
Center both.

Design Invoice In Excel - Center and Increase Font Size for Work Order & Invoice
In Cell G:4 type in “No.”

Design Invoice In Excel - No. Cell
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.
Design Invoice In Excel - Form Design Complete

 

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.

Design Invoice In Excel - Click 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.

Design Invoice In Excel - Saving an Excel Workbook File
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.

Design Invoice In Excel - Center Document on Page
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.

Design Invoice In Excel - Saving Form as a PDF
Make sure that the file is optimized for publishing online and printing by selecting the Standard option.
Click Save.

Design Invoice In Excel - Select Standard Optimization
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.

Tags: , , , , , , , ,

Categorised in:

This post was written by Progressive Printing Team

Back to blog