Creating Complex Formulas

In Excel complex formulas can include several operations. Use complex formulas in Excel to calculate more extensive data.

Absolute references

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.

Absolute Reference Chart

In the below example, we want to calculate the sales tax for a list of products with varying prices. We'll use an absolute reference for the sales tax ($B$1) because we do not want it to change as we are copying the formula down the column of varying prices.

To create and copy a formula using an absolute reference:

  1. Select the first cell where you want to enter the formula (C4, for example).
    Selecting cell C4 Selecting cell C4
  2. Type an equals sign, and then click the cell that contains the first value you want in the formula (B4, for example).
  3. Type the first mathematical operator (the multiplication sign, for example).
  4. Type the dollar sign ($), then enter the column letter of the cell you are making an absolute reference to (B, for example).
    Entering formula Entering formula
  5. Type the dollar sign ($), then enter the row number of the same cell you are making an absolute reference to (1, for example).
    Entering formula Entering formula
  6. Press Enter to calculate the formula.
    Result in C4 Result in C4
  7. Select the cell you want to copy (C4, for example), then click the Copy command from the Home tab.
  8. Select the cells where you want to paste the formula, then click the Paste command from the Home tab. You can also drag the fill handle to fill cells.
    Values calculated in C5:C8 Values calculated in C5:C8
  9. Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.), and your values are calculated.

When writing a formula, you can press the F4 key on your keyboard to switch between relative and absolute cell references, as shown in the video below. This is an easy way to quickly insert an absolute reference.