Back to previous pageReturn to Tutorials index



Use formulae and functions in Microsoft Excel
Free backup is finally here.  Mozy Remote Backup.
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


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


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






All content copyright Support PCs and the original authors