Functions are a great time-saving option for calculating data in your worksheet. They can
be used in place of formulas when a formula would be too long, or just too complicated
to write out manually.
To access the function library, go to the Formulas tab on the Ribbon. Here, you'll find
hundreds of functions to choose from. They've all been organized into categories to make
them easier to find.
For example, under Financial, you'll find functions that deal with things like interest
and monthly payments. Under Text, you can perform text manipulations,
like converting lowercase to uppercase, replacing text, and more.
Date & Time includes functions that return the current date, time, day of the week, and
other useful data. Some of the most common functions are pretty
simple, though (like SUM and AVERAGE). You can access these using the drop-down arrow
under AutoSum.
In this example, I'd like to add these ten cells to calculate the total. If I tried to
create a formula for that, I'd have to add each cell individually.
Not only would that be tedious—it would also make the formula really long, especially
if I had, say, a hundred cells that I wanted to add. So instead of creating a formula,
I'm going to use a function.
First, select the cell where you want the answer to appear. Then choose a function.
In our case, we'll open the AutoSum menu… and then choose Sum.
Now Excel has inserted the function, and also selected the cells that are being added together.
If it selects the wrong cells, you can always change that by dragging the corner handles
here.
When you're done, press Enter on your keyboard… and the answer will appear.
Let's look at this function in a little more detail.
Just like formulas, functions always start with an equal sign. But you may have noticed
that there are no mathematical operators (such as addition or subtraction) like you would
normally see in a formula. Instead, functions use a specific syntax that includes the name
of the function… followed by one or more arguments in parentheses.
The arguments tell the function which cells or numbers to use when calculating the result.
In this example, we only have one argument, even though there are two different cell references.
This is called a range of cells, or a cell range.
A cell range is represented by two different cell references with a colon in between. In
this case, the range consists of all of the cells between D3 and D12. If you want to use
more than one argument, you'll need to separate each one with a comma.
Let's try a different function now. In this example, we have a column for the date that
each item was ordered, and the date that it was received. What I'd like to know is how
many business days it took for each item to be delivered.
First, select the cell where you want the function to go. Then choose a function if
you know what category it's in.
If you don't, you can try searching for it instead. Just click the Insert Function command
on the far left of the Ribbon.
Searching for functions can be tricky, but for more experienced users, this can be an
extremely useful tool. Just type a description of the function you're looking for. I'm looking
for something that'll count the number of days that have elapsed, so I'll type "count
days," and then press Enter.
We've gotten a lot of different results here. To learn more about a function, just click
the name… and a description will appear below.
The function called NETWORKDAYS returns the number of workdays between two dates, and
that's exactly what we want. So I'm just going to click OK… and another dialog box will
appear.
There will be separate fields for each argument in the function. This function has up to three
arguments: Start_date, End_date, and Holidays. When you click a field, you'll see a description
of it below. And if you need more help, you can click the link that says, "Help on this
function."
For the start date, I'll select cell E3… and for the end date, I'll select F3. We know
the Holidays argument is optional because it's not in bold, so I'm going to leave that
one blank.
Before you continue, you can check the result of the function here. When you're ready, click
OK… and the function will be added to the worksheet.
Just like with formulas, you can copy the function using the fill handle here.
And now we can see the number of business days it took for each item to be delivered.
If you've never used functions before, the syntax may seem a little strange at first.
But with some practice, you're bound to get the hang of it—and soon, you'll be using
your favorite functions over and over.