How to Create an Invoice Template in Excel!

In this session, we’ve explained in easy STEP by STEP details about how to create an Invoice Template by just using some basic sum function and lookup feature without VBA in just few minutes. After this video, not only it will be easier for you to create such basic templates, but also you can get the print outs of this template at a single click.

This template is very useful for beginners, startups businesses and individuals as it helps in managing and organizing all your finances into one place.

We’ve used the following features to create this tutorial.

1. Border Alignments & Formatting
2. SUM function
3. VLOOKUP with IFERROR function
4. Cross Across Selection
5. Page Setup & Print Preview
6. Excel to PDF
7. CONCATENATE function to combine normal text and text from cell reference


Visit us for daily dose of Excel Tips & Tricks at http://www.exceltip.com/

In case of any simple or complicated "Excel" query, you can ask to our experts for instant solution at http://www.excelforum.com/

---

Closed Caption:

hello and welcome back to the exit of
videos today in this session we will
show you how to create an invoice
template using my top except we will
create this invoice template without
using BB and we'll use the basic Excel
functions and features which we have
launched in previous videos to have the
maximum calculation simply five and
automating this template is perfect for
start-up businesses and individuals
alike
it can be used to perform the simple
invoicing task so without wasting much
time
let's see how we can create an invoice
template with the help of basic
functions in excel as you can see we
have a blank sheet here and another
sheet that contains your abilities will
use this product details in the template
to free of that descriptions
anyway we come back to invoice template
thank you
and then we move out cause it to be one
we first insert the company logo here so
we go to insert tab and then we click on
pictures and insert the company logo
this is too big so let's make it
follow we use size of this image looks
okay
we have placed the computer on the left
side of the page
how would in case if you want to place
it anywhere on the page you can place it
as for your own choice then we go to i 1
and we type in voice
let's change the font size of this text
from eleven to twenty four and then
we'll make it bold and now we'll change
the point that as well so we go to .
hello and we'll put a nice . color which
matches here people
this is a new exchange five so we'll
keep it works in five four this invoice
. and now we type the company it is that
are selling the products at the top of
the invoice so we go to be four and we
type company name in the packets for now
we will have a simple text instead of
taking actually kiss but you can change
the display on convenience so variable
we are inserting the text inside the
brackets
you can replace it with the actual
values then in b5 we will have a street
address then city state zip and b6 for
detail and b7 then takes to tail
and b 8 like this is enough for the
company information and now will add the
customer details like name company name
address phone and emails
except so we go to be 11 and first we
type field be too
then we go to be 12
and there we have the customer name so
type name here inside the bracket then
company here in be 13 street address in
b 14 city stay here and be 15 + + b 16
and then we will have email address in
b-17 every invoice must have you
standard it is like customer ID date
invoice number
exactly so in order to add them we go to
sell edge five and we type customer ID
then we type invoice date invoice number
and women do by in columns at six x 7
and wedge a respectable we will have
these details in corresponding cells
from i five - I so we select i five - I
ate change and we go to Home tab and
under fourth category will select all
borders and come back then we select h5
to etch eight and with this control one
to open the format cells dialog box
there we go to alignment tab and we
select slide indent in the horizontal
axis alignment category and increase the
Indian from 0 to 2 and then click on OK
the movie click on ok we can see that
the texts are indented and looking much
better than they were looking for you
can have the visual happy we make these
headings as ball and come back
ok now we can enter the required it is
in the required cells to start with
customer ID we can put any random ID for
now and let's say we enter ET c 2 3 4
then in I six we will have the current
dick and for that we can use to function
as every time we've been making invoice
we must need the current gig and to the
function will return the current date
for us
ok in case you want any other date you
can supply it manually
instead of ending the function in the
set in
I seven we have to enter the voice
number
so for now enter anything them number so
we enter X Y Z 1 2 3 4 z and then in
payment you buy will add 20 days into
the invoice date
I come back
all right now if we look at column ID
the text is going left whereas gets
outside with just blew nice
no problem we'll change the alignment of
the cells to the center and then we'll
change the date format also to make it
look nice to go to number and here in
date we select this day's month and year
formic and then we apply the same way
about pressing f4 key which is used to
repeat the last section
good now let's have the formatting for
bill to free as well so we go to be 11
and highlight the range from being 11 to
f/11 then we go to free color and we
fill this with something . okay this
looks nicer
this is start you with this yes this is
dog loose so we see that dark blue hill
and then white for the font color and
we'll make it
poor after making bold we go to border
area and we select outside border for
the selected area and come back now we
move on to the description part of the
invoice will add description and other
details regarding the items which were
selling to the customer
so we go to be 19 and we type item code
then item description and see 19 as item
description could be plenty so we
highlight seen 19 - f 19 and we will
march across the sets will much across
more cells under the item descriptions
column to have the description of items
but you don't need to go to Home tab
every time and click on much applause
options as you can defeat the last
command using f40 by highlighting the
white area or you can also copy the most
area and a street from the cell in where
you want
by doing this you can save lots of time
then click interesting anyway we have
much the self - 30 second row and we
have enough clothes now to have the
products for this invoice template but
in case if you want more rows you can
add it now we come back to the 19 and we
go to g 19 and we type quantity is
heading as this column will contain the
quantity for these items then we need
unit place for this item image 19 and
then we will have amount in i thinkin
like since we have included all the
categories so now we form at this table
and we make the entries
first we select and highlight be 19 - I
32 inch and then we open format cells
dialog box
they jumped the border tab and with
select line style first and then we
click on outline border the movie click
on outlying bottom you can see the box
this place is a preview that how and
where it will be applied after that we
want to hire each column so we select
the necessary bottle line and then we
click on ok and as soon as we click on
ok we can see that the border has been
applied to the table successfully but we
forward to apply the outline border for
the phone 19 so we selected all 19 and
we best control chip 7 which is a
shortcut key to apply that our plane
bottom once we are done with border
formatting will filled up
hello with dark you and then apply wife
own color to the headings of this table
and then we make them for it
after that we make the changes in the
alignment a sport input type your text
will keep it unchanged as left and four
numbers NT we change it to the center
line
she's the item port and quantity will be
supplied as number so we select item for
change and quantity and then we change
it to the center alignment to Center the
contact the item descriptions will be in
text format so we'll leave it as it is
then in last unit price and amount with
chi to the center line map and then
we'll open the form excel of faults and
we go to number and we'll use the
thousand separator with the zero decimal
place since we have for making our table
of descriptions will now enter the
products so we allow users to only
select the product and the rest column
should automatically be updated so in
order to start with it
we will have the item description part
so we select see 22 °c 32 and then we
first opened the data validation dialog
box we select list and allow category
than in source we go to call up to tell
sheet tab and here we select a - 8:10
that contains the item name here since
we have already made the list of
products but in case if you're going to
add or delete or make any changes in the
product you can make it here then
accordingly you can expand the
revelation listening anyway we recall ok
and now we can select any item and
product name from the drop-down list
then we also be 20
and there we have to pick the item code
based on the items selected in self c20
so to pick that we will use vlookup
function here so we type the lookup
function here so you since Colin sees
towards the value which we want to look
up so we select c20 as a look at value
and then we go to product to tell you
tap to select the table area we select
the table at a range from a to c 10 and
then test f42 make it absolute reference
since the function would be moving in
column be put down side and when we copy
and paste the function to another
location from the original . with the
relative reference that said that
Frances also gets changed with that
and then there are chances that the
function returns any other because it
will not be able to find the look of
value in the table
so if you do not want cell reference to
change use absolute reference to save
the time and as we have to pick the
second column that contains the item
code from the table
daddy we enter two in the third argument
then in the last argument will enter 0
to return the exact match
we hit enter the moment we enter a
function determines item for the
selected item and see
20 anyway we copy and paste the formula
only to be too deep to how this 10
because the formula in the rest cells we
get any other
but why that's because there is nothing
in the corresponding set to look up the
value and functional effect on any error
in the first set
so to avoid returning l witness relook
function inside if our function and will
tell in case any edit occurs it should
return and keep txt we
I'm to the problem ok now we'll pick the
unit price for the selected item and
we'll use the previous method only so
instead of repeating the same action we
go into the Attic more of this cell
the 20 and we copy the entire formula
and then we come back
- 20 and we're going to the added more
once again and now we missed you too
then as we have to pick the third column
that contains unit prize in the product
detail sheet tab so we make the
necessary changes here and then we hit
enter we get the unit price of the
particular selected item but in case if
you want to check you can go to the
product detail sheet and you can look
for the product which we have selected
there in the invoice championship and
you
you can see that the pliers of the per
unit for the starter is 2450 and when
you come back you get the same price
he also ended it with copy and paste on
the function to HT -
now we come to i-20 where we will
multiply the unit value by quantity to
return the mo
so we enter the calculation in F at a
function to return an empty text in case
any other occurs by that calculation
call it now we be - I - E -
now we go to x 34 and here we will type
subtotal then we'll have the text amount
will be next 35 then discount category
in a story six and total in h 37 then we
select I 34
- I 37 and apply all borders and then
we're going to format cells to select
the England currency to be English India
with zero decimal
this is so look here for you
currency is cool it is
and then we'll reduce the decimal places
20 and then click on OK
you can choose any currency format ever
into that requirement and now we change
these cells to the center alignment then
we go and select x 37 and I - 37 and
once again we go to format cells to
change the borders style of these two
sets
so we turn to the bottle tab and there
we selectable lines to apply on the top
order and we'll let the single light
with line for right corner and click on
OK and then we make them as good now we
go to sell
I - 34 and into some function and select
I 22i 32 as some rage and then we enter
in cell i-35 will get the text amount
which will be applicable in the
subjective value so we multiply the
subtotal by the text day
what always applicable and then we'll
leave the discounsel as empty as it will
be entered manually if there will be any
disco and now we come to I 37 and there
we type sum function once again and we
take the sum of
I 34 and i-35 then this subject discount
value from the some written by i
teleport and i-35
now we are almost ready with the tactic
and we add special north and
instructions and other things to give a
final touch to this camping
we're going to be 34 and with type
special notes and actions and then we
select be 34 to 38 and will apply
outline order and then once again we
select me 34 34
we applied outline bored of this change
as well
and then we apply the formatting color
will the point cool
and then we make a mess ok you can
include the special loss instructions
like payment terms are 20 days from the
date of invoice please include the voice
number on both checks will format them
as italic and will reduce the font size
- 10
ok now we'll add the signature areas we
go down and we select page 43 and I 43
and we apply the top model
I go to a line man
and there we select center post
selections in the text alignment
horizontal category and then we people
ok then we come back to equality and
there we type sick nature
have you noticed one thing that X is
starting from the center alignment of
both the cells
that's because of the cross because
selection styles of the alignment we had
selected and the format cells dialog box
isn't it interesting
you don't need to worry to make it
centralized manually anymore or too much
cells for such requirements
now we'll add a few more lines before we
finalize the template to you
so we go to be 14 9
and then we select be 49 - I 14 9
and once again we go to format cells and
will change the line made to Central
Coast selection and click on OK will
repeat this action for the next four
lines 250 third row but instead of
repeating we copy the entire row and the
restrictive to play third row so no need
to go to dialog box again and again to
do the same thing
ok now we're back in be 49 so we type
equal to double quotes make all your
chicks be able to then we add in person
sign and we give us sell their friends
to be for that contains the company and
as soon as we hit enter instead of a
cell reference company name is appearing
along with the text which we have
supplied manually so this is working as
formula and what we do is we have
combined text and a cell reference
together
so whenever you make any changes to the
company name
the check in favor of instructions will
be updated automatically
ok now we come back and here in the next
line-in be 50 we say in case of any
query concerning this
voice please email us at
why your email ID and then in the next
line we say thank you for your business
and hit enter to make it appear better
we convert these lines into italic
format and then we reduce the font size
the first two lines - 10 and then we
make last line as both we are ready
within voice campaign now we go to
folder and select few products and
differences to drop down option in
column C and then we enter the quantity
of all this
and as soon as we update the quantity
you can see all formulas are getting
updated automatically and all the
amounts have been a big so we are doing
the final amount which customer has to
pay so in case there's any discount you
can mention it here that discount will
be subtracted from the total value and
you will be ready with the total value
which customers to be so almost the
entire template is automated except few
places like customer information and
when the information then customer ID
invoice number product we go down and
quantity then we have discount which is
supposed to be filled out manually and
this is not enough when you go and check
the print preview
you don't need to set the page area as
it perfectly fixed on one page so you
can simply give the paint command and
get the printout without spending more
time on each second or in case if you
want to save as PDF
go to file save as and browse the hold
of a want to save the file and it's
going to save here and then
in service type we select PDF and then
we click on Save button
the PDF file will be generated in the
parts selected and it will be open
automatically as with so you can have
the idea that how you excel file
look like when you will change into the
PDF format
so we go back and this is how you can
create automatic invoice template using
simple and basic Excel functions in
excel
also we would recommend you to send
invoice as PDF only to you
customers always if you liked this video
don't forget to click on subscribe
button to get regular updates and feel
free to share this video tutorial video
friends as well that's all for now
we hope you found this such an
informative stay tuned and keep learning
others
thank you and have a nice day