Understanding Absolute, Relative, and Mixed Cell References in MS Excel would help you work more efficiently and save a lot of time! Let us directly dive into understanding what are cell references and the types of cell references in MS Excel present to ease our work.
Introduction to Cell Referencing:
In Excel, a cell reference (example A1) is a combination of column name (i..e., A) followed by a row number (i.e., 1). By default settings, each cell in Excel is denoted with a column letter and a row number. While writing formulas, the user can refer to cells/ range of cells which is the ultimate power of cell references in MS Excel. For example, suppose we have the following data in cell A1 = 10 and cell A2 = 5. Let’s say you type formula as =A1+A2. Then Excel shall calculate the sum of values present in those cells which are 10+5 = 15.
Types of Cell References in MS Excel:
There are three types of cell references in MS Excel:
1. Relative Cell Referencing in Excel:
A Relative cell reference changes when copying or moving the formula or cell value to a different location in the worksheet. Relative references are very useful when you have a range of cells and you want the references to relatively change as and when you copy the formula down to other cells.
Let us understand this with a small example.
Below is an image that displays the three different tables. The first contains the original Data, the second contains a Result that appears using the formula (i.e. cell referencing), and the third table portrays the formula that we used (i.e. the cell addresses).
1. In the cell G5 below we try to link it with C5
2. It gives the same value that is present in cell C5 i.e. the number 5
3. When you copy the link from G5 TO H6, the reference shall relatively change from C5 TO D6
2. Absolute Cell Referencing in Excel:
An absolute cell reference does not change while copying or moving the formula to a different location in the worksheet. The cell references are fixed. Now you would wonder how can you fix cell reference?
To fix the cell references, we need to add a Dollar sign ($) before the column name and the row number by pressing the key F4. The F4 Key will allow you to add a dollar sign automatically before the cell references.
When a dollar sign is added before the column name and row number it fixes the references, as in stops the references from changing when copying the formula to the other cells.
Let us understand this with an example.
- In the below image, in cell G5, we link cell C5. But we want to fix the cell reference so that it does not change when copying the formula to other cells. To fix the cells press the key F4, which will insert a dollar symbol before the column name and row number
2. It returns the same value as in cell C5. Now when you copy the formula from G5 to H5, it still returns the same value as in C5, since the reference is now fixed and shall not change when copying the formula
3. Mixed Cell Reference in Excel
Mixed cell reference allows fixing either the column name or the row number in a cell reference. That is one shall be relative and one shall be fixed.
There are two types of mixed cell references:
a. Fixed Row and Relative Column (i.e. the row reference is fixed but the column reference changes when the formula is copied to another location)
b. Relative Row and Fixed Column (i.e. the row reference changes but the column reference is fixed when a formula is copied to another location). When you use mixed references, the dollar sign shall be fixed only either before the column name or row number.
Let us understand this with an example:
a. Example of Mixed Referencing- Fixed Row and Relative Column
1.In the cell G5, we try to link the cell C5, but we do want row 5 to be fixed and not change when we copy the formula to other location, hence prefix a dollar sign before the row number 5
2.Now when copied the formula to cell H6, you would notice that the column reference has changed to “D”, but the row reference remains the same as “5”
b. Example of Mixed Referencing- Relative Row and Fixed Column
1.In the cell G5, we try to link the cell C5, but we do want the column C to be fixed and not change when we copy the formula to other location, hence prefix a dollar sign before the Column name “C”
2.Now when copied the formula to cell H6, you would notice that the column reference is fixed to “C”, but the row reference has changed from “5” to “6”
Follow our Youtube channel for more updates.
Visit our website for more updates on MS Excel