How to start using Excel 2007 Functions

You may have used calculations in Excel 2007 for example to add two cell values by typing something like =D4+e4 into a cell, or to multiply two cells by typing this formula =D5*E5. You can build calculations like this using an "=" followed by a mixture of cell references and the symbols +, -, *, and / to add, subtract, multiply and divide values in different cells, with the answer showing in the cell containing the formula.

Excel takes this a step further by using preset calculations called functions built into the software. You may have already used the SUM function by clicking on the AutoSum button or you may have typed =SUM(D4:D10) into a cell below some numbers. Excel knows from the "=" that you are starting a calculation, and from "SUM" that you want to use the inbuilt SUM function to add cells, and the brackets tells Excel which range of cells you want to add - in this case, the values in cells D4 to D10 inclusive.

There are a total of 347 functions built into Excel 2007. Everyday examples of functions include AVERAGE (to find the average value of a range of cells), MAX (to find the highest value of a range of cells) and MIN (to find the lowest value of a range of cells). Other functions are for more specialised use, for example for use in financial calculations.

You can add a function in Excel 2007 in two ways, either by using the Insert Function fx button, or by typing in the function directly into a cell. We'll look at Insert Function first.

Suppose you have several cells in a column all containing numbers. Select the cell immediately below the numbers. To add a function, click on the Insert Function fx button to the immediate left of the edit bar (above the cells). The Insert Function box appears. Lets suppose you want to add the AVERAGE function.

You can find particular functions in different ways.. For example you can type one or more words in "Search for a function" and then click the Go button. Try typing "Average" and then click Go. Excel 2007 shows a recommended list of functions all to do with averaging. Select Average, then click OK.

Alternatively, to the right of "Or select a category" click the pop down and choose "Most Recently Used". This shows the regular everyday functions, which includes Average. To use this function, make sure Average is selected, then click OK.

The Function Arguments box then appears to help you complete the job. The box also has a sentence or two about of what the function does - always handy if you're not exactly sure what it does. If the Function Arguments box blocks the cells in the worksheet, move it by dragging the blue bar at its top.

Next you tell Excel which cells you want to apply the function to - in our case which cells to average. You can either select them directly (left mouse button press and drag through the cells) if you can see the cells, or click on the red collapse button to the right of the white "Number 1" box to minimise the box, select the cells you want to average, and click again on the same red collapse button to restore the Functions Argument box. You should now see the range of cells you selected, in the "Number 1" box. You can also see a preview of the answer in the bottom left, just where it says "Formula result = ". To finish click OK and you are taken back to the worksheet.

The selected cell now shows the average value of all the cells you specified and the function =AVERAGE(D4:D8) appears in the cell's edit bar above, but with the selected range of cells in the formula.

If you can't see the function you want in the Function Arguments box, for example MIN, change the category selection to All. Excel now lists all 347 functions in alphabetical order. Try typing MIN reasonably quickly, and Excel matches what you type to the required function, or just type "M" or "MI" and then scroll down the list to find "MIN". Once you find the desired function, select it, and click OK to proceed as before.

One interesting point is that the "Most Recently Used" category changes as you use functions. So if you have just used MIN, it now appears in this category even though it wasn't there before.

Excel 2007 functions are also stored under 11 different subject categories as well as the All category. So if your are looking for financial functions, choose the Financial category. To show these 11 categories click the "or select a category" pop down. The full list shown is : Financial, Date & Time, Math & Trig, Statistical, Lookup and Reference, Database, Text, Logical, Information, Engineering and Cube.

You can also use the fx button to edit or find out more about any function already in a cell. To do this, first select the cell containing a function, then click the fx button. The Insert Function box appears displaying details for the function already entered - this can be very useful if you are trying to amend an existing function or just work out what it does in a worksheet.

The other way to add an Excel 2007 function assumes you know how it's spelt, or at least how the first few characters are spelt. Excel has a helpful autocomplete facility to help you as you type the function name.

To show this, select an empty cell under some numbers. In this cell type "=" followed by "A". An autocomplete pop down appears listing all the functions beginning with "A". Then type "V" and the list shortens to functions beginning with "AV" and so on. On the autocomplete pop down double click the function you want, drag the cells you want in the range, and press Enter to complete.

Interested in finding out more about Excel 2007 functions? I'd suggest you find a good Excel 2007 training course with Functions on the agenda and take it from there.