How to use Cell References in Excel – Relative, Absolute and Mixed

Understanding Cell References in Excel is one of the important things to master excel and make the most out of spreadsheets. Cell References combined with auto fill feature of excel help you to reduce/ eliminate the dirty manual work. To begin with cell references in excel, we need to understand what a cell is and the notation/ language used in excel to identify a cell.

Suggested Reading : Know about the terminology used in Excel.

What Is Cell Reference in Excel

A cell reference refers to a cell/ range of cells that refers/ tells excel where to look for the data. In short cell reference means the cell to which another cell refers to or points to. Cell references can be in a particular cell or range in same worksheet, a cell/ range in a different worksheet or even to a cell/ range in a different workbook.

How to Create Cell References In Excel

There are multiple ways to create a cell reference in excel. The mostly used ones are mentioned below.

• Select the cell and place an = or + sign and click (select) on the cell you want to refer to.
• Select the cell and place an = or + sign and type the cell name which you want to refer.

See the below image to understand how to refer to different cells.

Types of Cell References In Excel

There are two types of Cell References in Excel. Relative and Absolute.

Relative Reference in Excel

A reference which changes upon copying to a different cellusing the autofill/ Manual copy is known as relative reference.

For Example: if you have a formula in Cell D1 as “=B1+C1”, upon copying it to row 2 i.e Cell D2 it changes to “=B2+C2”

By default all references in excel are Relative. Relative Reference come in handy when doing calculations that are dynamic in nature. Especially when you need to repeat same calculation across multiple rows and columns.

See the below image to understand relative reference. The below example shows the total price a customer need to pay by multiplying the number of items with unit price of respective item.

Absolute Reference in Excel

Absolute reference means keeping the row and column constant. This can be termed as opposite to relative reference.

Unlike relative reference, Using absolute reference makes no impact on copying the formula to other cells in excel.

How to create Absolute Reference In Excel

To make a Cell reference Absolute you need to use the $ sign before the column and row number / name.

A simple example for an absolute reference – $A$2 – Both Column A and Row 2 remain constant.

Mixed Reference In Excel

As the name says, Mixed Reference is a combination of both relative and absolute reference. In Mixed Reference either row or cloumn remain constant.

See the below Examples for Mixed Reference. Both the examples are for mixed reference, however there is a small difference.

• $A2 – Keeps the Column A Constant.
• A$2 – Keeps the 2nd row constant.

See the below image to understand Absolute and Mixed reference. Let us now calculate total price a customer need to pay after discuont and tax using a combination of both absolute and mixed references.

Circular Reference

Incase if a formula is referring to the same cell then it is known as circular reference. These are generally iterative in nature which excel do not support (while using formulas).

A warning message pops up when circular references are used in a formula. Excels also tells you the same on the status bar as well. See the below images.

The Challenge

Now, we have seen and understood different cell references in excel, let us try to solve a probelm.

Download the Practice/ Sample workbook used in the above example. Challenge Problem is also included in the sample file.

Using a Single Formula Create a Multiplication table (1 to 10) using Relative and Absolute References as shown in the Sheet 3 (Challenge) of Practice Workbook. Give your Answers in the comment section below.

Do not forget to share this post with your friends.