Develop more powerful spreadsheets in Excel by getting to grips with functions and formulae
F
ormulas lie at the heart of everything you do in Microsoft Excel. They turn a blank worksheet into a powerful document capable of calculating just about any figure you need or want.
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].
By default, cell references are relative - to find out what this means, and how to make them absolute, read on.
Step-by-step:
Name cells and formulas for easy reference
1. DEFINE USING SELECTED CELLS
Select the cell or group of cells you wish to name. Now type a suitable name into the Name box (to the left of the fx box) - cells can be named with a mixture of letters and numbers, plus the backslash (\) and underscore (_) characters, but must not begin with a number. Once entered, the name will be assigned an absolute cell reference that includes the sheet name - for example, Sheet1!$A$1.
2. DEFINE USING EXISTING LABELS
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 dialogue.
3. CREATE NAMES FROM SCRATCH
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.
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.
Step-by-step:
Get more from your functions and formulae with our top tips
1. SELECT MULTIPLE CELLS
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.
2. SELECT CELLS FROM BOX
Select cells to include in a dialogue box like Define Name or Conditional Sum by clicking the button next to the Refers to: box to collapse the box temporarily.
3. SWITCH REFERENCES
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.
4. COPY FORMULA RESULTS ONLY
To copy the results of a formula into a new cell instead of the actual formula, copy the formula, then choose Edit > Paste Special > Values and click OK.
5. CREATE REFERENCE TO ANOTHER SHEET
Select the cell where the formula is go to, type = without pressing any other key. Switch to the other sheet, select the cell in question and press [Enter].
6. TEMPORARY FORMULAE
Common formulae appear in the status bar when you select a number of cells for a quick glance - right-click the Sum= figure to change which ones are displayed.
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.
If you want to go further and apply up to seven different conditions to your calculation install the Conditional Sum Wizard Add-In from the Tools > Add-Ins menu (Excel 2007 users should click the Office button and choose Excel Options > Add-Ins). Once installed, open the Tools menu and select either Wizards > Conditional Sum or Conditional Sum in Excel 2003 or earlier, or switch to the Add-ins ribbon in Excel 2007.
