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.



Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...