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.

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.

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.

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.