Enter a formula

Try Office 2010 In Excel 2010, entering and editing formulas and using functions in formulas is easier, thanks to improvements to the formula bar and Function AutoComplete.
Read an article or try Office 2010!

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

Enter a simple formula: =128+345

The following formulas contain operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.).

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.

Enter a formula that contains references or names: =A1+23

The following formulas contain relative references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to and names (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) 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: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) 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. 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

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

Enter a formula that contains a function: =AVERAGE(A1:B4)

The following formulas contain functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.).

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: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) 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 (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.). To enter cell references as an argument, click Collapse Dialog Button image to temporarily hide the dialog box. Select the cells on the worksheet, then press Expand Dialog Button image.
  5. When you complete the formula, press ENTER.

Enter a formula with nested functions: =IF

Nested functions use a function as one of the arguments of another function. 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

  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: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) 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 (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.).
    • To enter cell references as an argument, click Collapse Dialog Button image next to the argument you want to temporarily hide the dialog box. Select the cells on the worksheet; then press Expand Dialog Button image.
    • To enter another function as an argument, enter the function in the argument box you want. For example, you can add SUM(G2:G5) in the Value_if_true edit box.
    • To switch the parts of the formula displayed in the Function Arguments dialog box, click a function name in the formula bar. For example, if you click IF, the arguments for the IF function appear.

(AVERAGE(F2:F5)>50, SUM(G2:G5),0)

Tips

 
 
Applies to:
Excel 2003