Cell Referencing in Excel

In this tutorial, we are going to discuss what is Cell Referencing in Excel and as well as types of Cell References in Excel with examples.

If you understand the difference between relative, absolute, and mixed references, it is easy to deal with Excel formulas.

Types of Cell References in Excel

There are three types of cell references that you can use in Excel.

  • Relative Cell References.
  • Absolute Cell References.
  • Mixed Cell References.

Now we are going to discuss all Cell References in Excel with examples.

Relative Reference

A relative reference in Excel is a pointer to the cell range or cell. which means it pointing the relationships of the cells.

For example, in the below example I am doing the multiplication of the two cells in cell D2. that is C2 * B2.

That means, Cell D2 references cell C2 and cell B2. both references are relative.

Relative Cell Reference Example

If I drag the formula till D5, then cell D3 references cell C3 and cell B3. in the same way cell D5 references cell C5 and cell B5.

Relative Cell Reference

Absolute Reference

An absolute reference in Excel remains constant even after dragging the formula also.

For example, in the below example we are giving a Rs10 (cell H1) discount on total value.

Absolute Reference in Excel

To create an absolute reference to cell H1 we have added the $ symbol on cell E2.

If I drag the formula till E5 also, cell H1 is fixed. As a result, the discounts are calculated for each product correctly.

Result is,

Absolute Reference Example

Mixed Reference

The name mixed itself saying that, it is the mixture of both An absolute reference and relative reference in Excel. Sometimes we require a combination of both relative and absolute reference (mixed reference).

Mixed Reference in Excel

In the above example, we are multiplying Qty and MRP (B5 * C5) using relative reference and also gave a discount of Rs10 (G1) on each fruit using absolute reference.

This is all about mixed references in Excel.


Get Your FREE Excel Shortcut Keys e-BOOK