Cell Referencing in Excel

This article explains cell referencing in MS-Excel, types of cell referencing and how to use different types of referencing.

What is Cell Referencing in Excel?

Cell referencing is same as cell address in MS-Excel. You can check what a cell address is in my last article Introduction to MS-Excel in the topic Introduction to Cell.

Types of Cell Referencing

There are four types of cell referencing:

I. Relative Cell Referencing
II. Absolute Cell Referencing
III. Mixed Cell Referencing
IV. Circular Cell Referencing

I. Relative Cell Referencing

MS Excel uses relative cell references by default. For example, in the below picture, cell C2 references to cell A2 and cell B2. Both are relative references.

Now, select cell C2 and drag down its fill handle upto cell C6. You will see that the values in these cells will be filled automatically by applying the same formula as shown in the below picture. Cell C3 will refer to A3 and B3, cell C4 will refer to A4 and B4 and so on. This is the concept of relative cell referencing.

II. Absolute Cell Referencing

To create absolute cell referencing, “$” sign is used in front of Column name and row number. For example, absolute reference for cell A3 will be $A$3. In the below picture, reference to cell C2 is absolute cell reference. It is used when we want to fix a particular value for every row.

Now, if we drag down the fill handle of cell D2, the reference to cell C2 will always be fixed because it is absolute cell reference.

III. Mixed cell referencing

A mixed reference is a combination of relative and absolute cell reference. In this, any one of the row or column is fixed which is absolute reference and other is not fixed which is relative reference. The example of mixed reference can be “$A2“. It means Column A is fixed but row is not fixed. Let’s take an example of Mixed cell reference:

For example, to multiply a column of numbers (column A) by 3 different numbers (B2, C2 and D2), you put the following formula in B3, and then drag it down and to the right:
=$A3*B$2

IV. Circular Cell reference

A circular cell reference is one that refers to its own cell directly or indirectly. For example, if you put the below formula in cell A1, this would create a circular reference:
=A1+100
In most cases, Circular references create problems so we should avoid using them wherever possible.

You can also refer official document of MS Excel at Microsoft support link.
Find more blogs on MS Excel here.