If you’re
fairly new to Microsoft Excel, you’ve probably learned to enter data into
cells to provide consistent formatting, and give yourself an easy way to update
information, such as your office budget. However, many people brand new to
Excel don’t realize how much easier the power of Excel’s formulas can make your job of keeping information current and
accurate.
A formula
is a recipe that lets you make calculations in Excel based on any number of
factors. A formula can do something as simple as adding 1+1, or can be complex
enough to look up data in a database and return a result that is dependent upon
data that you enter into the spreadsheet. By the end of this document, you will
know how to create your own formulas and become familiar with a few of Excel
more useful formulas.
You should
already know how to enter data into a cell in an Excel
spreadsheet.
The Equal (=) sign, the formula bar, and formula parts
Let’s start
with a very simple formula: =100+50.
Note that
the formula above starts with an equal (=) sign. This is the signal to Excel
that you’re not going to enter ordinary data into a cell, but, rather, you will
be entering a formula into the cell—a formula for which Excel will then need to
calculate a result. If you were to enter =100+50 into a cell in Excel, you
wouldn’t see the text “=100+50” appear on the screen. Rather, the
value of the formula—in this case 150—would appear instead. So as not to
completely confuse you, Excel does show you the actual contents of the cell
rather than the value, though… it’s just in a different area of the screen
called the formula bar. In Figure A
below, the formula bar is highlighted in green. Note the value of 150 in the
selected cell and then note the contents of the formula bar.
|
Figure A |
 |
| The formula bar shows you the full contents of the cell—formula and all. The cell reflects the value of the calculated formula. |
Now you
know what the equal sign is for and where you can actually see the contents of
your cell, formula and all.
Cell references
The formula
presented in the last example is very simple and really not all that useful. You
might as well just type 150 into the cell. However, suppose you have other
cells in your spreadsheet and you want to calculate a sum based on the values
of those cells. As an example, let’s add up the contents of two cells. In this
case, cell A1 has a value of 100, and cell B1 has a value of 50. For a formula,
we’ll use =sum(A1:B1) as shown below in Figure B. Now,
look at Figure C to see what happens
when we change the value of cell A1 to 500.
|
Figure B |
 |
| This formula adds up all the numbers between and including A1 and B1. |
|
Figure C |
 |
| This example uses the exact same formula, but the source cell contents have changed. |
This is a
simple example, but a whole lot has happened here. You might not know it yet,
but we’ve added up a range of cell
values, and you’ve made changes to your source cells, forcing Excel to
automatically recalculate the formula based on the new values. You’ve also used
an Excel function to make it all
happen.
First, to
be clear, in these examples, I made no changes to the formula. It was always =sum(A1:B1). All I changed was the contents of
cell A1. Excel did the rest.
Now, about cell ranges. You can always tell when
Excel is using a range of cells rather than a single cell for a calculation. Whenever
this is taking place, the formula has a colon sign separating two cell
references. If you read this literally (A1:D1, for example), it reads “the
contents of every cell between A1 and B1, inclusive of A1 and B1”. This
would include cells A1, B1, C1 and D1.
If you want
to add up specific cells, but don’t want to use a range, separate the cell
references with commas instead. For example, the formula “=sum(A1,C1) will add up the contents of just
cells A1 and C1. In contrast, if you use a colon, i.e. “=sum(A1:C1)”, you will add up the contents of
cells A1, B1 and C1. See Figure D
for an example.
|
Figure D |
 |
| Note the difference in the results in this example, contrasting the difference between the comma and the colon cell reference separator. |
I also used
the term function earlier in this
section. The word “sum” in the formulas used in these examples is one
of Excel’s many functions. In this case, the sum function is used to add up the
values of the cells that you specify in the functions arguments—the values used by the function to make its calculation. The
cell references in the sum function, such as A1, B1 and A1:B1, are examples of
arguments.
One more
note before I move on to a few useful functions: As you type in your formula,
notice that Excel highlights the cells that you use in your formula. Each cell
reference gets a new color that corresponds to the cell reference in your
formula. If you use a cell range in your formula, the entire range is
highlighted with a single color. This highlighting feature makes it much easier
to quickly identify problems in your formula later on. Figures E and F show you example of this feature in action.
|
Figure E |
 |
| In this shot, cell A1 is highlighted in blue while C1 gets a green box. Look in cell C1 and notice that the cell reference matches the color of the box surrounding that particular cell. |
|
Figure F |
 |
| This formula uses a cell range and, thus, the range gets a single color that matches the one used in the formula. |
You can get access to the entire Excel series using this
link to the excel
anatomy tag
Useful Excel functions
Excel
includes dozens and dozens of functions, all of which can prove useful in
specific situations. For this section, I will go over three useful Excel
functions that are general purpose in nature, and show you a way that you can
quickly get a list of all of Excel’s
functions in an easy-to-use screen.
Sum
=sum(number or range, number or range,…)
The sum
function takes up to thirty numbers or ranges as arguments. The function’s
purpose is to add up the numbers provided and calculate the total.
Average
=average(number or range, number or range,…)
Like the
sum function, you can provide up to thirty arguments for the average function. As
you probably guessed, the average function calculates the average (mean) for
the numbers you provide. I list this function here for a reason. While you
could use a variation of the sum function to find an average, I wanted you to
know that Excel includes this useful function. The same is true for a number of
other functions.
Count
=count(number or range, number or range,…)
Again, up
to thirty arguments are accepted by this function. This function, however, only
counts the number of cells that actually contain values. For an example, see Figure G.
|
Figure G |
 |
| Counting |
In this
example, notice that the first count function resulted in a value of 4. That’s
because only four of the five cells in the range A1 to A5 contained values. The
second count function had a value of 7—four from the D1 to D5 range, and three
more as a result of the three extra numbers I put directly into the formula.
The count
function is extremely useful when you have a situation in which you need to
just count the number of cells that have data. For example, suppose you’re
creating a spreadsheet for an event you’re hosting and you want to keep track
of the number of people that have paid their deposits. If you enter their
deposits in Excel, you can use the sum function to add up the total amount of
the deposits, and use the count function on that same information to get the
number of deposits that have been made.
More functions
To get a
complete list of all of the functions included in Excel, go to the Insert menu and choose Function. The Insert Function dialog box
opens, as shown in Figure H.
|
Figure H |
 |
| The Insert Dialog box helps you locate the function you need |
The Insert
Dialog box has a window at the top of the screen that lets you try to tell
Excel what you want to do. Excel will in turn try to locate the function that
best suits your need. In this case, I told Excel that I wanted to find the
largest number in a range. Excel looked at its function database and told me
that the MAXA function was my best bet.
Alternatively,
if you don’t want to have Excel look for a function on your behalf, you can use
the Category drop down box to try to narrow down your function search and go
through the list of individual functions in each category on your own. Figure I shows the contents of the
Category drop down.
|
Figure I |
 |
| The Category box has a number of selections to make it easier for you to find the function you want |
Once you
locate the function you want, double-click it, or choose OK. The next screen of
the Insert Function process opens. This is where things get really useful. Basically,
using this screen, you don’t need to remember anything about Excel functions. Excel
pops up a form and tells you to fill it in and even tells you what kind of
information you need to enter. A sample of this screen is shown in Figure J, but shows the complex
function called getpivotdata. I only chose this function here to give you a look
at how Excel handles more complex functions on this screen. The sum, average,
and count functions are fairly simple and a shot of the count function’s
arguments in shown in Figure K.
|
Figure J |
 |
| Notice that Excel tells you, in white letters, what kind of data is needed for each argument |
|
Figure K |
 |
| The count functions needs only one parameter, but you can enter more. |
Also note
that the result is actually calculated right on this window. I’ve highlighted
it in green for you in this screenshot.
Summary
Excel
formulas and functions are what make the application work its magic. In future
parts of this training series, I’ll show you how to create more complex
functions, automate some processes and make your work look spectacular. After all, if you spend a lot of time creating a
budget spreadsheet for your boss, it might as well look good, too!
