Updated August 22, 2023
Cell References in Excel (Table of Contents)
Introduction to Cell References in Excel
You would have seen the $ sign in Excel formulas and Functions. The $ sign confuses many people but is easy to understand and use. The $ sign serves only one purpose in the 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 the cell address used in the Excel formula. In Excel, there are two types of cell references. One is an Absolute reference, and the other is a Relative reference.
What is Relative Cell Reference?
The cell reference without a $ sign will change every time it is copied or moved to another cell, known as a Relative cell reference.
What is an Absolute Cell Reference?
The cell references with a $ sign before the Row or Column coordinates are Absolute references. In Excel, we can refer to 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 cells in Excel with some examples.
When a formula with relative cell reference is copied to another cell, the cell references in the formula change based on the position of rows and columns.
Example #1 – Excel Relative Cell Reference (without $ sign)
Suppose you have sales details for January, as given in the screenshot below.
There is Quantity sold in column C and Rate per KG in Column D. So to arrive at the 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, 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)
The absolute cell reference is a cell address with a $ sign in a row or column coordinates. 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, the Rate per KG is given only in one cell, as shown in the below screenshot. Thus, 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 ensure that we lock cell H2, which is the Rate Per KG for Apple. So the formula to enter in cell D2 =$G$2*C2.
After applying the above formula, the output is shown below.
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. However, 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 the 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 remains the same. In the above formula, we saw the case where we locked an entire cell, H2. Now there can be two more scenarios where we can use absolute reference better.
- Lock the row – Refer to Example 3 below
- Lock the column – Refer to Example 4 below
As we already know in the cell reference, the columns are represented by words, and numbers represent rows. In the absolute cell reference, we have the option to either lock the row or column.
Example #3 – Copying the Formula
We will take a similar example of Example 2.
After applying the above formula, the output is shown below.
In this case, we are only locking row 2, so when you copy the formula to the below row, the row reference and 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 locked.
After applying the above formula, the output is shown 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, the 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 the column reference will not.
But when you copy the formula to the right, the column reference of H will not change, and the 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, the output is shown below.
Things to Remember About Cell Reference in Excel
- The key that helps insert 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.
- One more reference style in Excel refers to a cell as R1C1, where numbers identify both rows and columns.
- Don’t use too many row/column references in the Excel worksheet, as it may slow down your computer.
- Depending on the situation, we can also use a mix of Absolute and Relative cell references in one formula.
Recommended Articles
This is a guide to Cell Reference in Excel. Here we discuss how to use Cell Reference in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
