How to Create an Invoice Template in Excel

In this article we will show you how to create an Invoice template using MS Excel by using simple functions.

An invoice is a little piece of paper that proves that a sale has been made. Little though it may be but it’s still a very powerful voucher your business just can’t do without.

Every business startup and individual entrepreneur alike, requires a perfect template for their day to day invoicing needs and that’s where MS Excel comes in.

Using Excel, you can create very attractive yet very professional looking Invoices for your business and in this tutorial we will help you in doing just that.

As you can see, we already have a product list with us located on another sheet named as “Product List”, on the basis of which we will create our Invoice.

Any Invoice Template is incomplete without its business/company’s logo. So to begin with, we will first insert the logo of the company in the worksheet where we will design our template.

So, to insert the logo of the company follow the below steps:

• Go to cell B1
• Click on the Insert tab
• Select the Pictures option and from the available options select the logo of the company

• Click on Insert.

Once the logo is placed appropriately on the sheet, you can resize it according to your requirements.

• Next, go to cell I1 and type “INVOICE” to provide a title to your template.
• Change the font size of the title to 22 and make it Bold.
• Go to the font color option and change its color as well to “Blue, Accent 5, Darker 50%”.

Now that we are done with basics, we will start with filling in the details.

First, we will enter the details of the company selling the products. And to do:

• Go to cell B4 and enter the “Company Name”.
• In cell B5 enter “Street Address”, followed by “City, State, Zip” in B6,
• Enter Phone and Fax number of the company in B7 and B8 respectively.

The details (sample text) within brackets can be replaced it with actual values according to your specific requirements.

Next, we will enter the customer details in to this template and to do that:

• Go to B11 and type in “Bill to” in it to signify the start of customer details.
• Then enter “Customer Name” in B12
• Type “Company Name” in B13
• Type “Street Address” in B14
• Type “City, State, Zip” details in B15
• Type “Phone Number” in B16
• Type “Email ID” from in B17.
• Go to the H column and type in Customer ID in cell H5
• Type “Invoice Date” in H6.
• Type “Invoice Number” H7.
• Type “Payment Due By” in cell H8.

Next we will make a small table in the “I” column where the values for the above categories will be added.

So, select cell I5 to I8, go to the Home tab and from the Font category select All Borders option.

• Now, select H5, H6, H7 and H8 and press the CTRL +1 (Learn 200+ keyboard shortcuts). This shortcut will open the format cell dialog box.
• From there, go to the Alignment tab and
• Select right indent from the Horizontal text alignment category
• Increase the Indent from 0 to 2 and click on OK.

As, you can see, now the text looks more indented and better than before. Now we will make this text Bold to further enhance its appearance.

• Now, enter the values for the above details in the corresponding table we made. We will type in the “Customer ID” and for the “Invoice date” we will make use of the TODAY function as an invoice always needs to reflect the current date.

• So go to cell I7 and enter the TODAY function and press enter. Then enter the “Invoice Number”.
• Next we have to enter the date when the payment will be due. So, to do that: Click on cell I8 and type “=I6+20” and then hit enter.

As you can see that doing this has returned the date that will fall after 20 days from today. You can add the days according to your preferences to reflect due date.

• Now, select I5 to I8 and change its alignment to center so that it appears more organized and neat.

• We will also change the format of the dates by going to cell I6, going to the cell format option and then from the number tab select the date category and from there, choose the “d-mmm-yy” format and click on OK.

• Apply the same formatting to I8 by clicking on the cell and then pressing F4, which is used to repeat the last action.

Next we will format the Bill to field and to do so:

• We will go to cell B11 and select B11 to C11 by holding down the Shift + Right Arrow key.
• Next go to the fill color option and select dark blue color to fill the selected area.
• Then make the font of Bill to text white and make it Bold.
• Now to get a border for the selected area, again go to the Font category and select Outside Border from the Border menu.

The final Bill to field will look like this

• Moving on to the description part of the items that we are selling. So, type in Item Code and Item Description in cell B19 and C19 respectively.
• As item description will take more space, we will therefore select C20 to cell F20 and then going to the alignment category under Home tab click on the Merge and Center option to merge these cells.

• Select cells from C19 to C32 and merge them too by pressing F4.
• Now type in rest of the product details like QTY (quantity) in cell G19
• Type Unit Price H19
• Type Amount in cell I19
• Select cells C19 to I32 and open the Format Cells dialog box.
• Go to the Border tab
• Select the line style of your choice
• Click on the outline border option.

• Select the required border line to highlight each column and then click on it and now we have a border for our product description part.
• Create an outline border for the title row of this table by selecting C19 to I19 and press the CTRL+SHIFT+7 (Learn more Keyboard Shortcuts), which is a shortcut for creating outline border.
• Now, fill dark blue color in the heading row and make the text. Then select the Item Code and Quantity column and select Center Alignment option for them from the Home tab as these columns would contain numbers in them.
• Next, select the Unit and Amount columns, select center alignment for them and open the Format Cells dialog box.
• Then go to the Number tab and select the number category and check the “Use 1000 separator box” and make the decimal places as 0. Click on OK.

• We are done with formatting of the item description box and now we will enter the required values in it.
• Now we will go to the Item description box and create a drop down list for the products and to do that we will select the entire column and go to the Data tab.

• From there go to the Data Tools category and click on the Data Validation option.
• From Data Validation dialog box select list from the Allow menu and for the Source go to the Product Detail sheet tab and select A2 to A10 containing the Product Names and click on OK. As you can see, now we have a drop down list of all the available products

Now we want that whenever we pick an item from the drop down product list we created, we should automatically get its item code in column B and to do that we will apply the Vlookup formula in cell B20.

So, in cell B20 the formula would be:

=VLOOKUP (C20,'Product Detail'!$A$2:$C$10,2,0) 

And the moment we hit enter, the function returns the item code for the corresponding product in cell C20.

Next we will nest the Vlookup function entered in B20 in Iferror function to avoid any errors from occurring. Therefore we will now update the formula in B20 as follows:

=IFERROR (VLOOKUP(C20,'Product Detail'!$A$2:$C$10,2,0),"")

Note:  we have nested the Vlookup function within Iferror function so that in absence of products in the C column the B column (item code) does not generate any error.

Now copy the formula in B20 and paste it to the entire range of item code to automatically generate item codes whenever more products are entered in the C column.

To automatically generate Unit Price, we will go to cell H20 and enter the following formula:

=IFERROR(VLOOKUP(C20,'Product Detail'!$A$2:$C$10,3,0),"")

And the moment we hit enter we get the price of our first product. Now copy this formula to the entire range of Unit price to automatically generate prices whenever more products are entered in the C column.

Coming to cell I20, here we will enter the following formula to multiply Unit Price with quantity to get the amount value:

=IFERROR (H20*G20,"")

And hit enter. Now that we have the total amount, we will copy the formula to the entire amount range to automatically generate amount whenever more products are entered in the C column.

Now for the finishing touches:

• Go to H34 and type "SUBTOTAL" and press enter
• In H35 type TAX AMOUNT and press enter.
• In H36 type “DISCOUNT“ and hit enter
• In H37 type “TOTAL” and enter.
• Select cells I34 to I37, go to the Font category under Home tab and apply All Borders.
• Then go to Format Cells option and from the number category choose currency option.
• From the symbol list select the option “Rs. English (India)” and reduce decimal places to 0 and click on OK.
• Change the cells to center alignment.
• Then select H37 and I37, go to Cell Formatting and from there go to Borders tab and apply the double line as the top border and single light weight line as the right most border and click on OK. Make it Bold.
• Go to I34 and enter the Sum function as: =SUM (I20:I32) < copy the formula to the entire Amount range < press enter. Doing this will automatically sum the amount and produce subtotal.
• In I35 we are supposed to get the Tax amount that will be applicable on the Subtotal value, so we will multiply the subtotal value with the percentage of tax applicable like so: =I34*14% and thenPress enter.
• Once again enter the sum function in the Total value cell and subtract the discount form it. Enter the3 Sum function like so: =SUM (I34:I35)-I36 and press enter.
• As you can see, all the values are now generated on our Invoice.

Adding Special Notes and Instructions:

• Go to cell B34 and type “Special Notes and Instructions” in it.
• Select B34 to F38 and apply outline border.
• Again go to B34 and apply outline border for the heading row.
• Fill dark blue color in the Headline row, change its text color to white and make the text Bold.
• Enter the required notes and instructions in B35 and B36.
• Now, to provide a place for signature, select H43 and I43, go to cell formatting and apply top border from the Borders tab.
• Go to the Alignment Tab and from the Horizontal Text Alignment category select Center Across Selection to improve the appearance of this part and Click OK.
• Type “Signature” in H43.

• Next we will select cell B49 to I49 and right click and go to Format Cells option and change Horizontal Text Alignment to “Center Across Selection”. Click OK.
• To apply this action to the next three rows, copy the entire 49^th row and paste it till 53^rd row.
• Go to B49 and type “=Make all your cheques payable to"&B4”. Adding the “&” ampersand symbol with this formula will automatically generate the [Company Name] along with your text.
• In cell B50 type “In case of any enquiry concerning this Invoice, please email us at xyz@abc.com”. You can replace the sample email id with original contact address.
• In B51 type “Thank You for Your Business!”
• Change the font of B49 and B50 to Italics and reduce font size to 10.
• Make the text in B51 as Bold and Voila! We are done with customized Invoice Template.

The final look of your Invoice will look something like this:

So, that is how you create an Invoice template using MS Excel.

Video: How to Create an Invoice Template in Excel!

Check out this interesting video tutorial where we talk in detail about creating an Invoice Template using simple techniques in Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com