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.

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.

 

 

You might also like:

Sidebar