WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Relative References in Excel

Relative References in Excel

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)

You can download this Relative References Excel Template here – Relative References Excel Template

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.

Example 1

So, you applied A1+ A2 that should be equal to 100.

Example 1-1

Now, you have different values in the cells B1 & B2 and do a similar summation in the cell B3.

Example 1-2

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1-3

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.

Relative reference in excel example 2

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.

Relative reference in excel example 2-1

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.

Relative reference in excel example 2-2

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Relative References Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More