Relative Reference & Absolute Reference & How to Change Relative Refence to Absolute Refence in Excel

In excel worksheet, there are relative reference and absolute reference two cells set up types. Normally, relative refence is set by default. If you are not very clear about the concepts or behavior of relative reference and absolute reference, we will use below examples to illustrate them clearly and simply.

Relative Reference


Case 1:

In daily work we often use formula to do calculation, and then drag the fill handle to fill other cells which want to copy the formula as well. For example, see below table, in E2 we enter the formula =B2*C2, then drag it (with formula) to fill E3 and E4, we found that in E3 the formula is automatically changed as =B3*C3.

Relative Reference & Absolute Reference 1

Relative Reference & Absolute Reference 2

If we move focus on E4, we may find in E4, we may find in E4 the formula is automatically updated as =B4*C4. So, in this case, Bx*Cx can be seen as relative reference. The formula is calculated properly based on the reference; it is not a fixed reference as B2*C2.

Case 2:

In above case1, the relative reference is updated properly in formula and we can get the correct result as we want. But in some cases, relative reference will cause wrong result. We use LOOKUP function here to do a demonstration.

In below table, we want to get Score from table E1:H11 for person lists in A column, and record the returned value in B column accordingly, obviously we can use VLOOKUP function here.

Relative Reference & Absolute Reference 3

So, enter =VLOOKUP(A2,F2:H11,3,FALSE) in B2 to get result.

Relative Reference & Absolute Reference 4

We can get the correct result A in B2 properly.

Relative Reference & Absolute Reference 5

Drag the fill handle to B3:B6. Noticed that we get some wrong results.

Relative Reference & Absolute Reference 6

Click B2 to check the formula. Noticed that reference (for table_array) is updated to F3:H12 automatically as relative reference is set up for cells by default. In this case, Cathy still can be found in new reference, so it can return the correct score. But for Calvin, it is not included in its relative reference, so we get improper result here.

Relative Reference & Absolute Reference 7

Relative Reference & Absolute Reference 8

Above all, we need to keep table_array with a fixed value, so we need to know what is absolute reference here.

Absolute Reference


In above case2, if we keep table_array with fixed value E2:H11, then the result will be calculated correctly in column B.

Relative Reference & Absolute Reference 9

So, we can confirm that F2:H11 is absolute reference for VLOOKUP function in this case.

Relative Reference & Absolute Reference 10

Change Relative Reference to Absolute Reference in Worksheet


Because relative reference is default set up in worksheet, so we just need to know how to change relative reference to absolute reference. Obviously, if we copy a cell with formula to another cell, relative reference will update automatically, if we want to use absolute reference, we have to change reference manually, it is quite troublesome. Actually, we can add $ before relative reference to change it to absolute reference, and there are two ways to implement this function, see details below.

Method 1: Add $ Manually

We still use case 2 table to do demonstration.

Step 1: In B2, use VLOOKUP function to search the mapping result for A2. For VLOOKUP each parameter, we can select A2 as lookup_value, select range F2:H11 as table_array directly, then enter col_index_num (in this case Score is the third column in table, so we enter 3) and select False, then click Enter to complete formula with VLOOKUP function.

Relative Reference & Absolute Reference 11

Step 2: Add $ before each character of table_array.

Relative Reference & Absolute Reference 12

Step 3: Drag this fill handle to fill other cells this time. Verify that $ is copied with formula to other cells as well, and F2:H11 is not changed anymore in formula after adding $.

Relative Reference & Absolute Reference 13

Method 2: Add $ by Press F4

Step 1: Repeat above step#1, but this time, before clicking Enter to confirm formula, click F4 directly. You may find that $ is auto added before F2:H11 each character properly.

Relative Reference & Absolute Reference 14

Change Relative Reference to Absolute Reference in Different Cases


Case 1: Add $ before Row and Number

See table below. If we want to record B2*C2, B2*C3, B2*C4 values in E2:E4 accordingly, we may add $ before B and 2 to keep B2 unchanged in formula.

Relative Reference & Absolute Reference 15

Copy the cell to E3 and E4. Verify that values are calculated properly.

x
How to Split Date into Day, Month and Year in Excel

Relative Reference & Absolute Reference 16

Case 2: Add $ before Column and Number

If we want to record B2*C2, B3*C2, B4*C2 values in E2:E4 accordingly, we may add $ before C and 2 to keep C2 unchanged in formula.

Relative Reference & Absolute Reference 17

Copy the cell to E3 and E4. Verify that values are calculated properly.

Relative Reference & Absolute Reference 18

By the way, you can also add $ only before row/column depends on your requirement anyway, for example $B2:C2, B$2:C2, $B2:C$2 etc.

 

 

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar