Microsoft Excel 2013 Tutorial - 18 - Awesome Formula Tips Video Lecture | Microsoft Excel 2013: Be the Excel Master - Software Development

all right guys so what we saw in the
last video is that Excel already comes
with a bunch of cool built-in functions
now one thing I want to point out is
whenever you're using these built-in
functions there are a couple of
different ways that you actually use
them in other words some functions they
require a range of data or a range of
cells in order to work other functions
they only require one cell or one piece
of information so let's go ahead and
look at something like s Q R T this is
the square root and you can either type
in one number right like that and of
course the square root of 144 is 12 or
instead of 144 you can click a cell
right here and it'll work just fine but
what you can't do is you can't throw in
a range of numbers for square root since
it's like how am I supposed to square
root on several numbers so that's why we
get an error of course anytime you see a
hash tag instead of an expected number
that means that you did something wrong
in this case we're using a range of
numbers other than just one cell or one
value so that's the difference between
functions that take in by the way I
probably should mention this equals s Q
the thing in between the parentheses is
called an argument so um if I kind of
was mentioning argument in the last
tutorial and you guys are like argument
what the heck set it means the extra
data that your function needs in order
to work so some arguments are a list of
cells or a range and some arguments are
just one cell or one number so now that
I talked to you guys about kind of the
built-in functions that come with Excel
and how they work I also want to mention
this anytime you want to use a function
that's built in but you don't um
someone's texted me show you guys let me
just go ahead and read this alright one
of my friends talking about drinking and
gambling with a police officers alright
so there's a lot built-in functions
already included
in Excel and we of course don't know the
names of them off the top of our head I
mean some do that you're going to use a
lot but most of them you don't so
anytime you want to find one what you
can do is you can go up here and click
on this little icon and this is actually
the four function dialog box that pops
up and you can either scroll for the
most commonly used ones or you can
select the category so I don't know
maybe when we talk about text functions
later on you can do that so again that's
this button right here another thing is
if you go to the formulas tab and scroll
over here to insert function it gives
you the same dialog box but maybe I
don't know if you like using the
formulas ribbon a little bit more and
the last thing if you're kind of a
keyboard person if you hold down shift
and hit f3 then they all do the same
thing so again this little button right
here in the formulas tab this or shift
in f3 I'll bring up the functions is
kind of a master list of all the
functions that are included in Excel and
if you click on it of course it gives
you a nice little description of what it
does so this one abs returns the
absolute value of the number simple
enough so let me go ahead and clear out
everything because now I want to talk to
you guys about something that's probably
going to make you cream your pants and
that is this so open up Bucky Sora right
here in to the right of price add a new
column and we'll just call it like uhm
total value and hit enter and let me
just adjust this so we'll say that we
wanted to set this cell right here equal
to the total value of this item so as
you can see this is pretty much um if I
had a fake store these are the items I
would be selling this is how many of
each item I have in stock and this is
the price of that item so I have 23
bread gloves in stock in there selling
for twenty three dollars each however
running so the total value of this of
course we already know how to do this
pretty simple set it equal to the
quantity multiplied by the price so I
have five hundred and twenty nine
dollars worth of bread gloves in stock
now of course we can go ahead and do it
equals and let me just do this okay
be for multiple okay that's taken a long
time so I'll show you guys something
really cool if you click on this
equation right here and click on your
fill handle in the bottom right corner
of the cell and drag down check out what
happens you may be expecting by the way
that's another thing whenever you see a
bunch of hash tags that means your
columns not wide enough so you may be
expecting 5:29 to be copied down a bunch
of times just like if you were to type
three they were to copy like that but
instead what you're seeing is these
different values and these values right
here are actually the perfect answer
that we were looking for so what it does
whenever you fill in a formula is it
doesn't fill in the absolute value
because you're not copying the value
you're actually copying the formula now
since or since you referenced cells
instead of giving it the number 23 what
it does is it automatically detects okay
what you're doing is you're multiplying
this cell to left to the cell want to
left together so this one you must try
to be multiplying these together and
check it out so Excel does an awesome
job better than any spreadsheet program
on earth at pretty much um acting smart
and detecting exactly what you want to
do so check out this I have $21,000 with
the laptops suite got to make some Bank
off that but anyways
with this power actually come some
problems and oh I guess I don't have
enough time to tell you about it but in
the next tutorial I'll show you guys the
problems that might occur whenever you
try to fill in a bunch of empty data
using a formula and I'll also show you
guys how to correct that problem too so
you guys are going to be pro's experts
you'll be freaking sweet good to go but
for now thank you guys for watching
don't forget subscribe if you have any
questions or if you want to see arm-like
if you have any concepts that you want
me to cover and go ahead and send me a
message on my website or just leave a
request in the forum now make sure to
cover those topics as well because I
know that there's a lot of stuff in
Excel that um you know we have to cover
so thank you again and uh well I'll shut
up now I'll see you next time