QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to use Relative and Absolute Cell Reference in Excel?

By Neha T Leave a Comment

Relative and absolute cell reference are the two ways to refer to a cell in excel. Both of them function differently when they are copied to the other cells of the spreadsheet.

In this context, we will discuss both relative and absolute references. We will learn, how to use them with the help of examples.

Relative Cell Reference

The relative cell reference is the one that changes as you copy other cells. Whenever you copy a relative reference to the other cell it changes according to the relative position of the rows and columns.

The relative reference of the cells is most commonly used when you have to apply the same formula across multiple rows and columns.

Absolute Cell Reference

The absolute cell reference does not change when you copy them to other cells. When you copy an absolute cell reference to another cell, the corresponding row and column of the cell remain constant.

To specify that we have referred a cell in an absolute reference mode then we have to apply a dollar sign before the row and column of the referred cell.

Steps to use Relative and Absolute Cell Reference in Excel:

Use Relative Cell Reference

We write a formula using the relative cell reference in cell C1.

For Example:

=A1+B1

Now if we copy fill this formula to cells C2, C3, C4, C5.

The formula changes accordingly:

=A2+B2   … in cell C2

=A3+B3   … in cell C3

=A4+B4   … in cell C4

=A5+B5   … in cell C5

Now let us discuss an example that we have listed some fruits in column C i.e., from C4 to C8. We have the quantity of the corresponding fruit in column D i.e., from D4 to D8. And we have the rate or price of the corresponding fruits in column E i.e., from E4 to E8.

Use relative and absolute cell reference-01

To calculate the total amount of each fruit we have to create a formula. So, we will create the formula for the first fruit that is orange in cell F4.

Use relative and absolute cell reference-02

Now just press Enter to get the result of this formula. Further, we want the total amount of the remaining fruit. So, we need to fill or copy the same formula over the desired cells.

Use relative and absolute cell reference-03

For this locate the fill handle at the lower right corner of the cell and click-drag it down up to the desired number f cell in this case up to F8.

Use relative and absolute cell reference-04

The moment you release the mouse the formula is copied to the selected cells and the result is calculated automatically.

Use relative and absolute cell reference-05

Observe that the formula has been copied to the other cells with relative references.

Use relative and absolute cell reference-06

 

The relative references of cells in the formula change as the row changes.

Use Absolute Cell Reference

When you copy a cell containing the absolute references, the references do not change where you copy the cell. To denote absolute reference, we have to specify the dollar sign ‘$’ in front of the row and column of the cell.

In case the ‘$’ sign precedes both rows as well a column we refer to its as mixed references.

  • $A$2 -> This indicates that column A and row 2 will remain constant.
  • $A2 -> This indicates column A will remain constant but row 2 can change.
  • A$2 -> This indicates column A will change but row 2 will remain constant.

Consider the above example and now we have to compute the sales tax on each fruit item. Let us say there 2.4% of sales tax on each fruit item. We have placed this sales tax rate in cell D11.

Now we will create a formula to calculate sales tax on orange in cell G4.

absolute cell reference-07

To calculate the sales tax on the rest of the fruits we can click-drag the formula up to G8.

absolute cell reference-08

Notice that as we have doesn’t mention the absolute reference of cell D11. It got changed at each cell where we have copied it.

absolute cell reference-09

This leads to an incorrect result.

absolute cell reference-10

To rectify this, we will now mention the absolute reference of cell D11 i.e., $D$11.

absolute cell reference-11

This time when we copy the formula to other cells the reference $D$11 doesn’t change. And we get the correct result.

So in this way, we can use the relative and absolute cell reference in excel.

You Might Also Like:

Index and Match - featured imageHow to Use INDEX and MATCH Functions in Excel? Create a named range in Excel feature imageHow to Create Named Range in Excel? Subtract feature image 1How to Subtract in Excel? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? how-to-split-text-of-cells-in-ms-excel-2016-featured-imageHow to Split Text in a Cell in MS Excel 2016? delete blank row feature imageHow to Delete Blank Rows in Excel 2016? count unique values featured imageHow to Count Unique Values in Excel 2016? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to shortlist your snapdeal products
  • How to use Flash Fill in MS Excel 2016?
  • How to Kill a Process in Windows 7
  • How to Make Bar Graph in Excel?
  • How to Schedule a Zoom Meeting?

Copyright © 2023 · QueHow · Contact Us