Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

A formula can also contain any or all of the following: functions, references, operators, and constants .

parts of a formula Parts of a formula

callout 1 Functions: The PI() function returns the value of pi: 3.142...

callout 2 References: A2 returns the value in cell A2.

callout 3 Constants: Numbers or text values entered directly into a formula, such as 2.

callout 4 Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies.

What do you want to do?

Create a simple formula with constants and calculation operators

The following formulas contain operators and constants .

Example formula What it does
=128+345 Adds 128 and 345
=5^2 Squares 5
  1. Click the cell in which you want to enter the formula.
  2. Type = (an equal sign).
  3. Enter the formula.
  4. Press ENTER.

Create a formula with functions

The following formulas contain functions .

Example formula What it does
=SUM(A:A) Adds all numbers in column A
=AVERAGE(A1:B4) Averages all numbers in the range
  1. Click the cell in which you want to enter the formula.
  2. To start the formula with the function, click Insert Function button image on the formula bar formula bar.
  3. Select the function you want to use. You can enter a question that describes what you want to do in the Search for a function box (for example, "add numbers" returns the SUM function), or browse from the categories in the Or Select a category box.
  4. Enter the arguments . To enter cell references as an argument, click Collapse Dialog button image (which temporarily hides the dialog box), select the cells on the worksheet, and then press Expand Dialog button image.
  5. When you complete the formula, press ENTER.

Create a formula with nested functions

Nested functions 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.

nested functions

callout 1 The AVERAGE and SUM functions are nested within the IF function.

  1. Click the cell in which you want to enter the formula.
  2. To start the formula with the function, click Function Wizard button image on the formula bar formula bar.
  3. Select the function you want to use. You can enter a question that describes what you want to do in the Search for a function box (for example, "add numbers" returns the SUM function), or browse from the categories in the Or Select a category box.
  4. Enter the arguments .
    • To enter cell references as an argument, click Collapse Dialog button image next to the argument you want (which temporarily hides the dialog box), select the cells on the worksheet, and then press Expand Dialog button image.
    • 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 in the Function Arguments dialog box reflect the function that you selected in the previous step. For example, if you clicked IF, Function arguments displays the arguments for the IF function.

    Create a formula with references and names

    The following formulas contain relative references to and names of other cells. 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.

    Example formula What it does
    =C2 Uses the value in the cell C2
    =Sheet2!B2 Uses the value in cell B2 on Sheet2
    =Asset-Liability Subtracts a cell named Liability from a cell named Asset
    1. Click the cell in which you want to enter the formula.
    2. In the formula bar 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 worksheet, or a location in another workbook. This behavior is called semi-selection. You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.

        range finder color-codes precedent cells

        callout 1 The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.

        callout 2 The second cell reference is C3, the color is green, and the cell range has a green border with square corners.

        Note If there is no square corner on a color-coded border, then the reference is to a named range.

      • To enter a reference to a named range, press F3, select the name in the Paste name box, and click OK.
    4. Press ENTER.

    For more information, see .

    Create an array formula that calculates a single result

    You can use an array formula to perform several calculations to generate a single result. This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.

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

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

      array formula that produces a single resultArray formula that produces a single result

      When you enter the formula {=SUM(B2:C2*B3:C3)} as an array formula, it multiples the Shares and Price for each stock, and then adds the results of those calculations together to get a total value of 9500.

    3. Press CTRL+SHIFT+ENTER.

      Microsoft Office Excel automatically inserts the formula between { } (braces).

    Important When you edit the array formula, the braces ({ }) do not appear in the array formula, and you must press CTRL+SHIFT+ENTER again.

    Create an array formula that calculates multiple results

    Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with 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. Type the array formula.

      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 of the results of the formula, it is entered into three cells in column C (C1:C3).

      array formula that produces multiple resultsArray formula that produces multiple results

      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 CTRL+SHIFT+ENTER.

      Excel automatically inserts the formula between { } (braces).

    Important When you edit the array formula, the braces ({ }) do not appear in the array formula, and you must press CTRL+SHIFT+ENTER again.

    Delete a formula

    1. Click the cell that contains the formula.
    2. Press DELETE.

    Learn tips and tricks about creating formulas

    Quickly copy formulas You can quickly enter the same formula into a range of cells. Select the range you want to calculate, type the formula, and then press CTRL+ENTER. For example, if you type =SUM(A1:B1) in range C1:C5, and then press CTRL+ENTER, Excel enters the formula in each cell of the range, using A1 as a relative 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 or a display trigger, Excel displays below the cell a dynamic drop down list of valid functions, arguments, and names that match the letters or trigger. You can then insert an item in the drop-down list into the formula.

    Use Function tooltips If you are familiar with the arguments of a function, you can use the function tooltip that appears after you type the function name and an opening parenthesis. Click the function name to view the Help topic on the function, or click an argument name to select the corresponding argument in your formula.

    Avoid common errors when creating formulas

    The following table summarizes some of the 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, Microsoft Office 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.
    Enter all required arguments Some functions have required arguments . Also, make sure that you have not entered too many arguments.
    Nest no more than seven functions You can enter, or nest, no more than seven levels of functions within a function.
    Enclose other sheet names in single quotation marks If the formula refers to values or cells on other worksheets or workbooks and the name of the other workbook or worksheet contains a nonalphabetical 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: