Every day I fall a little more in love with your site.”

Online Learner

Creating Opportunities For A Better Life.®

Excel 2010

Single page view print copy

Creating Complex Formulas

Working with cell references

Video: Working with Cell References in Excel 2010

Launch video!Watch the video (4:38).

In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.

Excel will interpret cell references as either relative or absolute. By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).

Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.

Relative references

Relative references can save you time when you are repeating the same kind of calculation across multiple rows or columns.

In the following example, we are creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months, we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.) For convenience, we can copy the formula in B4 into the rest of row 4, and Excel will calculate the value of the bills for those months using relative references.

To create and copy a formula using relative references:

  1. Select the first cell where you want to enter the formula (for example, B4).
    Selecting cell B4 Selecting cell B4
  2. Enter the formula to calculate the value you want (for example, add B2+B3).
    Entering formula into B4 Entering formula into B4
  3. Press Enter. The formula will be calculated.
    Result in B4 Result in B4
  4. Select the cell you want to copy (for example, B4), then click on the Copy command from the Home tab.
  5. Select the cells where you want to paste the formula, then click on the Paste command from the Home tab. (You can also drag the fill handle to fill cells.)
    Values calculated in C4:M4 Values calculated in C4:M4
  6. Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.), and the values are calculated.
1 2 3 4 5 6