Different Types of Cell References in Excel (With Examples)

While working with Excel formulas or functions, we often need to use the value of one cell to another cell. Using the value of one cell into another one can be done in multiple ways. In this blog post, you are going to learn about how to use one cell value to another one and also how many ways this can be done in Excel.

Download the Practice Workbook

In this practice workbook, we tried to calculate the monthly savings of 10 employees. Using this workbook, we’ve tried to demonstrate different types of cell references in Excel. You are recommended to download this workbook and practice along with it.

What is Cell Reference in Excel?

Cell Reference refers to the usage of one cell value or property to a different cell while performing various operations in Excel.

For example, we want to see the differences between two values located at cell C5 and D5 respectively. To do so, we need to keep the result in another cell. Let’s call it E5. So, all we need to do is, type =C5-D5 within cell E5. So, we are using the values of cells C5 and D5 within cell E5. This phenomenon of using one cell value to another is called Cell Reference.

cell reference in excel

Cell Address Structure

Every single cell in Excel can be uniquely identified using Cell Addresses. Cell addresses consist of one or more letters followed by a number where the letter(s) indicate(s) the column and the number(s) represent(s) the row.

cell address structure in excel

Different Types of Cell References in Excel

There are 3 types of cell references in Excel. Those are:

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

1. How to Use Relative Cell References

These types are the default type of cell reference in Excel. Both the column and row numbers can be varied in this particular type of cell reference. We will calculate the monthly savings in this section to demonstrate the usage of a relative cell reference. Here’s how to use this particular type in Excel:

Here,

Savings = Net Salary – Expenditure
  • Select E5 to locate the result value.
  • Type =C5-D5.
  • Press ENTER.

Now you will get the monthly savings for George.

While calculating the monthly savings for George, we’ve retrieved the values of cells C5 and D5. If you drag down the Fill Handle to the end of the table, you will get the savings for the rest of them.

The cell addresses continue to vary sequentially as we go down. This means that cell addresses can be varied either by the column number or the row number. Thus, this type of cell reference is called Relative Cell Reference.

relative cell reference in excel

2. How to Use Absolute Cell Reference

Absolute Cell Reference is a particular type of cell reference where the cell address of a particular cell is locked up and its value doesn’t change regardless of the cell location. Dollar Sign ($) is used before the column and row number of a cell address to lock it up.

In this section, we will try to calculate the Net Salary which can be done as follows:

Net Salary = Gross Salary – (Gross Salary*Income Tax) + Bonus

Here, the Bonus remains the same for all the employees. So, we will lock up this cell. To do so, we’ve used Dollar Sign ($) before the column number as well as the row number. Which is $E$5.

absolute cell reference in excel

3. How to Use Mixed Cell Reference

Mixed Cell Reference is a combination of both the relative as well as absolute cell references. In this case, either column or row; one of them will be absolute and the other will be relative.

For this instance, the formula to calculate the Net Salary is:

Net Salary = Gross Salary – (Gross Salary*Income Tax) + Bonus

Which in terms of a cell address is:

=C5-(C5*$D5)+$E$5

Here the address of the Income Tax column is written as $D5. Where the Dollar Sign ($) is put only before the column value i.e. $D which locks up the column value means it is absolute now. But there’s no such sign before 5 which means it’s still relative. Thus, the combination of both the absolute and relative cell references makes this cell reference a Mixed Cell Reference.

mixed cell reference in excel

Wrapping Things Up

  • Relative Cell Reference: No Dollar Sign ($) e.g. D5
  • Absolute Cell Reference: Two Dollar Signs ($) e.g. $D$5
  • Mixed Cell Reference: One Dollar Sign ($) e.g. $D5 or D$5

Conclusion

Cell Reference is a vital feature that is used extensively in Excel either with formulas or functions or charts or various other commands. So, the conception regarding different types of cell references in Excel is a must to understand. Special care should be taken care of while using any of these reference types to avoid errors.

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo