EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Relative Reference in Excel

By Manisha SharmaManisha Sharma

Home » Excel » Blog » Excel Tips » Relative Reference in Excel

Relative Reference in Excel

Relative Reference in Excel (Table of Contents)

  • Relative Reference in Excel
  • How to Use Relative Reference in Excel?

Relative Reference in Excel

Relative Reference in Excel is like selecting a cell without putting anything in it. By this, the cell value will not be fixed, and whenever we are copying or using that cell, its value will also get changed with the respective reference of that sheet. For example, the relative reference of cell A1 will look like “=A1”. This reference will get changed when we have copied it to other cells or sheets.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

What is the Relative Reference in Excel?

Relative references refer to a cell or a range of cells in excel. Every time a value is entered into a formula, such as SUMIFS, it is possible to input into Excel a “cell reference” as a substitute for a hard-coded number. A cell reference may come in the form B2, where B corresponds to the tcell cell column letter in question and 2 represents the row number. Whenever Excel comes across a cell reference, it visits the particular cell, extracts out its value, and uses that value in whichever formula that you’re writing. When this cell reference is duplicated to a different location, the cell’s relative references correspondingly also changes automatically.

When we reference cells like this, we can achieve it with any of the two “reference types”: absolute and relative. The demarcation between these two distinct reference types is the different inherent behavior when your drag or copy and paste them to different cells. Relative references can alter themselves and adjust as you copy and paste them; absolute references, contrarily, do not. In order to successfully achieve results in Excel, it is critical to be able to use relative and absolute references in the right way.

How to Use Relative Reference in Excel?

This Relative Reference is very simple easy to use. Let us now see how to use the Relative Reference in Excel with the help of some examples.

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

Example #1

Let us consider a simple example to explain the mechanics of Relative Reference in Excel. If we wish to have the sum of two numbers in two different cells – A1 and A2, and have the result in a third cell A3.

Example 1

So we apply the formula A1+A2, which would yield the result as 200 in A3.

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (10,985 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (15 Courses, 8+ Projects)Excel for Finance Training (18 Courses, 7+ Projects)

Relative Reference Example 1-1

The result is 200.

Relative Reference Example 1-2

Now suppose, we have a similar scenario in the next column (“B”). Cell B1 and B2 have two numbers, and we wish to have the sum in B3.

Relative Reference Example 1-3

We can achieve this in two different ways:

Here we physically write the formula to add the two cells B1 and B2 in B3 to get the result of 30.

Relative Reference Example 1-4

The result is 30.

Relative Reference Example 1-5

Or we could simply copy the formula from cell A3 and paste it into cell B3 (it would work if we drag the formula from A3 to B3 also).

Relative Reference Example 1-6

Relative Reference Example 1-7

So, when we copy the contents of cell A3 and paste in B3 or drag the contents of cell A3 and paste in B3, the formula gets copied, not the result. We could achieve the same result by right-clicking on cell A3 and use the Copy option.

Copy option

And after that, we move to the next cell, B3 and right-click and select “Formulas (f)”.

Formulas (f)

Result of Example 1

What this means is that cell A3 = A1+A2. When we copy A3 and move one cell to the right and paste it onto cell B3, the formula automatically adapts itself and changes to become B3 = B1+B2. It applies the summation formula for B1 and B2 cells instead.

Example #2

Now, let us look at yet another practical scenario which would make the concept quite clearly. Let us assume that we have a data  co consisting Unit Price of a product and the quantity sold for each of them. Now our objective is to calculate the Sale Price; with the following formula can describe price = Unit Price x Units Sold.

Example 2

To find the Sale Price, we need to now multiply Unit Price with Units Sold for each product. So, we shall now proceed to apply this formula for the first cell in Sale Price, i.e. for Product 1.

Relative Reference Example 2-1

When we apply the formula, we get the following result for Product 1:

Relative Reference Example 2-2

It successfully multiplied the Unit Cost by the Units Sold for Product 1, i.e. cell G2 * cell H2, i.e. 1826.00 * 20, which gives us the result 36520.00.

So now we see that we have 9 other products to go. In real case scenarios, this could go up to hundreds or thousands of rows. It becomes difficult to nearly impossible to go about writing the formula for each row simply.

Hence, we will use the Relative Reference feature of Excel and simply copy the contents of cell I2 and paste in all of the remaining cells in the table for the column Sale Price or simply drag the formula from cell I2 to the rest of the rows in that column and get the results for the whole table in less than 5 seconds.

Relative Reference Example 2-3

Result of Example 2

We can either press Ctrl + D or simply Copy and Paste the cell I2 to all the selected cells.

Things to Remember

  • While copying the Excel formulae, relative referencing is generally what is desired. This is the reason why this is the default behavior of Excel. But sometimes, the objective might be to apply absolute reference rather than relative reference. Absolute Reference is making a cell reference fixed to an absolute cell address, due to which, when the formula is copied, it remains unaltered.
  • Absolutely no dollar signs are required! With Relative referencing, when we copy the formula from one place to others, the formula will adapt accordingly. So, if we type =B1+B2 into the cell B3 and then drag or copy-paste the same formula into the cell C3, a relative reference would automatically adjust the formula to =C1+C2.
  • With Relative referencing, the referred cells automatically adjust themselves in the formula as per your movement, either to the right, left, upward or downwards.
  • If we were to give a reference to cell D10 and then shift one cell downwards, it would change to D11; if instead, we shift one cell upwards, it would change to D9. If we shift one cell to the right, the reference will change to E10, and instead, if we shift one the cell to the left, the reference would automatically adjust itself to C10.

Recommended Articles

This has been a guide to a Relative Reference in Excel. Here we discuss its uses and how to use Relative Reference in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in Excel –

  1. Cell References in Excel
  2. 3D Cell Reference in Excel
  3. Mixed Reference in Excel
  4. Excel Circular Reference

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

7 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (58+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

EDUCBA

Download Relative Reference Excel Template

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - EXCEL COURSE Learn More