Excel for Mac 2011: Make Relative References on Worksheets - dummies

Excel for Mac 2011: Make Relative References on Worksheets

By Geetesh Bajaj, James Gordon

Copying and pasting cells and cell formulas works differently from text and other objects in Excel in Office 2011 for Mac, so they can be a little bit puzzling if you don’t know the secrets about references.

Say you want to use the value of cell A1 in a formula, so you type =A1 in your formula to use the value of cell A1 and away you go. Your formula works, and all seems well. What Excel is actually thinking when you type =A1 is that you want to use the value of the cell that’s the number of rows and columns away from the cell in which you’re typing your formula.

Your formula works as expected in its original location, but if you copy the cell containing your formula and then paste that cell somewhere else, the formula in the pasted cell no longer refers to the value in cell A1. Instead, it refers to the cell in the relative location (the same number of rows and columns away from the copied cell). This concept of relativity is why this reference style is a relative reference.

Here’s a fun little example with a relative reference. Start with a blank worksheet and follow these steps:

  1. Click in a cell, any cell.

  2. Type something in the cell.

  3. Click in any other cell.

  4. In the cell you chose in Step 3, type a formula to equal what’s in the cell from Step 1 and then click the green Enter button.

    Cell B2’s formula has a relative reference to cell A1, which is one column to the left and one row up.

  5. Select the cell that you just typed in and then press Command-C.

    This copies the cell you just put the formula into.

  6. Click in any unused cell and choose Edit→Paste or press Command-V.

    Notice that the pasted cell shows zero instead of the value of cell A1. The pasted cell refers to empty cell A3 as you can see in the Formula bar, and by choosing Trace Empty Cell in the error widget. Cell A3 is one row up and one row over from the pasted cell B4. That’s because the copy/paste operation pastes a relative reference by default.

Excel notices two things and displays two widgets:

  • Exclamation Point widget: Indicates an error of some sort. Click to display an alert that the formula references an empty cell.

  • Clipboard widget: When clicked, the Clipboard widget displays Paste Special options.