Resources

On our resources page you can find links to our current Excel blog, Excel survey, free training manuals, hints & tips and much more.

Below is an extract from our Excel training course material that we supply as a printed handout on all our public courses.

Excel 2010/2013 Introduction

Unit 4: Using Functions

In this section you will learn how to:

Identify common functions

Insert a function into a formula

Use the SUM, AVERAGE, MAX, MIN and COUNT functions

What are Functions?

In Excel, a function can be described as a built in tool for performing mathematical or logical tests. Quite often, you may need to perform operations in your worksheets that involve many cells, like totaling a lengthy column of numbers or averaging a large group of data. Excel's functions can help you with these tasks.

Excel 2010 has a wide range of functions. For example, you can use functions to find totals, averages, counts, minimum (smallest) and maximum (highest) amounts.

SUM - Calculates the sum for a range of cells.

AVERAGE - Returns the average for a range of numbers.

MAX - Returns the largest number from a range of numbers.

MIN - Finds the minimum number in a range of numbers.

COUNT - Will count the number of cells in a list that contain numbers.

Using AutoSum

It is often useful to have totals or sums for the rows and columns of numbers in your worksheet. AutoSum can easily add all of the numerical data in a column or row.

Select the cell immediately below the column of data (or immediately beside the row of data)

Click the Formulas tab

Click AutoSum button in the Function Library group.

(The AutoSum button can also be found on the 'Home' ribbon in the 'Editing' group).



(The AutoSum button can also be found on the 'Home' ribbon in the 'Editing' group). The column or row of data to be summed will now be enhanced by an animated border.

Notice that you can see the range to be summed in the active cell.

Notice that you can see the range to be summed in the active cell. Press Enter and the total will be displayed in the cell.

Try it: On Sheet 1 of Sales report.xlsx use the AutoSum button to calculate Total actual and target sales for each quarter in row 11.

The Function Library

Excel 2010 contains an extensive library of functions that you can call upon to help you solve problems. These tools are available in the Function Library button group, on the Formulas ribbon.

The first and largest button is Insert Function. This button will open a dialogue box allowing you to search for and insert hundreds of functions.

You can also click the small fx button next to the formula bar to display the Insert Function box.

Clicking the Insert Function button activates the Insert Function dialogue box and provides access to the large range of functions available in Excel.

Once the Insert Function dialogue box is open:

Select the function you wish to use from the available list and click OK or

Type the name of the function you wish to use in the Search for a function area, press Enter, select the function when it appears in the list and click OK.

In the Function Arguments dialogue box, click the top button with the red arrow, then select the range of cells to enter into the formula from the spreadsheet, click OK.

Try it: On Sheet 1 of Sales report.xlsx type Average into cell A12. Use the Insert Function button to calculate average actual and target sales for each quarter in row 12.

Using the AutoSum dropdown list

The AutoSum button dropdown arrow provides access to common functions, and the Insert Function dialogue box.

To insert a function using the AutoSum list:

Select the cell where the formula is to appear in the spreadsheet

Select the function you wish to use from the AutoSum list

Check the formula to ensure it is accurate - edit the formula if necessary

Press Enter to confirm the formula and display the formula result.

Try it: On Sheet 1 of Sales report.xlsx type Minimum into cell A13. Use the AutoSum list to calculate minimum actual and target sales for each quarter in row 13.

Entering functions manually

All the common functions follow the same basic structure:

=function_name(cell:cell) or =function_name(cell,cell,cell)

Therefore, formulas containing functions can be typed directly into a cell in the spreadsheet. After pressing the = (equals button) start typing a function name. Immediately after entering the first character of the name, Excel provides you with a list of function names beginning with that letter. Locate the function name you require and double click on it. Excel will then insert the name together with the opening bracket. All you then have to do is to complete the function requirements and press enter. The closing bracket is automatically inserted for you and the result of the formula is entered into the selected cell.

Try it: On Sheet 1 of Sales report.xlsx type Maximum into cell A14. Enter formulas to calculate maximum actual and target sales for each quarter into row 14.

Type No. of employees into cell A15.

Use any one of the three methods for inserting functions to enter the COUNT function into cell B15. The COUNT function can be used on cells that contain values, to determine how many values there are. In this case, we want to know how many employees there are.

Using AutoCalc

The AutoCalc feature in the status bar at the bottom-right of the Excel window can be used to find the result of using particular functions on cells that have been selected in the spreadsheet.

To use the AutoCalc feature: