In Microsoft Excel, a cell reference is how you refer to the 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.
Understanding your options for Excel cell references are 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 Microsoft Excel, there are 3 types of references:
- Circular (which you DO NOT want to have in a worksheet)
Mixed references are a combination of relative and absolute.
The default structure of a formula in Excel is that the formula adjusts 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 you copy or move formulas.
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.” It’s 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 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 which updates to C$4 when copied one column to the right.
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. 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.
- 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 upper left corner of the cell.
Understanding the different types of cell references in formulas is one of the keys to accurate results and success in Microsoft Excel. This is key to more easily and successfully building and editing formulas.