GCFLearnFree.org

This site covers more than I ever dreamed.”

Online Learner

GCFGlobal.org
Creating Opportunities For A Better Life.®
  • All Topics
  • Excel 2003
  • Creating Complex Formulas

Excel 2003

Single page view print copy

Creating Complex Formulas

Creating an absolute reference

In earlier lessons, we saw how cell references in formulas automatically adjust to new locations when the formula is pasted into different cells.

Sometimes when you copy and paste a formula, you don't want one or more cell references to change. An absolute reference solves this problem. Absolute cell references in a formula always refer to the same cell or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.

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

$A$2 The column and the row do not change when copied.
A$2 The row does not change when copied.
$A2 The column does not change when copied.

To create an absolute reference:

  • Enter the numbers you want to calculate (e,g., 34567 in cell B2 and 1234 in cell B3).
  • Create a simple formula (=B2+B3).

    Create a Simple Formula

  • To create an absolute reference in the formula you just created, insert a $ value before the B (column reference) and 2 (row reference) in the reference to B2 so the new formula reads: (=$B$2+B3).

    Insert $ Values to Indicate an Absolute Reference

  • Copy and paste the formula into another adjacent cell. The formula now includes an absolute reference to B2: (=$B$2+D3).

    Paste the Formula Containing an Absolute Reference

previous next
previous 1 2 3 4 5 6 7 8 9 next
Terms Of Use

©1998-2014 Goodwill Community Foundation, Inc. All rights reserved.