Excel 2010 Introduction – Lesson 3 – Building Formulas

Lesson Three: Building Formulas

The backbone of Excel is its ability to perform calculations. There are two ways to set up calculations in Excel: using formulas or using functions. Formulas are mathematical expressions that you build yourself. You need to follow proper math principles in order to obtain the expected answer. Building the formula is simply a matter of combining the proper cell addresses with the correct operators in the right order. This module will explore how to build, edit, and copy formulas. This module will also explain the difference between relative and absolute references. We will explore functions in the next module.

The Math Basics of Excel

Excel performs calculations by combining the contents of cells using operators. There are several different types of operators to perform arithmetic, comparisons, and text concatenation operations, as well as to provide references to use in those calculations.

Review the different types of operators.

The Arithmetic operators are:

  • + Plus Sign – Adds values
  • – Minus Sign – Subtracts values
  • * Asterisk – Multiplies values
  • / Forward slash – Divides values
  • % Percent sign – Finds the percentage of a value
  • ^ Caret – Exponentiation – Finds the exponential value

The Comparison operators are:

  • = Equals sign – Equates values
  • > Greater than sign – Indicates that one value is greater than the other
  • < Less than sign – Indicates that one value is less than the other
  • >= Greater than or equal to – Indicates that one value is greater than or equal to the other
  • <= Less than or equal to – Indicates that one value is less than or equal to the other
  • <> Not Equal – Indicates that values are not equal

Text concatenation allows you to combine text from different cells into a single piece of text. The operator is the & sign.

The reference operators combine a range of cells to use together in an operation. The reference operators are:

  • : Colon – A Range operator that produces a reference to all of the cells between the references on either side of the colon
  • , Comma – A Union operator that combines multiple range references
  • Space – An intersection operator that returns a reference to the cells common to the ranges in the formula

Building a Formula

All formulas begin with the = symbol. To enter a formula, click the cell where you want the formula and begin typing. When you click on another cell, the contents of that cell will be included in the formula. Press Enter when you have finished entering the formula.
Use the following procedure to enter a formula to calculate the Total Value in the sample worksheet

  1. Click on the Total Value column for the first product (cell D4).
  2. Enter the = sign to begin the formula.
  3. Click on cell B4 to use it as the first value in the formula. Excel enters the reference as part of the formula.

  1. Enter the * sign.
  2. Click on cell C4 to use it as the second value in the formula. Excel enters the references as part of the formula.

  1. Press ENTER to complete the formula. Excel moves to the next row and performs the calculations in the formula.

The following illustration shows the answer to the calculation in the cell, and since the cell is active, you can see the formula in the Formula bar.

Editing a Formula

If you have made an error in a formula, you can easily correct it. Both the operators and the cell references can be edited.

Use the following procedure to edit a formula. The following example uses an incorrect cell reference in a formula.

  1. Click on the cell with the formula you want to correct to make it active.
  2. Click on the Formula Bar. Excel highlights the cell references in the current formula.

  1. Highlight the operator or cell references and either type over with the correct reference or operator, or click the correct cell to replace a cell reference.

  1. Press ENTER to complete the formula. Excel calculates the formula and moves to the next row.

Copying a Formula

In Excel, you often repeat the same formula, changing only the cells you are referencing. You can easily copy the formula instead of having to re-enter it multiple times. If you are familiar with copy and paste from other applications, this is an easy way to copy a formula. You can also paste in multiple cells at once by highlighting all of them before pasting.
Use the following procedure to copy and paste a formula.

  1. Right click on the cell with the formula you want to copy. This displays the context menu. Select Copy. You can also click on the cell and use the keyboard shortcut: CTRL + C.

Excel highlights the cell whose contents you are copying. This will remain highlighted until you finish pasting, in case you want to paste the cell contents more than once.

  1. Right click on the cell where you want to copy the formula. Excel displays a number of paste options. To paste a formula, select Paste or Paste formula. You can also click on the cell and use the keyboard shortcut: CTRL + V.

  1. You can repeat the paste as many times as desired. Or you can highlight multiple cells at once before pasting to repeat the paste for all highlighted cells.
  2. Press ENTER to stop pasting.

Relative vs. Absolute References

Copying formulas works because of Relative Referencing. Formula references actually reference the cell relative to the formula location. This means that when you copy the formula, the new formulas reference cells in the same way as the original formula, but relative to the new cell location. However, absolute referencing references the exact same cell address no matter where the formula is copied. To create an absolute reference in Excel, put dollar signs before the column address and before the row address. For example, $A$2.

Use the following procedure to copy a formula with an absolute reference.

  1. Create a new column labeled Taxes.
  2. Click on the Taxes column for the first product (cell E4).
  3. Enter the = sign to begin the formula.
  4. Click on cell B16 to use it as the first value in the formula. Excel enters the reference as part of the formula. Use the Formula Bar to enter dollar signs before the column and the row (i.e., $B$16).
  5. Enter * and the relative reference in the Total Value column.

  1. Press ENTER to complete the formula. Excel moves to the next row and performs the calculations in the formula.

Copy the formula for the other products and click on some of them to see the results.

Module Three: Building Formulas

The backbone of Excel is its ability to perform calculations. There are two ways to set up calculations in Excel: using formulas or using functions. Formulas are mathematical expressions that you build yourself. You need to follow proper math principles in order to obtain the expected answer. Building the formula is simply a matter of combining the proper cell addresses with the correct operators in the right order. This module will explore how to build, edit, and copy formulas. This module will also explain the difference between relative and absolute references. We will explore functions in the next module.

The Math Basics of Excel

Excel performs calculations by combining the contents of cells using operators. There are several different types of operators to perform arithmetic, comparisons, and text concatenation operations, as well as to provide references to use in those calculations.

Review the different types of operators.

The Arithmetic operators are:

  • + Plus Sign – Adds values
  • – Minus Sign – Subtracts values
  • * Asterisk – Multiplies values
  • / Forward slash – Divides values
  • % Percent sign – Finds the percentage of a value
  • ^ Caret – Exponentiation – Finds the exponential value

The Comparison operators are:

  • = Equals sign – Equates values
  • > Greater than sign – Indicates that one value is greater than the other
  • < Less than sign – Indicates that one value is less than the other
  • >= Greater than or equal to – Indicates that one value is greater than or equal to the other
  • <= Less than or equal to – Indicates that one value is less than or equal to the other
  • <> Not Equal – Indicates that values are not equal

Text concatenation allows you to combine text from different cells into a single piece of text. The operator is the & sign.

The reference operators combine a range of cells to use together in an operation. The reference operators are:

  • : Colon – A Range operator that produces a reference to all of the cells between the references on either side of the colon
  • , Comma – A Union operator that combines multiple range references
  • Space – An intersection operator that returns a reference to the cells common to the ranges in the formula

Building a Formula

All formulas begin with the = symbol. To enter a formula, click the cell where you want the formula and begin typing. When you click on another cell, the contents of that cell will be included in the formula. Press Enter when you have finished entering the formula.
Use the following procedure to enter a formula to calculate the Total Value in the sample worksheet

  1. Click on the Total Value column for the first product (cell D4).
  2. Enter the = sign to begin the formula.
  3. Click on cell B4 to use it as the first value in the formula. Excel enters the reference as part of the formula.

  1. Enter the * sign.
  2. Click on cell C4 to use it as the second value in the formula. Excel enters the references as part of the formula.

  1. Press ENTER to complete the formula. Excel moves to the next row and performs the calculations in the formula.

The following illustration shows the answer to the calculation in the cell, and since the cell is active, you can see the formula in the Formula bar.

Editing a Formula

If you have made an error in a formula, you can easily correct it. Both the operators and the cell references can be edited.

Use the following procedure to edit a formula. The following example uses an incorrect cell reference in a formula.

  1. Click on the cell with the formula you want to correct to make it active.
  2. Click on the Formula Bar. Excel highlights the cell references in the current formula.

  1. Highlight the operator or cell references and either type over with the correct reference or operator, or click the correct cell to replace a cell reference.

  1. Press ENTER to complete the formula. Excel calculates the formula and moves to the next row.

Copying a Formula

In Excel, you often repeat the same formula, changing only the cells you are referencing. You can easily copy the formula instead of having to re-enter it multiple times. If you are familiar with copy and paste from other applications, this is an easy way to copy a formula. You can also paste in multiple cells at once by highlighting all of them before pasting.
Use the following procedure to copy and paste a formula.

  1. Right click on the cell with the formula you want to copy. This displays the context menu. Select Copy. You can also click on the cell and use the keyboard shortcut: CTRL + C.

Excel highlights the cell whose contents you are copying. This will remain highlighted until you finish pasting, in case you want to paste the cell contents more than once.

  1. Right click on the cell where you want to copy the formula. Excel displays a number of paste options. To paste a formula, select Paste or Paste formula. You can also click on the cell and use the keyboard shortcut: CTRL + V.

  1. You can repeat the paste as many times as desired. Or you can highlight multiple cells at once before pasting to repeat the paste for all highlighted cells.
  2. Press ENTER to stop pasting.

Relative vs. Absolute References

Copying formulas works because of Relative Referencing. Formula references actually reference the cell relative to the formula location. This means that when you copy the formula, the new formulas reference cells in the same way as the original formula, but relative to the new cell location. However, absolute referencing references the exact same cell address no matter where the formula is copied. To create an absolute reference in Excel, put dollar signs before the column address and before the row address. For example, $A$2.

Use the following procedure to copy a formula with an absolute reference.

  1. Create a new column labeled Taxes.
  2. Click on the Taxes column for the first product (cell E4).
  3. Enter the = sign to begin the formula.
  4. Click on cell B16 to use it as the first value in the formula. Excel enters the reference as part of the formula. Use the Formula Bar to enter dollar signs before the column and the row (i.e., $B$16).
  5. Enter * and the relative reference in the Total Value column.

  1. Press ENTER to complete the formula. Excel moves to the next row and performs the calculations in the formula.

Copy the formula for the other products and click on some of them to see the results.

Leave a Reply