Changing an absolute cell reference to a relative reference in Microsoft Excel

Cell references in Excel are very important. In this article we will learn difference between absolute cell reference & relative cell reference in excel & then learn how to change an absolute reference to a relative reference or vice versa in Microsoft Excel 2010. When a formula is copied, a Relative reference is used. Excel Relative reference is the distance in rows and columns between the reference and the cell that contains the formula.

img1

We use the excel absolute reference shortcut F4 key.

Let’s take an example to understand how we can change the relative cell reference from absolute cell reference and  absolute to relative.

We have data in the range A4:F9 in which column A contains subjects and range B4:F9 contains the score of the students. Cell A1 contains the total marks. We need to calculate the percentage of students’ scores.

img2

In this case, we define the cell A1 as absolute reference.

Absolute reference to the column and row and Relative reference to the Column and Row

Follow below given steps:-

  • Write the formula for returning the %age of achievement.
  • Select the cell B11.
  • =B10/$A$1 (%age of Achievement = obtained marks/total marks).
  • In this example, A1 is our absolute reference for all the students.
  • And B11 is our relative reference.
  • By pressing the key F4, we make it (A1) absolute reference.
  • When we copy the same formula for returning the percentage of all the students, that time all relative reference will get changed but absolute reference ($A$1) will never change.

img3

This is the way by which we can use the Absolute references and relative references in our formulae by using the key F4 in Microsoft Excel 2010 and 2013.

Users are saying about us...

  1. Can you discuss how a formula is written using real relative references, such as the R1C1 designations. Using =AVERAGE(M$90:M$100) will not calculate the average of the last ten rows if the user inserts new data rows between row 90 and 10>

  2. Can you discuss how a formula is written using real relative references, such as the R1C1 designations. Using =AVERAGE(M$90:M$100) will not calculate the average of the last ten rows if the user inserts new data rows between row 90 and 10>

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube