I started loving Excel when I saw how much you could do with functions and
formulas once you dive in there are so many ways you can crunch your numbers
I'm going to show you how to get started with functions and I'm going to show you
some special functions that everyone needs to know I have this simple
spreadsheet showing the battery ranges of all the current plug-in hybrid
vehicles as you can see there's a lot of difference in the mileage between all of
these cars and I want to calculate the average so here's how you start a
formula you go to the cell where you want the formula to be and then you
start typing up here every formula starts with an equal sign that's the
first step and then I can just start typing in average here and as you can
see there a list of functions that show up here is average and I can either keep
typing it myself or I can double-click it to bring it in now when i started
typing that you see there are these other functions that showed up if you're
wondering what those are all you have to do is click the name and you'll get a
brief definition another way to enter a function is through the formulas menu
you just click formulas at the top and at the far left you see a button called
insert function you click that and a dialog box comes up where you can start
typing in the function you want so I want average i'm going to type that in
and hit go and there it is and now that will show up in the cell that i selected
and it's also already guessed the number range i want so i'm going to hit that
there just to show you that the average works but then i'm going to show you
other ways you can find a function and this is my favorite it's the next button
over it's called auto sum and you click that and it comes up with a list of the
most common formulas that everyone uses all the time so look here's average
right here I click it and not only does it put the average formula in the cell
but it goes up an auto selects what it thinks I want to average so it's all
there for me and it's done but there are more ways you also have the next button
over recently used it remembers the formulas that you used most recently and
say you want to use it again it's right there here's a good old average it's
pre-selected my rain I'm done finally you can click one of
these functions subcategories like financial or logical to narrow down what
you're searching for now that you have your function you have to tell Excel
what numbers you want to use with it and you do that in parentheses after the
function so you can enter the numbers manually if you want to for instance I
can just type in b2 with it then put a comma then be three and a comma before
in a comma I hit enter and it will average those three numbers but entering
numbers manually is super tedious right so there are other ways to do it I know
that I want all the numbers so i can actually put in the first cell on the
top then put a colon and then put in the last cell on the bottom which is b12 hit
enter and it automatically averages everything in between those two cells an
even easier way to do it is to highlight the cell range with my cursor just like
that grab the mall hit enter and there's your average again but say that I want
to average only the European cars on this list I can select separate cells
like this i just select my first one then I hit the control button and hold
it down and I click the next one then I go down to the next one and the next one
and this one and as you can see in the formula entry bar on the top each of
these cells has its own colors you can see which one it is in the formula bar
I've got them all so I'm going to hit enter and now it's averaged only those
cars once you're comfortable with functions and formulas you'll want to
learn a few that will save you tons of time and heartache this one is a classic
you have two cells that have the first name and the last name of something and
you need to put those names together into one cell there's a function that
will do this for you and it's called can catch
Nate so here we have a spreadsheet showing four of my favorite apple
desserts but Apple is separated from pie and strudel and so on so i'm going to
use concatenate to put them together as you see I've already done it for apple
pie and now I'm going to do it for apple strudel I start with my equals sign and
then I take a deep breath and I type concatenate and thank God the autofill
saves me I don't need to type all of that now what I do is I select the two
cells i want to put together and that would be a five and here's the tricky
part you need to watch I want a space between Apple and strudel so I actually
have to tell excel I want to put a space in so I put quotation marks with a space
in the middle then another comma and then the cell with strudel in it which
is b5 that's the end of my formula and look at that apple strudel showed up now
that was a really tedious formula to enter but luckily you can just copy it
down to the other ones so I'm going to hit control C for copy or any of the
other ways you can copy a cell and then select the next two cells where I want
the formula hit control V or paste and the formula pastedown it sees the cells
next to it and it does it all for me say you've just concatenated some cells but
there's weird spaces left over because of some formatting thing there is a
function to help you with that and it's called trim so here we are again with my
apple desserts and for apple pie you can see I've already written the formula and
it's very simple all you do is you type the equal sign and the word trim
parentheses and then you just tell it which cell you want to get all those
spaces out of that's a 4 that's it that's all you do and as I did before
I'm going to copy this formula down to fix the other ones notice that it can
remove extra spaces but if you have words stuck together it can't pull them
apart that's how easy it is to get started
with Excel functions and formulas just start playing around and don't worry if
something goes wrong you can always hit the undo button if you have any
questions just email us at answer at pcworld com