Looking to start developing your own spreadsheets? Nick and
Toni Peers outline everything you need to know to get started with formulas and
functions
Formulas lie at the heart of everything you do in Excel.
They turn a blank worksheet into a powerful document capable of calculating just
about any figure you need or want. In this tutorial, we're going to look at how
you can start using them in your own spreadsheets for maximum effect.
Anatomy of a formula
A formula can contain anything from a basic calculation such as 2+2 to
encompass the contents of specific cells. While a formula is pretty simple,
Excel also boasts a large number of functions - predefined formulas that
let you tap into Excel's power without leaving you confused.
All formulas are preceded by an equals sign (=). Once you type = into a
cell, Excel understands that you'll be entering a formula. If you're
performing multiple mathematical operations within a formula - for example,
adding two figures and then multiplying a third - the multiplication and
division are performed before any addition or subtraction. So, if you type
=1+2*5 expecting the result to be 15, you'll be disappointed; Excel will
multiply 5 by 2 before adding 1.
You can fix this problem by using brackets to indicate that you want the
calculation within those brackets performed before anything else, so to get 15
you'd merely type =(1+2)*5.
Cell references
You can replace numbers in your formulas with cell references like A1, B2
and so on. Type =A1+A2 into cell A3 and it will add up
whichever numbers you've entered into cells A1 and A2.
You can use the mouse if you so prefer: type = into the cell you
wish the result to appear in, then click the first cell you want to add,
press the plus, minus, multiply or divide key, click the next cell and
carry on until you're done, pressing [Enter] to finish. If your cells are
scattered all over your workbook, you can scroll away from the active cell
- it will remain selected until you press the appropriate mathematical
key.
You can also reference cells in other worksheets in
your workbook too: simply prefix the cell reference with Sheet2! or
whatever the sheet name is. For example, to add up cells A1 from Sheet1
and Sheet2 in cell A2 of Sheet1, type =A1+Sheet2!A1, or follow the
instructions above, switching sheets and selecting the cell you wish from
your second sheet before returning to the original sheet and pressing [Enter]. |
|
Absolute versus relative
By default, all formulas in Excel are relative. That means they refer
to the cells around them, and if you move or copy the formula to another cell,
it'll change to reflect the cells around its new position. For example, you
create the following formula in cell A5: =SUM(A1:A4). If you were to
copy cell A5 and paste it into cell B5, its formula would change to
=SUM(B1:B4). Move it to cell C8, and it changes to =SUM(C4:C7).
In most cases, this works perfectly. When you copy a formula from
one cell to another you'll usually want to its references to reflect where the
formula is now placed. But what happens when you want the formula to be
absolute? You may, for example, wish to move or copy the formula to another
cell because it's easier to read elsewhere on your sheet. The simple answer to
this conundrum is to make the formula's references absolute.
This is done simply by placing a dollar ($) sign in front of the row and
column reference, so =SUM($A$1:$A$4) would always provide the totals of
cells A1, A2, A3 and A4 regardless of where you moved or copied the formula
around your spreadsheet.
It's possible to provide mixed references - $A1 for example
would always refer to column A, but the row number would change depending on
where the formula was pasted to; similarly, A$5 would always refer to
row 5, even though the column would change.
| Name cells and formulae for easy reference |
|
1 Name a
cell from scratch
Define a name for a single cell or group of cells by first
selecting the cells. Now type a suitable name into the Name
box (to the left of the fx box). The name will be assigned an
absolute cell reference that includes the sheet name - for
example, Sheet1!$A$1. |
2 Use text
labels to define names
You can also define names using existing text labels. Select
Insert > Name > Define and you can use
text from adjacent cells to define cell and range names.
Should you wish to edit or remove existing names, do so from
the same dialog. |
|
3 Create
names for formulas
You can even create names from scratch - perfect for creating
custom formulas with relative or absolute references. Just
type the formula into the Refers to: box, give it a suitable
name and click OK. To use the formula, type =name
into the cell in question, replacing "name" with your
formula's name. |
EXPERT TIPS
Excel features hundreds of functions - for a complete list and starter's
guide in Excel format, click
here.
For more tips on working with functions and formulas, click
here.
Select a formula, place the cursor next to the reference
you wish to change and press [F4] up to four times to change it to
a relative, absolute or mixed reference.
To create a reference to another sheet in your workbook, select the cell
where the formula is go to and type =. Switch to the other sheet,
select the cell in question and press [Enter]. |
|
Functions
The formulas we've used so far are great for basic
mathematical requirements, but you'll find Excel is
bursting at the seams with advanced mathematical functions too. A
function is basically a predefined formula, designed to simply
certain tasks like adding up consecutive cells in a row or column
(the =SUM function) or returning an average of the selected cells
(=AVERAGE).
To get started with functions, click the fx button next to
the box you enter formulas for. The Insert Function box will
appear, with a list of common functions you can pick from. Select
one and you'll see its syntax appear at the bottom of the screen -
to find out more about individual functions, select one and click
Help on this function.
The power of SUM
One of the most commonly used functions is =SUM. Its default syntax is
=SUM(number1,number2...). Type =SUM(A1, A3) for example and it'll
add cells A1 and A3 together, which is no different to typing =A1+A3.
Where =SUM comes into its own, however, is when you want to add up
a large group of consecutive rows or columns together: type
=SUM(A1:A10) for example, and it'll add up the contents of cells A1,
A2, A3 all the way to A10.
The SUMIF command enables you to apply a single condition to your
calculation. Type =SUMIF(A1:A5, ">100") for example, and any cells
that contain numbers of 100 or less are automatically skipped by the SUMIF
command. |
|
SUMIF is useful for simple conditions, but if you
want to go further and apply up to seven different conditions to your
calculation install the Conditional Sum Wizard Add-In (Tools >
Add-Ins). After installation, select Tools > Conditional Sum
(Tools > Wizards > Conditional Sum in Excel 97
and 2000) to launch it.
| Get more from your formulas |
|
1 Copy and
paste formula result
It's possible to copy a formula from one cell and then paste
its result from that cell into a new cell as opposed to the
formula itself. To do this, copy the formula in the usual way.
To paste it into a new cell, choose Edit > Paste
Special, select Values and click OK. |
2 Select
cells within dialog boxes
When you open a dialog box like the Define Name or Conditional
Sum boxes, you can collapse the box momentarily to enable you
to select cells from your worksheet to include. You'll find
the button you need next to the box containing the currently
selected cells. |
|
3 Quickly go
to named range
Once you've set up a few named ranges following the
instructions in the other walkthrough, you can quickly select
a range name's cells one of two ways: either choose Edit
> Go To and select it from the list, or click the down
arrow next to the Name box and select from there. |
EXPERT TIPS
Cells can be named with a mixture of letters and numbers, plus the
backslash (\) and underscore (_) characters. You may not begin a name with
a number
Select consecutive cells by clicking on the first cell and holding [Shift]
as you click on the last; select non-consecutive cells by holding [Ctrl]
as you click each cell
You can quickly apply a formula to your selected cells without making it
permanent: select the cells, then check the Sum= figure at the
bottom of the status bar. Right-click to choose from other common
functions |
| |
