Absolute, Relative And Mixed Cell References in Excel

If you want to use Excel like a power user, you will need to understand the cell addressing in an Excel workbook.

In Excel, a cell reference points to a cell on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values that you want the formula to calculate.

Cell reference is the format used for addressing a particular cell. Each cell is created at the crossing of a row and a column. Therefore, every cell can be uniquely addressed using the column and row number. Excel addresses each cell with (Column Letter)(Row Number) format. For example, cell C7 cell is located at the crossing of column C and row number 7.

A cell address is also called cell reference because Excel uses this cell address to refer to a cell.

There are three types of cell references in Excel:

1. Relative
2. Absolute
3. Mixed

In this article we will examine the difference between absolute, relative and mixed cell references in Excel.

Relative Cell Reference

Relative cell reference indicates that the reference will change if it is copied and pasted elsewhere in the worksheet. Let’s understand it by example. Open a new worksheet and enter the values in cells as follows:

• A1 = 7
• A2 = 5
• B1 = 4
• B2 = 2

Now in cell C4 type the following formula:

=SUM(A1,B1)

Press enter and you will see that C4 will show (7+4 = 11) as sum.

Now select cell C4 and press Ctrl+C to copy the formula

Select Cell C5 and press Ctrl+V to paste the copied formula. You will see that C5 will show sum result as 7 because moving the formula also automatically changed the cell reference from A1,B1 to A2,B2.

Cell references like A1 and B1 are relative references and they see the target cell with respect to the location of formula.

While working on the formula in cell C4, Excel will need to find the value in cells A1 and B1. How does it find these values? Well, it searches for B1 like a crossword puzzle… three cells up and one cell to left from the location of formula.

When you copy the formula and paste it in cell C5, even then Excel will follow the same steps for locating the second cell mentioned in the formula. That is to say that Excel will still go three cells up and one cell to the left. As a result the target cell will change from B1 to B2.

So, this is the Relative Reference. Excel calculate a cell’s location with respect to the location of the formula containing cell. The benefit of relative referencing is that your formula will automatically change if you need to make several copies of the same formula (for example, through auto fill). The drawback of relative referencing is that it may throw unexpected results if you don’t know what you’re doing.

Absolute Cell Reference

Absolute cell reference means that the reference will not change if it is copied and pasted somewhere else. For example, if you copy a formula containing absolute cell references and paste it elsewhere, the references will still point to exactly the same cells as they were pointing in formula’s original location.

To make a cell reference absolute, just add $ sign before the column number or row number or both of them:

1. A2 = both column and row references are relative
2. $A2 = column reference is absolute, row reference is relative
3. A$2 = column reference is relative, row reference is absolute
4. $A$2 = both column and row references are absolute

Let’s understand it with the help of an example. Let’s use the same worksheet that we created for the previous example. Type the following formula in cell C7

=($A$1*B1)

This formula multiplies the value of cell A1 with the value of cell B1. Note that the first cell reference is fully absolute ($A$1) and the second cell reference (B1) is fully relative.

The result of this formula will be 28.

Now let’s copy this formula and paste it in cell C11. You will see that the absolutely referenced cell $A$1 will remain as it is while the relative reference B1 will change to B5.

Question for you: Can you now suggest why B1 changed to B5 and not B4 or B6? We have already explained the answer :-)

Mixed Cell Reference

Mixed cell reference occurs when we use both relative and absolute references to refer to a cell. For example, A$1 is a mixed reference because the column name (A) is relatively referred to and row number is absolutely referred to ($1). Similarly $C5 is also an example of mixed reference.

Switch Between Absolute, Relative and Mixed References

By pressing F4 key, you can switch among various types of references in Excel. Let’s see it by an example:

1. Type the following formula in any cell
   =(A1+B1)
2. Press F4 key and Excel will change B1 to $B$1 (fully absolute)
3. Press F4 key again and $B$1 will change to B$1 (mixed)
4. Press F4 key again and B$1 will change to $B1 (mixed)
5. Press F4 key again and $B1 will change to B1 (fully relative)
6. If you want to change reference type of A1, just place your typing cursor at A1 in the formula and then press F4

Difference between Absolute and Relative Cell Reference

	Absolute Reference	Relative Reference
1.	Does not change when formula is moved to another location	Changes according to the location of the formula
2.	$ sign is used to indicate absolute reference	No special sign is required.
3.	In case formula is moved, result will remain unchanged if all references are fully absolute.	Result will change if formula is moved.
4.	Useful if we want formula results to remain same irrespective of its location.	Useful if we want to use facilities like flash fill.

Which Excel Version Does it Apply to?

Cell referencing is a core concept of Microsoft Excel. It remains same across all the versions of Excel. So, it does not matter whether you are using Excel version XP, 2007, 2010, 2013 or 2016 —the above mentioned concept and examples will hold true.

So, this was all about cell references in Microsoft Excel. We hope that you found this article helpful. Should you have any questions on this subject, please feel free to ask in the comments section. We will try our best to assist you. Thank you for using TechWelkin!