Create different types of formulas

Note: This article has done its job, and will be retiring soon. To prevent "Page not found" woes, we're removing links we know about. If you've created links to this page, please remove them, and together we'll keep the web connected.

Excel supports many types of simple and complex formula to help you achieve the calculation result that you want. Depending on the type of formula that you create, a formula might contain functions, cell reference, constant, and operator. You can use a single function, nested functions, or array that calculate single or multiple results.

Do any of the following:

Create a simple formula by using constants and calculation operators

1. Click the cell in which you want to enter the formula.

2. Type = (equal sign).

3. To enter the formula, do one of the following:

   • Type the constants and operators that you want to use in the calculation.

Formula	Description
=5+2	Adds 5 and 2
=5-2	Subtracts 2 from 5
=5/2	Divides 5 by 2
=5*2	Multiplies 5 times 2
=5^2	Raises 5 to the 2nd power

• Click the cell that contains the value that you want to use in the formula, type the operator that you want to use, and then click another cell that contains a value.

Formula	Descr iption
=A1+A2	Adds the values in cells A1 and A2
=A1-A2	Subtracts the value in cell A2 from the value in A1
=A1/A2	Divides the value in cell A1 by the value in A2
=A1*A2	Multiplies the value in cell A1 times the value in A2
=A1^A2	Raises the value in cell A1 to the exponential value specified in A2

1. Tip: You can enter as many constants and operators in a formula as you need, up to 8192 characters, to achieve the calculation result that you want.

2. Press RETURN.

Create a formula by using cell references and names

The example formulas at the end of this section contain relative references of other cells, or relative cell reference. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, cell B2 is a dependent cell if it contains the formula =C2.

1. Click the cell in which you want to enter the formula.

2. In the formula bar, type = (equal sign).

3. Do one of the following:

   • To create a reference, select a cell, a range of cells, a location in another sheet, or a location in another workbook. You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.

   • To enter the reference to a name range, on the Insert menu, point to Name, and then click Paste. In the Paste Name box, select the name that you want and click OK.

Formula	Description
=C2	Uses the value in the cell C2
=Sheet2!B2	Uses the value in cell B2 on Sheet2
=Asset-Liability	Subtracts the value in a cell named Liability from the value in a cell named Asset

1. Press RETURN.

Create a formula by using a function

1. Click the cell in which you want to enter the formula.

2. To start the formula with the function, on the Formulas tab, under Function, click Insert, and then select the function that you want to use.

   Note: If you're not sure which function to use, on the Formulas tab, under Function, click Reference to browse the available formulas.

3. Enter the argument.

   Tip: To enter cell references as an argument, select the cells on the sheet.

Formula	Description
=SUM(A:A)	Adds all numbers in column A
=AVERAGE(A1:B4)	Averages all numbers in the range

1. After you complete the formula, press RETURN.

Create a formula by using nested functions

Nested function use a function as one of the arguments of another function. You can nest up to 64 levels of functions. The following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50. Otherwise, it returns 0.

1. Click the cell in which you want to enter the formula.

2. To start the formula with the function, on the Formulas tab, under Function, click Insert, and then select the function that you want to use.

   Note: If you're not sure which function to use, on theFormulas tab, under Function, click Reference to browse the available formulas.

3. To enter the argument, do one or more of the following:

   • To enter cell references as an argument, select the cells on the sheet.

   • To enter another function as an argument, enter the function in the argument box that you want. For example, you can add SUM(G2:G5) in the Value_if_true edit box of the IF function

   • The parts of the formula displayed under Arguments reflect the function that you selected in the previous step. For example, if you clicked IF, the Arguments section displays the arguments for the IF function.

4. After you complete the formula, press RETURN.

Create an array formula that calculates a single result

You can use an array formula to perform several calculations to generate a single result. Array formulas use standard formula syntax. They all begin with an equal sign (=), and you can use any of the built-in Excel functions in your array formulas. This type of array formula can simplify a sheet model by replacing several different formulas with a single array formula.

1. Click the cell in which you want to enter the formula.

2. Enter the formula that you want to use.

   For example, the following formula calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the total values for each stock.

   When you enter the formula {=SUM(B2:C2*B3:C3)} as an array formula, Excel multiples the number of shares by the price for each stock (500*10 and 300*15), and then adds the results of those calculations together to get a total value of 9500.

3. Press + RETURN to enter the formula as an array formula.

   Excel automatically inserts the formula between { } (a pair of opening and closing braces).

   Notes: 

   • Manually typing braces around a formula will not convert it into an array formula — you must press + RETURN to create an array formula.

   • Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you must press + RETURN again to incorporate the changes into an array formula and to add the braces.

Create an array formula that calculates multiple results

Some sheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results by using an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments have.

1. Select the range of cells in which you want to enter the array formula.

2. Enter the formula that you want to use.

   For example, given a series of three sales figures (column B) for a series of three months (column A), the TREND function determines the straight-line values for the sales figures. To display all the results of the formula, it is entered into three cells in column C (C1:C3).

   When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962), based on the three sales figures and the three months.

3. Press + RETURN to enter the formula as an array formula.

   Excel automatically inserts the formula between { } (a pair of opening and closing braces).

   Notes: 

   • Manually typing braces around a formula will not convert it into an array formula — you must press + RETURN to create an array formula.

   • Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you must press + RETURN again to incorporate the changes into an array formula and to add the braces.

Learn tips and tricks about creating formulas

Easily change the type of reference

To switch between relative, absolute, and mixed references:

1. Select the cell that contains the formula.

2. In the formula bar, select the reference that you want to change.

3. On the Formulas tab, under Function, click Switch Reference.

Use Formula AutoComplete

To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula AutoComplete. After you type an equal sign (=) and beginning letters (the beginning letters act as a display trigger), Excel displays a dynamic list of valid functions and names below the cell. After you insert the function or name into the formula by using an insert trigger (pressing TAB or double-clicking the item in the list), Excel displays any appropriate arguments. As you fill out the formula, typing a comma (,) can also act as a display trigger—Excel may display additional arguments. You can insert additional functions or names into your formula and, as you type their beginning letters, Excel again displays a dynamic list from which you can choose.

Quickly copy formulas

You can quickly enter the same formula into a range of cells. Select the range where you want the calculated results to appear, copy the formula into the formula bar, and then press CONTROL + RETURN . For example, if you type =SUM(A1:B1) in range C1:C15, and then press CONTROL + RETURN , Excel updates the formula copied in each cell of the range, using A1 as a relative reference.

Use Function and argument inline help

If you are not familiar with the arguments of a function, you can use the function Help that appears after you type an equal sign (=), the function name, and an opening parenthesis. Click the function name to view the Help topic on the function. You can also click an argument name in the formula to select the corresponding argument in your sheet.

Avoid common errors when creating formulas

The following table summarizes some of the most common errors that you can make when entering a formula and how to correct those errors:

Make sure that you…	More information
Match all open and close parentheses	Make sure that all parentheses are part of a matching pair. When you create a formula, Excel displays parentheses in color as they are entered.
Use a colon to indicate a range	When you refer to a range of cells, use a colon (:) to separate the reference to the first cell in the range and the reference to the last cell in the range. For example, A1:A5.
Enter all required arguments	Some functions have required arguments. Also, make sure that you have not entered too many arguments.
Nest no more than 64 functions	You can enter, or nest, no more than 64 levels of functions within a function.
Enclose other sheet names in single quotation marks	If the formula refers to values or cells on other sheets or workbooks, and the name of the other workbook or sheet contains a non-alphanumeric character, you must enclose its name within single quotation marks ( ' ).
Include the path to external workbooks	Make sure that each external reference contains a workbook name and the path to the workbook.
Enter numbers without formatting	Do not format numbers as you enter them in formulas. For example, even if the value that you want to enter is $1,000, enter 1000 in the formula.

See also

Enter a formula

Calculation operators and order of operations

Switch between relative and absolute references

Use AutoComplete when entering formulas

Use names in formulas