Absolute and relative addresses in Excel

Absolute and relative addresses in Excel Picture 1

What is relative and absolute address and how to identify it?

Relative addresses are addresses that change when copying a formula. This is the default address when we formulate the formula. For example A1, B2 .

The absolute address is that the address you did not change when copying the formula. The absolute address is distinguished from the absolute address with the character $. For example $ A1 $ 1, $ B1 $ 2….

Mixed addresses are a combination of relative and absolute addresses. For example $ A1. B $ 2….

For example, relative and absolute address differentiation:

Absolute and relative addresses in Excel Picture 2

With the formula of column F3 = VLOOKUP (D3, $ I $ 2: $ J $ 5,2, FALSE).

Address D3 is the relative address. And $ I $ 2: $ J $ 5 is the absolute address. When you copy the formula to cell D4, the formula in D4 will be = VLOOKUP (D4, $ I $ 2: $ J $ 5,2, FALSE).

Absolute and relative addresses in Excel Picture 3

When copying the formula, Excel changed the position D3 => D4 corresponding to the change correlated with the position of cells F3 and F4. The position of the region $ I $ 2: $ J $ 5 is preserved because you have set it to the absolute position.

Ways to convert relative and absolute references

Relative addresses are the default addresses when you formulate. To switch to the relative position you press the F4 button immediately after entering the relative position.

For example, in any cell you type: = B1, you press 1 key F4 => address changes to = $ B $ 1: absolute for both columns and rows). You press once again F4 => address changes to = B $ 1: absolute line and relative column. Next press F4 => the address changes to = $ B1: relative row and absolute column. You press the F4 key to return to the absolute address = B1.

Apply relative and absolute addresses in Excel

The absolute wall address is applied in the LOOKUP, INDEX, MATCH, search functions SUMIF, COUNTIF, RANK, etc. that need to extract data from related tables.

Example of using the RANK function to rank a series of numbers:

Absolute and relative addresses in Excel Picture 4

With the above usage, you leave the Value value as a relative value so that when copying the formula changes according to the position of the cell that returns the result. As for the array $ G $ 3: $ G $ 12 you leave the absolute value to not change position when copying the formula.

Above Dexterity Software has introduced readers with absolute and relative address. Hope the article will help you. Good luck!!!