Functions

By using functions, you can quickly and easily make many useful calculations, such as finding an average, the highest number, the lowest number, and a count of the number of items in a list. Microsoft Excel has many functions that you can use.


The SUM function adds argument values.

1. Open Microsoft Excel.
2. Type 12 in cell B1.
3. Press Enter.
4. Type 27 in cell B2.
5. Press Enter.
6. Type 24 in cell B3.
7. Press Enter.
8. Type =SUM(B1:B3) in cell A4.
9. Press Enter. The sum of cells B1 to B3, which is 63, appears.

Alternate Method: Enter a Function with the Ribbon

1. Type 150 in cell C1.
2. Press Enter.
3. Type 85 in cell C2.
4. Press Enter.
5. Type 65 in cell C3.
6. Choose the Formulas tab.
7. Click the Insert Function button. The Insert Function dialog box appears.
8. Choose Math & Trig in the Or Select A Category box.
9. Click Sum in the Select A Function box.
10. Click OK. The Function Arguments dialog box appears.

12. Type C1:C3 in the Number1 field, if it does not automatically appear.
13. Click OK. The sum of cells C1 to C3, which is 300, appears.

Format worksheet

1. Move to cell A4.
2. Type the word Sum.
3. Select cells B4 to C4.
4. Choose the Home tab.
5. Click the down arrow next to the Borders button .
6. Click Top and Double Bottom Border.

As you learned in Lesson 2, you can also calculate a sum by using the AutoSum button .

Calculate an Average

You can use the AVERAGE function to calculate the average of a series of numbers.

1. Move to cell A6.
2. Type Average. Press the right arrow key to move to cell B6.
3. Type =AVERAGE(B1:B3).
4. Press Enter. The average of cells B1 to B3, which is 21, appears.

Calculate an Average with the AutoSum Button

In Microsoft Excel, you can use the AutoSum button to calculate an average.

1. Move to cell C6.
2. Choose the Home tab.
3. Click the down arrow next to the AutoSum button .
4. Click Average.

5. Select cells C1 to C3.
6. Press Enter. The average of cells C1 to C3, which is 100, appears.

Find the Lowest Number

You can use the MIN function to find the lowest number in a series of numbers.

1. Move to cell A7.
2. Type Min.
3. Press the right arrow key to move to cell B7.
4. Type = MIN(B1:B3).
5. Press Enter. The lowest number in the series, which is 12, appears.

Note: You can also use the drop-down button next to the AutoSum button to calculate minimums, maximums, and counts.

Find the Highest Number

You can use the MAX function to find the highest number in a series of numbers.

.

1. Move to cell A8.
2. Type Max.
3. Press the right arrow key to move to cell B8.
4. Type = MAX(B1:B3).
5. Press Enter. The highest number in the series, which is 27, appears.

Count the Numbers in a Series of Numbers

You can use the count function to count the number of numbers in a series.

1. Move to cell A9.
2. Type Count.
3. Press the right arrow key to move to cell B9.
4. Choose the Home tab.
5. Click the down arrow next to the AutoSum button .
6. Click Count Numbers. Excel places the count function in cell C9 and takes a guess at which cells you want to count. The guess is incorrect, so you must select the proper cells.

7. Select B1 to B3.
8. Press Enter. The number of items in the series, which is 3, appears.

Fill Cells Automatically

You can use Microsoft Excel to fill cells automatically with a series. For example, you can have Excel automatically fill your worksheet with days of the week, months of the year, years, or other types of series.

The following demonstrates filling the days of the week:

1. Click the Sheet2 tab. Excel moves to Sheet2.
2. Move to cell A1.
3. Type Sun.
4. Move to cell B1.
5. Type Sunday.
6. Select cells A1 to B1.
7. Choose the Home tab.
8. Click the Bold button . Excel bolds cells A1 to B1.
9. Find the small black square in the lower-right corner of the selected area. The small black square is called the fill handle.
10. Grab the fill handle and drag with your mouse to fill cells A1 to B14. Note how the days of the week fill the cells in a series. Also, note that the Auto Fill Options button appears.

Copy Cells

1. Click the Auto Fill Options button. The Auto Fill Options menu appears.
2. Choose the Copy Cells radio button. The entry in cells A1 and B1 are copied to all the highlighted cells.
3. Click the Auto Fill Options button again.
4. Choose the Fill Series radio button. The cells fill as a series from Sunday to Saturday again.
5. Click the Auto Fill Options button again.
6. Choose the Fill Without Formatting radio button. The cells fill as a series from Sunday to Saturday, but the entries are not bolded.
7. Click the Auto Fill Options button again.
8. Choose the Fill Weekdays radio button. The cells fill as a series from Monday to Friday.

Adjust Column Width

Some of the entries in column B are too long to fit in the column. You can quickly adjust the column width to fit the longest entry.

1. Move your mouse pointer over the line that separates column B and C. The Width Indicator appears.

2. Double-click. The Column adjusts to fit the longest entry.

After you complete the remainder of the exercise, your worksheet will look like the one shown here.

Fill Times

The following demonstrates filling time:

1. Type 1:00 into cell C1.
2. Grab the fill handle and drag with your mouse to highlight cells C1 to C14. Note that each cell fills, using military time.
3. Press Esc and then click anywhere on the worksheet to remove the highlighting.

To change the format of the time:

1. Select cells C1 to C14.
2. Choose the Home tab.
3. Click the down arrow next to the number format box . A menu appears.
4. Click Time. Excel changes the format of the time.

Fill Numbers

You can also fill numbers.
Type a 1 in cell D1.

1. Grab the fill handle and drag with your mouse to highlight cells D1 to D14. The number 1 fills each cell.
2. Click the Auto Fill Options button.
3. Choose the Fill Series radio button. The cells fill as a series, starting with 1, 2, 3.