What are Relative Cell References in Excel?
Relative references is one of the types of cell reference in excel, it is the type of reference which changes when the same formula is copied to any other cells or in any other worksheet, suppose in cell A1 we have =B1+C1 and when we copy this formula to cell B2 the formula becomes C2+D2, why because in the first formula the cells were referred to the two right cells of cell A1 while in the second formula the two cells on the right are c2 and d2.
How to use Relative References in Excel? (with Examples)
Example #1
Let me take a simple example to explain the concept of relative cell references in Excel. Assume you want to perform the summation of two numbers that are located in cells A1 & A2, and you want to do the sum in the cell A2.
So, you applied A1+ A2 that should be equal to 100.
Now, you have different values in the cells B1 & B2 and do a similar summation in the cell B3.
We can do the summation in 2 ways. One is once again by applying the excel formula in the cell B3, or you can copy-paste the A3 formula to B3.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
You must be thinking when you copy the cell A3 and paste in B3; you should get 100. However, the cell A3 contains the formula, not the value. That means the A3 cell depends on other cells A1 & A2. When you copy the A3 cell and move one cell to the right, A1 becomes B1 and A2 becomes B2, so the cell B3 takes the values of B1 and B2 and applies the sum of those two numbers.
Example #2
Now, look at the one more example of Relative references. Assume you have data, which includes unit price and unit quantity sold for each product, and you want to do the calculation of Unit Price * Unit Cost = Sale Price.
To calculate the sales price for each product, we need to multiply Unit Sold with unit Price i.e., B2*C2 and similarly for all the products. Now go ahead and apply the formula.
The above formula should give us the sales amount for the product 1. We have a total of ten products here, and we cannot apply the same formula every time. Instead, we can just copy-paste the formula to other cells.
As you copy and paste the formula from cell D2 to cell D3, the formula reference also changes from B2*C2 to B3*C3 and so on. Probably it would have taken 1 minute to apply to all the ten products, but copy-paste or dragging the formula would take hardly 5 seconds of your time.
Either press Ctrl + D or Copy and Paste the cell D2 to all the selected cells.
Things to Remember
- When copying formulas in Excel, relative addressing is usually what you want. That is why it is the default behavior. Sometimes you do not want relative addressing but rather absolute addressing. This is making a cell reference fixed to an absolute cell address so that it does not change when the formula is copied.
- No dollar signs at all! If you copy this bad boy from place to place, the formula will move with it. So if you type =A1+A2 into cell A3, then copy and paste that formula into cell B3, it will automatically change the formula to =B1+B2.
- In Relative reference, each referred cell keeps changing along with the cells you move either left, right, downward, and upward.
- If you give reference to cell C10 and move to one cell down, it changes to C11; if you move one cell upward, it changes to C9; if you move one cell to the right, it changes to D10; if you move on the cell to the left, then it changes to B10.
Recommended Articles
This has been a guide to Relative References in Excel. Here we discuss how to use Relative Cell References in Excel along with the example and downloadable excel templates. You may also look at these useful functions in excel –
- Use 3D Reference in Excel
- ISNUMBER in Excel
- TRANSPOSE Excel Function
- Char Function in Excel
- CSV Files into Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
