Using Functions

Excel has over 200 built-in formulas called functions divided into groups such as Most Recently Used, Financial, Date & Time, Math & Trig, Statistical, Lookup & Reference, Database, Text, Logical, and Information. A function can be used alone or with other formulas or functions.

The Structure of Functions

Start functions using the equal sign (=) followed by the function name and its arguments enclosed in parentheses. For example:

Functions image

Entering Functions

Functions can be entered in several ways:

Type the entire function directly into the cell.
Type the function name and the left parentheses; use the mouse to select the range(s) as the argument(s), separating multiple arguments with commas, and type the right parentheses. Note: If you omit the closing parentheses when you enter a function needing only one pair of parentheses, Excel automatically enters the closing parentheses.
Use the Insert Function button located on the Standard toolbar.

Practice: Entering the SUM function using the mouse to specify a range

Select cell C13 and type =sum(
Using the mouse, select the range C7:C11. The range has a flashing marquee around it. As you select the range, Excel indicates how many rows and columns you have selected (see example below).

Functions image

Type ) to complete the function and press Enter.
Select cell C15. Type =average(
Using the mouse, select the range C7:C11 but do not type the end parentheses.
In the formula bar, click on the Enter button, the green check mark beside the Insert Function button.
Observe the formula bar showing =AVERAGE(C7:C11)—Excel adds the closing parentheses.
Select cell C17. Type =max(
Select the range C7:C11 and enter.
Using Autofill, enter formulas for Total, Average, and High for November and December. Autofill is dragging the fill handle, the black square in the lower right corner of the active cell. You can do this when the cursor changes from the white cross to a black crosshair shown below.

Simply drag the fill handle in the direction you want to fill cells with the current formula so that you get the data shown.

Functions image

Save the file.

The AutoSum Button

You can use the AutoSum button

on the Standard toolbar to sum a range of values. When you click on the AutoSum button, the Sum function and a suggested range to be added are displayed and highlighted in the formula bar. You can change this range if it is not correct.

To assure the correct range is selected, an alternate method for using the AutoSum button is to select a range that includes the cells that need to be added and a destination cell for the result, then to click on the AutoSum button. This creates and enters the correct Sum function.

Practice: Using the AutoSum button

Select cell F7.
Click on the AutoSum button .
Observe the formula. Excel correctly assumes that you want to add cells C7:E7.

Autosum image

Press the Enter key or click on the green check by the formula bar. The result is 425.
Select the range C8:F11 and click on the AutoSum button.

Autosum image

The Formula List

Practice: Using the Formula List

Select cell F13.
Enter the equal sign (=). Once you enter the equal sign, you can display the formula list (see below) to the left of the Formula bar.

Autosum image

Select Sum. Excel displays the formula box and correctly assumes that you want to add cells C13:E13.

Autosum image

Click on OK. The formula is entered into cell F13, which displays the value 2733.

The Insert Function Button

The Insert Function button enables you to select a function from the Insert Function dialog box, which lists each function by category. When you select a category and function from the list and click on OK, Excel suggests a range and provides additional information about what the function does and the arguments it requires.

Practice: Using the Insert Function Button

Select cell F15.
Click on the Insert Function button . The Insert Function dialog box opens.
From the Select a Category list, select All.

Insert function image

From the Function Name list, select Average. Click on OK.

Insert function image

Click on the Collapse button to temporarily hide the dialog box.
Select the range F7:F11.

Insert function image

Click on the Expand button to display the dialog box.
Observe the formula dialog box. The range you selected is displayed in the Number 1 box.

Insert function image

Click on OK and the value 546.6 is displayed.
Using the method of your choice, enter the Max Function to find the highest number within the range F7:F11. The result is 775.
Save the file.
Go on to the next topic on Order of Operations.

Click here to return to Course Topics.

The Structure of Functions Entering Functions The AutoSum Button The Formula List The Insert Function Button		Search:

	E-mail Internet Access Software Assistance Computer Labs Software Licensing Web Publishing Calendaring Computer Security Unix Training

	Using Functions Excel has over 200 built-in formulas called functions divided into groups such as Most Recently Used, Financial, Date & Time, Math & Trig, Statistical, Lookup & Reference, Database, Text, Logical, and Information. A function can be used alone or with other formulas or functions. The Structure of Functions Start functions using the equal sign (=) followed by the function name and its arguments enclosed in parentheses. For example: Entering Functions Functions can be entered in several ways: Type the entire function directly into the cell. Type the function name and the left parentheses; use the mouse to select the range(s) as the argument(s), separating multiple arguments with commas, and type the right parentheses. Note: If you omit the closing parentheses when you enter a function needing only one pair of parentheses, Excel automatically enters the closing parentheses. Use the Insert Function button located on the Standard toolbar. Practice: Entering the SUM function using the mouse to specify a range Select cell C13 and type =sum( Using the mouse, select the range C7:C11. The range has a flashing marquee around it. As you select the range, Excel indicates how many rows and columns you have selected (see example below). Type ) to complete the function and press Enter. Select cell C15. Type =average( Using the mouse, select the range C7:C11 but do not type the end parentheses. In the formula bar, click on the Enter button, the green check mark beside the Insert Function button. Observe the formula bar showing =AVERAGE(C7:C11)—Excel adds the closing parentheses. Select cell C17. Type =max( Select the range C7:C11 and enter. Using Autofill, enter formulas for Total, Average, and High for November and December. Autofill is dragging the fill handle, the black square in the lower right corner of the active cell. You can do this when the cursor changes from the white cross to a black crosshair shown below. Simply drag the fill handle in the direction you want to fill cells with the current formula so that you get the data shown. Save the file. The AutoSum Button You can use the AutoSum button on the Standard toolbar to sum a range of values. When you click on the AutoSum button, the Sum function and a suggested range to be added are displayed and highlighted in the formula bar. You can change this range if it is not correct. To assure the correct range is selected, an alternate method for using the AutoSum button is to select a range that includes the cells that need to be added and a destination cell for the result, then to click on the AutoSum button. This creates and enters the correct Sum function. Practice: Using the AutoSum button Select cell F7. Click on the AutoSum button . Observe the formula. Excel correctly assumes that you want to add cells C7:E7. Press the Enter key or click on the green check by the formula bar. The result is 425. Select the range C8:F11 and click on the AutoSum button. The Formula List Practice: Using the Formula List Select cell F13. Enter the equal sign (=). Once you enter the equal sign, you can display the formula list (see below) to the left of the Formula bar. Select Sum. Excel displays the formula box and correctly assumes that you want to add cells C13:E13. Click on OK. The formula is entered into cell F13, which displays the value 2733. The Insert Function Button The Insert Function button enables you to select a function from the Insert Function dialog box, which lists each function by category. When you select a category and function from the list and click on OK, Excel suggests a range and provides additional information about what the function does and the arguments it requires. Practice: Using the Insert Function Button Select cell F15. Click on the Insert Function button . The Insert Function dialog box opens. From the Select a Category list, select All. From the Function Name list, select Average. Click on OK. Click on the Collapse button to temporarily hide the dialog box. Select the range F7:F11. Click on the Expand button to display the dialog box. Observe the formula dialog box. The range you selected is displayed in the Number 1 box. Click on OK and the value 546.6 is displayed. Using the method of your choice, enter the Max Function to find the highest number within the range F7:F11. The result is 775. Save the file. Go on to the next topic on Order of Operations. Click here to return to Course Topics.
Copyright © 1998-2004 The University of Alabama \| Disclaimer \| Contact: Help.Desk@ua.edu