Creating Simple Formulas

When using formulas in Excel you can add, subtract, multiply, and divide. Use simple Excel formulas to also make cell references in Excel.

Introduction

By the end of this lesson, you should be able to:

  • Understand the parts of an Excel formula
  • Create a simple addition formula
  • Create a simple subtraction formula using the point-and-click method
  • Create simple multiplication formulas
  • Create simple division formulas

About formulas

In school, you learned formulas to calculate math problems. Microsoft Excel uses these same formulas to perform calculations in a spreadsheet.

A formula can be a combination of values (numbers or cell references) and math operators (+, -, /, *, =) into an algebraic expression. Excel requires every formula to begin with an equals sign (=).

The following table illustrates the mathematical operators learned in school and those represented in Excel 2003.

SchoolExcel 2003
Addition++
Subtraction--
MultiplicationX*
Division//
Equals==

The result of a formula—the answer to 2+3, for example—displays in the cell on the Excel worksheet. The formula is visible only in the Formula bar. A formula's result will change as different numbers are entered into the cells included in the formula's definition.

The Formula Bar

Creating a simple addition formula

A simple formula in Excel contains one mathematical operation only: one number plus a second number equals a third number. Writing a simple formula is no more difficult than that: 1+1. The only difference in Excel is that all formulas must begin with the equals sign (=). It is not enough to type 1+1 in Excel because what will appear in the cell is "1+1." You must begin the equation with an equals sign, or =1+1. This holds true for any formula—simple or complicated—that adds, subtracts, multiplies, or divides.

Let's add two numbers to create a third: 128+345=473. In Excel, this would be expressed by the formula =128+345, as shown below.

Simple Addition Formula

To create a simple formula that adds two numbers:

  • Click the cell where the formula will be defined.
  • Type the equals sign (=) to let Excel know that a formula is being defined.
  • Type the first number to be added (128, for example).
  • Type the addition sign (+) to let Excel know that an add operation is to be performed.
  • Type the second number to be added (345, for example).
  • Press Enter, or click the Enter button on the Formula bar to complete the formula.

Excel will not always tell you if your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, you can read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

Creating a simple addition formula

What if a column contains many numbers, each of which regularly changes? You don't want to write a new formula each time a number is changed. Luckily, Excel 2003 lets you include cell references in formulas.

A formula can add the value of two cells—B2 and B3, for example. Type any two values in these two cells, and the formula will adjust the answer accordingly.

Using this method to calculate two numbers—128 and 345, for example—requires that you type 128 in cell B2, for example, and 345 in cell B3. The Excel formula, =B2+B3, would then be defined in cell B4.

Simple Addition Formula

To create a simple formula that adds the contents of two cells:

  • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
  • Click the cell where the answer will appear (B4, for example).
  • Type the equals sign (=) to let Excel know that a formula is being defined.
  • Type the cell number that contains the first number to be added (B2, for example).
  • Type the addition sign (+) to let Excel know that an add operation is to be performed.
  • Type the cell number that contains the first number to be added (B3, for example).
  • Press Enter, or click the Enter button on the Formula bar to complete the formula.

Creating a subtraction formula using the point-and-click method

Formulas can be created by using either numbers or cell references in the definition. You can also use the mouse to select the cells to be used in the formula instead of typing the cell number or cell reference. Using this method, we are going to write a simple formula that subtracts one cell from another: =B3-B2.

Simple Subtraction Formula

To create a simple formula using the point-and-click method:

  • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
  • Click the cell where the answer will appear (B4, for example).
  • Type the equals sign (=) to let Excel know that a formula is being defined.
  • Click on the first cell to be included in the formula (B3, for example).
  • Type the subtraction sign (-) to let Excel know that a subtraction operation is to be performed.
  • Click on the next cell in the formula (B2, for example).
  • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
  • Press Enter, or click the Enter button on the Formula bar to complete the formula.

Creating simple multiplication formulas

Creating multiplication formulas is similar to addition and subtraction formulas. To multiply two cells the formula—B2 and B3—you would need to insert a multiplication operator * between them: =B2*B3.

Simple Multiplication Formula

To create a simple formula that multiplies the contents of two cells:

  • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
  • Click the cell where the answer will appear (B4, for example).
  • Type the equals sign (=) to let Excel know a formula is being defined.
  • Click the first cell to be included in the formula (B2, for example).
  • Type a mathematical operator (the multiplication symbol, or *). The operator displays in the cell and Formula bar.
  • Click the next cell in the formula (B3, for example).
  • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
  • Press Enter, or click the Enter button on the Formula bar to complete the formula.

Creating simple division formulas

Creating division formulas is similar to the addition, subtraction, and multiplication formulas. To divide the contents of cell B2 by cell B3, you would need to insert a division operator, or /, between them: =B2/B3.

To create a simple formula that divides one cell by another:

Simple Division Formula

  • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
  • Click the cell where the answer will appear (B4, for example).
  • Type the equals sign (=) to let Excel know a formula is being defined.
  • Click the first cell to be included in the formula (B2, for example).
  • Type a mathematical operator (the division symbol, or /). The operator displays in the cell and Formula bar.
  • Click the next cell in the formula (B3, for example).
  • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
  • Press Enter, or click the Enter button on the Formula bar. This step ends the formula.

Challenge!

  • Open your Monthly Budget file.
  • Add cells C2 through C10 using a handheld calculator, the calculator on your computer, or a pencil and paper.
    • If you included additional monthly bills in cells C11 through 14, add cells C2 through C14 together to get your total monthly expenses.
    Challenge 5

How long did it take you to add those numbers? In the next three modules, you will learn how Excel can do the math for you!

  • Type the total you came up with in cell C15.
  • Type a subtraction formula in C17 that subtracts the amount in C15 from the amount in C16.
  • Save and close the Monthly Budget file.