Cell References in Excel – Relative, Absolute and Mixed

Understanding cell reference 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 that needs to be done. To begin with cell references, we need to understand what a cell is and the notation/ language used in excel to identify a cell. Read more about the terminology used in Excel.

What Is a Cell Reference

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 of a work sheet, range in a worksheet, a cell/ range in a different worksheet or even to a cell/ range in a different workbook.

To create a cell reference,

  1. Select the cell and place an = or + sign and select the cell you want to refer to.
  2. Select the cell and place an = or + sign and type the cell name which you want to refer. (Ex: A2, b2 etc.)

For example

  1. Using =C2 or +C2 in a cell will reflect the value present in cell C2 in the current cell.
  2. Similarly =C2+D2 returns the sum of values in cells in C2 and D2 in the current cell. The same can be used with other operators like – , * and / as well.

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

Cell references in Excel

Excel provides two types of cell references. Relative and Absolute.

Relative Reference: A reference which changes upon copying to a different cell
using 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 References come in handy when doing calculations or operations 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 needs to pay by multiplying the number of items with unit price of respective item.

References In Excel

Absolute Reference: There might be situations in which the reference should not change upon copying to a different cell
using the autofill/ Manual copy. In these cases absolute reference come in handy. Absolute reference is used to keep a row/ column constant.

To make a Cell reference Absolute you need to use the $ sign. It can be either for a column/ row or both. See below example to understand Absolute references.

  • $A$2 – Keeps both Column and Row constant
  • $A2 – Keeps the Column Constant. (Also termed as Mixed Reference)
  • A$2 – Keeps the row constant. (Also termed as Mixed Reference)

Shortcut: Use F4 key, To Switch between Absolute and Relative References

See the below image to understand Absolute reference. Continuation to the example above, we need to calculate the total price a customer needs to pay by multiplying the number of items with unit price of respective item along with the discount and sales tax (which are constant % ages ) to be applied for all items.

References in Excel

Mixed Reference: If either or the row or column are made constant. See case 2 and 3 in the above example.

Circular Reference: When a formula refers to the same cell. These are generally iterative in nature which excel doesn’t support (while using formulae). 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.



Download the Practice Workbook used in Example

Challenge: 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

Do share this post with your friends if you find it useful.


5 Replies to “Cell References in Excel – Relative, Absolute and Mixed”

  1. […] to give a cell reference or formula in IF […]

  2. […] the cell reference value changes while using the auto fill unless it is set to absolute reference. (Know more about references). The formula can also be written […]

  3. […] The arguments of count function are Value1,Value2,… These can be values, alphabets, symbols, special characters, space or array of values or referring to a range of cells (Cell References). [Learn more about Cell References] […]

  4. […] : The Value to look/search for. This can be a Text/ Number/ Date/ Value or a Cell Reference. Output of/ value returned by another function can also be used as […]

  5. […] : This can be a cell reference, text/ string or even both which is used to create a reference to a cell/ range. The range which is being referenced can again be a cell/ text/ range or Named Range (Read this […]

Leave a Reply