Cell References in Excel (Table of Contents)
Introduction to Cell References in Excel
All of you would have seen the $ sign in Excel formulas and Functions. The $ sign confuses a lot of people but it is very easy to understand and use. The $ sign serves only one purpose in excel formula. It tells excel whether or not to change the cell reference when the excel formula is copied or moved to another cell.
When writing a cell reference for a single cell, we can use any type of cell reference but when we want to copy the cell to some other cells, it becomes important to use the correct cell references.
What is Cell Reference?
A cell reference is nothing but the Address of the cell which is used in the excel formula. In Excel, there are two types of cell references. One is Absolute reference and the other is Relative reference.
What is Relative Cell Reference?
The cell reference without a $ sign will change every time it is copied to another cell or moved to another cell and it is known as Relative cell reference.
What is an Absolute Cell Reference?
The cell references in which there is a $ sign before the Row or Column coordinates are Absolute references. In excel we can refer one and the same cell in four different ways, for example, A1, $A$1, $A1, and A$1. We will look at each type with examples in this article.
How to Apply Cell Reference in Excel?
Applying Cell References in Excel is very simple and easy. Let’s understand how to reference cell in Excel with some examples.
When a formula with relative cell reference is copied to another cell, the cell references in the formula changes based on the position of row and columns.
Example #1 – Excel Relative Cell Reference (without $ sign)
Suppose you have sales details for the month of January as given in the below screenshot.
There is Quantity sold in column C and Rate per KG in Column D. So to arrive at Total Amount you will insert the formula in Cell E2 = C2*D2.
After inserting the formula in E2, press the Enter key.
You will need to copy this formula in another row with the same column say, E2, it will automatically change the cell reference from A1 to A2. Because Excel assumes that you are multiplying the value in column C with the value in Column D.
Now drag the same formula in cell E2 to E17.
So as you can see when using the relative cell reference, you can move the formula in a cell to another cell and the cell reference will change automatically.
Example #2 – Excel Relative Cell Reference (Without $ Sign)
As we already know that Absolute cell reference is a cell address with a $ sign in a row or column co-ordinates. The $ sign locks the cell so that when you copy the formula to another cell, the cell reference doesn’t change. So using $ in cell reference allows you to copy the formula without changing cell reference.
Suppose in the above example Rate per KG is given only in one cell as shown in the below screenshot. The Rate per KG is given only in one cell instead of providing in each line.
So when we insert the formula in cell D2, we need to make sure that we lock the cell H2 which is the Rate Per KG for Apple. Formula to enter in cell D2 =$G$2*C2.
After applying the above formula output is as shown below.
Now when you copy the formula to the next row say cell D3. The cell reference for G2 will not change as we locked the cell reference with a $ sign. The cell reference for C2 will change to C3 as we have not locked the cell reference for Column C.
So now you can copy the formula to the below rows till the end of data.
As you can see, when you lock the cell in cell reference in a formula, no matter where you copy or move the formula in excel, the cell reference in the formula remains the same. In the above formula, we saw the case where we lock an entire cell H2. Now there can be two more scenarios where we can use absolute reference in a better way.
- Lock the row – Refer Example 3 below
- Lock the column – Refer Example 4 below
As we already know in the cell reference, the columns are represented by words and rows are represented by numbers. In the absolute cell reference, we have the option either to lock the row or column.
Example #3 – Copying the Formula
We will take a similar example of Example 2.
After applying above formula output shown below.
In this case, we are only locking the row 2 so when you copy the formula to the below row the row reference will not change as well as column reference will not change.
But when you copy the formula to the right, the column reference of H will change to I keeping row 2 as locked.
After applying the above formula output is shown as below.
Example #4 – Locking the Column
We will take a similar example of Example 2 but now we have the rate per KG for an apple in each line of Column G.
After applying the above formula output is shown below.
In this case, we are only locking column H so when you copy the formula to the below row the row reference will change but column reference will not change.
But when you copy the formula to the right, the column reference of H will not change and row reference of 2 will also not change but the reference of C2 will change to D2 because it is not locked at all.
After applying the above formula output is shown below.
Things to Remember About Cell Reference in Excel
- The key which helps in inserting a $ sign in the formula is F4. When you press F4 once it locks the entire cell, when you press twice it locks the row only and when you press F4 thrice it locks the column only.
- There is one more reference style in excel which refers to cell as R1C1 where both rows and columns are identified by numbers.
- Don’t use too many row/column references in excel worksheet as it may slow down your computer.
- We can also use a mix of Absolute and Relative cell reference in one formula depending on the situation.
Recommended Articles
This is a guide to Cell Reference in Excel. Here we discuss how to use Cell Reference in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
