Exploring Options for Microsoft Excel Cell References

In Microsoft Excel, a cell reference is how you refer to the cell address or location. In its simplest expression, a cell address is just the label for the location, such as A1, which is the intersection of Column A and Row 1.

Understanding your options for Excel cell references is key to the accuracy and success of your calculations. When you are creating formulas, you may work with more than one type of cell reference.

In Excel, there are 3 types of cell references:

• Relative
• Absolute
• Circular (which you DO NOT want to have in a worksheet)

Mixed cell references are a combination of relative and absolute references.

Relative References

The default structure of a formula in Excel is that cell references adjust automatically when you copy a formula from one cell to another. This adjustment is known as relative referencing. That is, the contents of a formula adjust relative to (or based on) the formula’s new location. This behavior in Excel cell references is why formulas update and adjust when you copy or move formulas.

Absolute References

When you don’t want the cell references to change when a formula is copied to a different cell, you must designate one or more values to be constant. This is called absolute referencing. Absolute cell references are often used when a formula refers to a constant value in a worksheet, such as an interest rate, labor cost, or projection percentage. An absolute cell reference says, “absolutely or always use this value regardless of the  location of the formula.” Think of an absolute reference as the GPS of a formula–it is an exact destination regardless of your starting location.

You can create an absolute reference for a constant by placing a $ in front of the column and/or row location that does not change.  You can also designate a part of a formula as absolute by highlighting the cell and pressing the [F4] function key.

Examples of absolute (or mixed) cell references include:

• $G$5
• B$3
• $C8

When building a formula, press [F4] repeatedly to switch the cell reference from a pure absolute reference, mixed references, or back to a relative reference.

Mixed References

A mixed cell reference is actually just a formula or a cell with both relative and absolute references. For example, if you want to consistently refer to values in Row 4 yet copy the formula across each column, the cell reference to the value might be B$4, which updates to C$4 when copied one column to the right.

Circular References

A circular reference occurs when a cell in an Excel worksheet refers to itself, directly or indirectly. For example, if =100+B2 is entered into cell B2, a direct circular reference has been created. An indirect circular reference is when the formula in a given cell refers to one or more other cells that refer back to the original cell. For example, a formula in C1 refers to cell C2, C2 refers to C3, and C3 refers back to C1.

When Excel encounters a circular reference in a worksheet, a Circular Reference Warning displays a dialog box when the workbook is first opened. You can either ignore the circular reference, or you can locate it to edit the worksheet. STOP! Ignoring circular references risks that your data is incorrect and may lead to bad decisions. Unfortunately, because many Excel users don’t understand circular references, they click through the warning without any additional action.

If you see the Circular Reference dialog box, click OK, and then track down the problem references so you can correct the formula logic:

• Look in the status bar. If you see the message Circular References followed by a cell, then the error is on the active worksheet. If not, move to other worksheets in the workbook until you find the worksheet with one or more cell references.
  -OR-
• Even easier with multiple worksheets, pick Formulas > Error Checking > Circular References, which shows you the circular references in the open workbooks.

Excel circular references will usually be visually flagged in the worksheet with a green marker in the cell’s upper left corner.

Understanding the different types of cell references in formulas is one of the keys to accurate results and success in Microsoft Excel.

To extend your learning on Microsoft Excel, check out these additional shortcuts, tips, and techniques at TheSoftwarePro.com/Excel.

© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional