Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Apple > Operating Systems

  • Print
  • + Share This
From the author of

But Wait! There's More!

In addition to relative and absolute cell references, there are mixed cell references. In a mixed cell reference, one part of the reference is absolute and the other is relative.

Huh?

Look at the worksheet in Figure 7. Suppose you wanted to write a formula in cell C9 that could be copied to down and across through cell E12 without giving any incorrect results or error messages.

Figure 7Figure 7 The challenge: Write a formula in cell C9 that can be copied through cell E12.

Impossible, you say? Not at all. The formula would be =$B9*C$6.

To understand what this formula means to Excel, substitute the word always for the dollar sign. $B9 says always column B but the row reference is relative. C$6 says the column reference is relative but the row is always row 6. Get it?

Give it a try. When you copy the formula to the cells, the references and results are all correct, as shown in Figure 8. Take a moment to select the cells, one at a time, and look at the formula Excel wrote—you can see it in the formula bar.

Figure 8Figure 8 A properly written formula can be copied with flexibility.

There's a shortcut for writing these fancy cell references. If you select a cell reference in a formula and press F4 (Windows) or Command-T (Mac OS), Excel cycles through the relative, absolute, and mixed cell reference options for the selected reference. For example, select cell C6. Each time you press F4 or Command-T, Excel cycles through $C$6, C$6, $C6, and back to C6. Stop pressing F4 or Command-T when you see the one you want.

  • + Share This
  • 🔖 Save To Your Account