Use Excel Functions

Functions enable you to make many useful calculations, such as finding a sum, an average, the highest number, the lowest number, or a count of the number of numbers in a list. Microsoft Excel has over 300 functions that you can use. You can use functions alone or you can include them in a larger formula

Reference Operators

Before you use functions, you should understand reference operators. Reference operators refer to a cell or a group of cells. There are three types of reference operators: range, union, and intersection.

A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon. The reference A1:A3 includes cells A1, A2, and A3. The reference C1:E3 includes cells C1, C2, C3, D1, D2, D3, E1, E2, and E3.

An Excel workbook can have multiple worksheets. You can reference other worksheets, by entering the name of the worksheet followed by an exclamation point, followed by the cell reference. If there are spaces in the worksheet name, enclose the sheet name is single quotes. Examples:

You can use the range reference operator to reference a range of worksheets. Enter the first sheet name followed by a colon, followed by the second sheet name , followed by an exclamation point, For example, the reference =Sheet2:Sheet4!A1:C3 refers to the values in cells A1 to A3 on sheet2, the values in cells A1 to A3 on sheet3, and the values in cells A1 to A3 on sheet4.

A union reference includes two or more references. A union reference consists of two or more numbers, range references, cell addresses, or functions separated by a comma. The reference A3,B4:B6,C5,10 refers to cells A3, B4 to B6, C5 and the number 10.

An intersection reference refers to all the cells the two range operators have in common. An intersection reference consists of two range operators separated by a space. The intersection operator A1:C3 C1:E3 refers to cells C1 to C3.

Functions

Functions are prewritten formulas. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. When using a function:

• If the function starts the formula, precede it with an equal sign
• Specify the function name.
• Enclose arguments within parentheses. Arguments are values on which Excel performs the calculation. For example, when using the SUM function, arguments specify the numbers or cells you want to add.
• Use a comma to separate arguments.

Here is an example of a function:

In this function:

• The equal sign begins the formula
• SUM is the name of the function
• 2, 13, A1, and B2:C7 are the arguments
• Parentheses enclose the arguments
• Commas separate the arguments

The example uses the SUM function. It adds the results of all of the arguments together and returns the result.

When entering a function, after you type the first letter of a function name, the AutoComplete list appears. You can double-click on an item in the AutoComplete list to complete your entry quickly. Excel will complete the function name and enter the first parenthesis.

How do I create a new worksheet?

Click the New Sheet button. It is located next to the last worksheet tab, to the left of the horizontal scroll bar.

Can I rename a worksheet?

Yes. Excel names the first sheet in a workbook Sheet1. As you add sheets, Excel names them sequentially, Sheet2, Sheet3, and so on. To change the name, right-click on the worksheet name. A menu appears. Click Rename. Type the name you want to give the worksheet.

Note

In a formula you cannot use a $ to denote currency, parentheses to denote a negative number, or commas to separate thousands.