Create an Invoice in Excel 2010

http://myexcelcharts.blogspot.com

How to create a basic invoice with lookup and auto-sum features.

---

Closed Caption:

hi in the session i'm gonna show you how
to create an invoice and I said you are
showing some items and you want to
create invoice for customers and you had
situations where when you enter then the
quantity of an item you want to pick
from a list of items that you had maybe
had a set amount of items 10 to 20
different types and when you selected
that the pressure automatic key
automatically come up and when you enter
in quantity and it would automatically
calculate that quantity and also
calculate the total and whatever sales
text that you had and give you a grand
total that you would give to your
customer and this would show up and you
can print it out eventually
you can print it out into this type of
form and one page eight by 11 and half
by eleven form and give it your customer
just called to see how we can create
that now the key to creating these type
of forms is all before you do any of the
formatting do all the nice colors and
font changes and formulas is to set your
text in the appropriate location so what
I've done is basically I just copy all
this all this and put it into another
sheet and it just doesn't happen for
mantle and what what this does is it
gives you a a space to place it where
you want to place it before you do a lot
of the fancy formatting because once you
do the formatting you might go back and
change the the way that the text is and
you always want to put your text in the
place where it's most appropriate before
you do any formatting
yes you'll find that once you do some
formatting you might think that the
placement the text is not in the correct
location and you have to adjust it and
do format again
so it's always best to do your text
placement before you do any formatting
so what we're going to do is now since
we just have our text here
we are going to format it so this is
just all plain text right now there's no
formulas or anything here so i'm going
to do is first we want to know how we
can get that nice white background
instead of having these gridlines so
what we want what we do first is we want
to select the areas where we want to
provide that nice white background so we
looked in our previous example here
pretty much the whole thing that whole
space was all this nice white background
so what we want to do is we want to
change the grid line colors so i'm going
to select the area that this form is
going to encompass and do a press ctrl
one to bring up the format cells and
when the style doesn't really matter i'm
just going to stay with the default
style
the color is what is key we want to
change that to a white color change that
to a white color and we want to have all
the cell change from great white
so I'm just going to click go ahead and
click inside and that wine you can see
that now the the border boxes are
selected that just means that that it is
selected for those lines
now if we click another color let's say
we're in black and we select the outline
you'll see that it shows it up here but
since we selected white
it seems like it's not selected
but it is because basically it's white
so once i click ok you'll see that all
the gridline colors are gone so now this
now we can start to do some of the
formatting that we wanted
so we can select hear that invoice that
was blue and we can go under cell styles
are you just painted whatever color you
want
I kind of like the cell Styles here i'm
just going to give it the accent here
and what we notice also since we had
originally selected white for our border
colors the orders in between hair turn
white so I'm going to turn those off and
select that and go and press ctrl one to
bring back the format cells
let's select our color may be up so it
was this color blue accent yes blue
accent one and just select that
now it's changed now you can see the
colors different i can probably increase
the font size here a little bit i think
ahead at 18
double-click it to increase the cell
size here see that how this is cut off
your problem double click here to
increase that size
so this part was all white so i don't
have to worry about that this part
there was a great brak background so i'm
going to select that and i'm going to
give it the there's none here so I'm
just going to select from here and i'm
going to choose the white background one
there
and then get also get rid of these lines
here website and select the dead
control one here and let's go back to
the gray here we're going to select that
great and select the middle part
so it's going to get rid of those lines
now those are gone so now i also want to
have the the dark lines for here
the border lines for here so i'm going
to select that area and since the
default is black lines i can just go
ahead and go under here the border and
just select all borders and I can
probably do the same here for borders
and this one our borders and see how
that the Texas cut off here
I can probably just increase the tech
increase the column size there and let's
see I think I wanted this one
I just wanted the border all around it I
don't want it in between I just want it
all around so i'm going to select these
two and it's going to select the outside
borders
all right so it's got that and I wanted
that the beautiful little bit of shade
of red so I'm going to go and not that
red
maybe I go to my cell styles
it's the accent maybe i'll give it just
an orange axon accent6 now I want to get
rid of that line there right so I select
that control one and try to find that
orange color think it was this color -
Orange accent6
and then just get just take that now
that that color shows up there so that
color should be that bored should blend
end and now this one I want to change
that to read and also center it so I
want to do is select my selection first
all right and then press ctrl one in the
alignment tab go horizontal center
across selection and click ok so what it
does is it centers it across the
selection that you had if I did emerge
and i'll show you what happens with
merge right now
it emerged what it's going to do it's
going to merge all these cells into one
so for example in the previous I had
this all see this is all CDE but you
don't see the lines
you don't see the lines but if you go
here you see oops
we go here you see the lines right here
right if i go over here
I'll still see the border here each cell
now if i do emergency center I select
this going to emergent center you can
see it's all in one cell now in a way
that's what Merchant Center does and
that's the difference between centering
across the selection just centers across
it but keeps the cell intact or Merchant
Center emerges all the cells together
into one
I'm go ahead and do the same here Morgan
center Merchant Center and now I have
pretty much my-my format for it and in
this invoice state visit the way the XL
c states and seasoned says the numbers
and the between the period is on the
left of it is the days and on the right
of it is the seconds in a day
so for example to this one will
represent this member actually
represents sep tember 30 2012
so what we want to do is probably just
change that to a date format right here
you can enter in the invoice number
whatever in which number you like to
provide
so all we do is also kind of copy the
formatting here to these other cells
here so what I can actually do is I can
just select this role and click on the
format painter and just apply it here
and you can see it just copied all the
formatting so you don't have to keep
going
each down each line to to do the
Merchant Center it doesn't fall for you
and I thought you don't need to sell the
this portion here so i'm going to go
ahead and take it off a new control one
and I'm gonna go back into the border
here
go to white and what I want to do is I
want to get rid of the I want to get I
want to turn the orders in between white
and then the ones at the bottom and also
the ones to the right that one
I mean left white so if i click OK
pretty much it should have taken care of
that you see now all those really just
white border line cells
so where do i get that drop down
that drop down is going to come from a
named range so basically what it means
is i would just select a range of cells
and give it a name so for example i
select the cell and has a proud name one
let's go ahead and show you how to do
that let me just go and delete this
named range first
and I go under the name manager and i'll
just go ahead and delete product name
from there
yes close there's a couple ways you can
do it but the easiest way you can
probably create a named range is to
select your range of cells and a pair
and go
I'm skype prod name so now basically it
has selected this selection of cells is
called pregnant so I'm going back in a
manager and select product name
you can see that it refers to sell a two
day 10 and that is how you select create
a names range
so how do we get that drop-down
capability so since I copy that's over
it's called widget one with you - I'm
going to delete that because i was from
the old
that's good from this one actually for
other new one we called a pname 1pm 2pm
three
so based off of the named range that
created I can actually go back and do a
data validation for that name range
the data validation feature is in under
the data tab under data validation
click data validation here and i want to
create a data validation based on a list
that list
the source is going to come from that
name brand i created prod product name
I can either if i remember i can just
type equal prod name or i can just press
the f3 key and that's going to bring up
the pace name function and I want
product name click ok click OK and then
i can select it will be product name
one two or three pm-12 3d name to like
miss Stewart pname one a name - and then
maybe just be named three
you can see the selections available
here to know how do i get the price to
come over
automatically well that's going to be a
vlookup so what I want to do is create a
vlookup for that product name it seems
that product name product name1 bring
that ten dollars probably in 23 bring
that the respective prices so it should
be look up i'm going to type equals the
lookup just tab complete that
look up this name under
this table the table array which is here
okay , and the column index numbers
which com do we want to bring back
I want to go back the second column 1 20
second column and I wanted to have a
exact match so false is zero you can get
the type false or the number zero little
tap falls here and now
10 20 30 right so 10 product name - if
we go ahead and just click on the fill
handle here and bring it down
you will see 10 20 30 well but now we
have this enable that's kind of a kind
of irritating
so what we can do is we can say here if
there is an error there bring back blank
if not bring back the price what we can
do is type in if statement so that if if
this look up comes back with in there
and that use the is error function if
that results in an error then
- double quotes basically give me a
blank invest if this is true
give me a blank if not then I want you
to do the vlookup excel so I'm just
going to go ahead and copy that control
C and then ctrl-v to paste and then
closed parenthesis and press enter
whoops what do we do we need another
parenthesis here
all right answer
yeah excel also said we needed another
parenthesis at the end so it was smart
enough to figure that one out
click oh yes and I can go ahead and just
drag that and copy it down and now it's
blank
so it doesn't when it sees that when it
sees that there's there's a result
doesn't find anything in the results in
error it will be a blank
and what about here where it
automatically calculates the price well
that's basically a former so i can just
say this is it gave this amount as I
copy it over
I can just say here equals
the quantity this is a shift eight to
get that multiplication symbol times the
price and then press enter now I try to
copy this down
you see I have also the same thing here
now i also want to create and kind of if
statements say okay if it's blank over
here then
then make it black don't don't do the
formal if there's a number here then go
ahead with the form
so basically i'll start off here
actually what i can do is this little
night and these neat little trick if i
can select this first cell and drag it
now so i just made a whole selection but
this one is the active one
so here what I can do as I say I can
create an if statement if this cell is
blank
ok so there's a is is blank statement is
blank
if this cell is blank and click that
cell
close parentheses and that's how was
blank then two quotations black
otherwise then multiply b-17 the
quantity times the price going to press
ctrl enter and when you press control
enter it will create the it will go
ahead and put the form under now in cell
i-17 and also in my 18 19 everything but
if you just press enter it's just going
to put it in that cell so i press
control enter and it will activate this
format for all the cell's you found an
error in the formal so what did what i
did was i forgot to put the closing
parenthesis there but it excel figured
it out
click yes and boom there we have it
so now i can just say ok now it's pretty
much done and I can just kind of verify
all right
oh wait we still have our subtotal so
this subtotal formula is basically
equals the sum of this area here right
oops press escape it's going to be the
sum of this area select I 7922 closed
parenthesis press ENTER and the tax is
going to equal the sum this subtotal
here
shift F shift aight up for the
multiplication times the sales tax here
so that and then i can just press enter
because actually excel smart enough to
figure that part out and then this is
the sum of these two
so go ahead and equal sum and then just
select those two cells closed
parenthesis press ENTER and there you go
and for this i can actually just do some
formatting maybe I want to have it
centered
and now i can just go ahead and test my
other selections here
I select p1 and since its
see when they pass a one-percenter value
then show up so I can see what happened
so what happens is this is actually a
very common error
there's actually a very common error so
look look at the vlookup here
so the vlookup looking at a to be four
which is a to be four but when I copied
it down here
it moved it up to a3 be five so i went
to a 3 B 5 so what happens that when you
copy it down
we copy this formula down the cell ship
relative to the next cell and this is
very common in Excel what you want to do
is you want to make them a absolute cell
reference
so you so when it copies down it keeps a
to be four constantly instead of
shifting at a to be five and then maybe
go a three to be six
so what you need to do is when i click
here
press the f4 key that have the dollar
signs in front of the letter and the
number you see that I want to have her
for both instances do the same thing
here
do the same thing here press ctrl enter
the same cell
and once I copy that down you'll see
now when I look at the next cell that's
the same at the same and that's the same
thing same thing is probably happening
here
we're actually does in here I don't
really need to worry about it because
when I copy it down i wanted to reflect
be 17 x 8 17
I want to reflect that role I want to
reflect that will be 18 times H 18
and the same for this role in this role
but in this row since i'm doing that we
look up
I want that look up table to stay the
same I want always to look up that table
right
that's what they look a bit now I'll
have links to my other videos that show
how to use the lookup in this video so
you can see how the vlookup function
works but this is how you can create an
invoice and what you can see in the
print preview its we're missing our
invoice up here but that's probably a
formatting issue with excel we can see
it up here
what's happening and now it's it's moved
it off to the side here so see that that
line
that means it's going to print on the
next page
hey this is the print area select that
and go to file and go to print and the
sea
what we can do is adjust the margins so
sometimes we have to do a little bit of
formatting with the margins
maybe want narrow margins we have narrow
margins and then we show it up a little
bit better
click on home and see now that dotted
line indicates where the margins are for
the printing
so now we can look at it and Justin see
ok here we are here's our invoice
company name so to cetera and that gives
us our invoice so this was a little bit
longer than I expected but i hope this
helps
thanks for watching