Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function rounds off a number in cell A10.
Structure of a function
Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
Function name. For a list of available functions, click a cell and press SHIFT+F3.
Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.
In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
Valid returns When a nested function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.
Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on.
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.
By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.
| To refer to | Use |
|---|---|
| The cell in column A and row 10 | A10 |
| The range of cells in column A and rows 10 through 20 | A10:A20 |
| The range of cells in row 15 and columns B through E | B15:E15 |
| All cells in row 5 | 5:5 |
| All cells in rows 5 through 10 | 5:10 |
| All cells in column H | H:H |
| All cells in columns H through J | H:J |
| The range of cells in columns A through E and rows 10 through 20 | A10:E20 |
Reference to another worksheet In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.
Link to another worksheet in the same workbook
Note that the name of the worksheet and an exclamation point (!) precede the range reference.
The difference between relative and absolute references
Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
Copied formula with relative reference
Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.
Copied formula with absolute reference
Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
Copied formula with mixed reference
If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.
- You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.
- 3-D references cannot be used in array formulas.
- 3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection.
How 3-D references change when you move, copy, insert, or delete worksheets
The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.
Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Microsoft Excel includes all values in cells A2 through A5 from the added sheets in the calculations.
Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation.
Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.
Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them.
Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them.
You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.
| Reference | Meaning |
|---|---|
| R[-2]C | A relative reference to the cell two rows up and in the same column |
| R[2]C[2] | A relative reference to the cell two rows down and two columns to the right |
| R2C2 | An absolute reference to the cell in the second row and in the second column |
| R[-1] | A relative reference to the entire row above the active cell |
| R | An absolute reference to the current row |
When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.
To turn R1C1 reference style on or off
-
Click Options on the Tools menu, and then click the General tab.
-
Under Settings, select or clear the R1C1 reference style check box.
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name.
Using defined names to represent cells, constants, or formulas
A defined name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).
Names are available to any sheet. For example, if the name ProjectedSales refers to the range A20:A30 on the first worksheet in a workbook, you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.
Names can also be used to represent formulas or values that do not change (constants). For example, you can use the name SalesTax to represent the sales tax amount (such as 6.2 percent) applied to sales transactions.
You can also link to a defined name in another workbook, or define a name that refers to cells in another workbook. For example, the formula =SUM(Sales.xls!ProjectedSales) refers to the named range ProjectedSales in the workbook named Sales.
Note By default, names use absolute cell references.
Guidelines for names
What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators
How many characters can be used? A name can contain up to 255 characters.
Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.
Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
Using existing row and column labels as names
When you create a formula that refers to data in a worksheet, you can use the column and row labels in the worksheet to refer to the data. For example, to calculate the total value for the Product column, use the formula =SUM(Product).
Using a label
Or if you need to refer to the Product 3 amount for the East division (that is, the value 110.00), you can use the formula =Product 3 East. The space in the formula between "Product 3" and "East" is the intersection operator. This operator designates that Microsoft Excel should find and return the value in the cell at the intersection of the row labeled East and the column labeled Product 3.
Note By default, Excel does not recognize labels in formulas. To use labels in formulas, click Options on the Tools menu, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.
Stacked labels When you use labels for the columns and rows on your worksheet, you can use those labels to create formulas that refer to data on the worksheet. If your worksheet contains stacked column labels
The order for stacked labels When you refer to information by using stacked labels, you refer to the information in the order in which the labels appear, from top to bottom. If the label West is in cell F5 and the label Actual is in cell F6, you can refer to the actual figures for West by using West Actual in a formula. For example, to calculate the average of the actual figures for West, use the formula =AVERAGE(West Actual).
Using dates as labels When you label a range by using the Label Ranges dialog box and the range contains a year or date as a label, Excel defines the date as a label by placing single quotation marks around the label when you type the label in a formula. For example, suppose your worksheet contains the labels 2007 and 2008 and you have specified these labels by using the Label Ranges dialog box. When you type the formula =SUM(2008), Excel automatically updates the formula to =SUM('2008').
About calculation operators in formulas
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.
Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.
| Arithmetic operator | Meaning (Example) |
|---|---|
| + (plus sign) | Addition (3+3) |
| – (minus sign) | Subtraction (3–1) Negation (–1) |
| * (asterisk) | Multiplication (3*3) |
| / (forward slash) | Division (3/3) |
| % (percent sign) | Percent (20%) |
| ^ (caret) | Exponentiation (3^2) |
Comparison operators You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.
| Comparison operator | Meaning (Example) |
|---|---|
| = (equal sign) | Equal to (A1=B1) |
| > (greater than sign) | Greater than (A1>B1) |
| < (less than sign) | Less than (A1<B1) |
| >= (greater than or equal to sign) | Greater than or equal to (A1>=B1) |
| <= (less than or equal to sign) | Less than or equal to (A1<=B1) |
| <> (not equal to sign) | Not equal to (A1<>B1) |
Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
| Text operator | Meaning (Example) |
|---|---|
| & (ampersand) | Connects, or concatenates, two values to produce one continuous text value ("North"&"wind") |
Reference operators Combine ranges of cells for calculations with the following operators.
| Reference operator | Meaning (Example) |
|---|---|
| : (colon) | Range operator, which produces one reference to all the cells between two references, including the two references (B5:B15) |
| , (comma) | Union operator, which combines multiple references into one reference (SUM(B5:B15,D5:D15)) |
| (space) | Intersection operator, which produces on reference to cells common to the two references (B7:D7 C6:C8) |
The order in which Excel performs operations in formulas
Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
Operator precedence
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence
| Operator | Description |
|---|---|
| : (colon)
(single space) , (comma) |
Reference operators |
| – | Negation (as in –1) |
| % | Percent |
| ^ | Exponentiation |
| * and / | Multiplication and division |
| + and – | Addition and subtraction |
| & | Connects two strings of text (concatenation) |
| = < > <= >= <> | Comparison |
Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)
A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. An expression, or a value resulting from an expression, is not a constant. If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.