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

Calculate Years Between Dates In Ms Excel

If you are an avid Ms Excel user, then you might have come across a task in which you needed to calculate the years between the dates; you might take it easy and do this task manually, which is also ...

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you're producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time ...

Calculate Loan Interest in Given Year

When you borrow money, you are supposed to repay it gradually. Lenders, on the other hand, want to be compensated for their services and the risk they incur by lending you money. That is, you will not just repay the ...

Calculate Interest Rate for Loan

The interest rate is the fee charged by a lender to a borrower and is expressed as a percentage of the principal—the lent amount. The interest rate on a loan is often expressed as an annual percentage rate, abbreviated as ...

Calculate Interest for Given Period

Using the IPMT function in Excel, we can compute the interest payment on any loan. This step-by-step tutorial will guide Excel users of all skill levels through the process to calculate interest for given period. Finally, the formula: =IPMT(B3/12,1,B5,-B2) The ...

How To Use Excel GCD Function

This post will guide you how to use Excel GCD function with syntax and examples in Microsoft excel. Description The Excel GCD function Returns the greatest common divisor of two or more integers. So you can use the GCD function ...

Calculate A Ratio From Two Numbers In Excel

In elementary mathematics, a ratio is a connection or comparison between two or more integers. For example, ratios are often expressed as ":" to demonstrate the relationship between two numbers. You would think that manually calculating a ratio from two ...

How To Use Excel RRI Function

This post will guide you how to use Excel RRI function with syntax and examples in Microsoft excel. Description The Excel RRI function Returns an equivalent interest rate for the growth of an investment. So you can use the RRI ...

CAGR Formula Examples in Excel

CAGR in Excel is a formula that calculates the compound annual growth rate for any invested amount over the specified years or timeframe. Although there is no direct function in Excel that can help us identify the CAGR value, there ...

Build Hyperlink With VLOOKUP in Excel

You might have come across a task in which you were assigned to build hyperlinks, which seems very easy, and if you are new to excel or don't have enough experience with it, then you might wonder about doing this ...

Sidebar