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:
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:
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 49th row and paste it till 53rd 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 email@example.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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
You can adjust all of your cookie settings by navigating the tabs on the left hand side.
Strictly Necessary Cookies
Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.
If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.