Exploring Options for Excel Cell References
Understanding your options for Excel cell references are key to the accuracy in success of your calculations. When you are creating formulas, you may work with more than one type of cell reference. A cell reference is how you refer to the cell address or location. In it’s 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.
In Excel, there are 3 types of cell references:
- Circular (which you DO NOT want to have in a worksheet)
Mixed cell references are just a combination of relative and absolute 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, that the contents of a formula adjust relative (or based on) the new location of the formula. This behavior in Excel cell references is why formulas update and adjust when copy or move formulas.
When you don’t want the cell references to change when aformula 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 or projection percentage. An absolute cell reference says “absolutely or always use this value regardless of the location of the formula.”
You can create an absolute reference for a constant by placing a $ in front of the column and/or row location that do 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:
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.
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.
A circular reference occurs when a cell in an Excel worksheet refers to itself, whether 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 in return 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 in 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. 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.
- 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 upper left corner of the cell.
Understanding the different types of cell references in formulas is key to your success in Microsoft Excel.
To extend your learning on Microsoft Excel, check out these additional shortcuts, tips and techniques at TheSoftwarePro.com/Excel.
By Dawn Bjork, MCT, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant