HomeApplicationOffice informationAbsolute and relative addresses in Excel

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!!!

4 ★ | 2 Vote

You should read it

May be interested

  • Quickly insert multiple images at once in ExcelPhoto of Quickly insert multiple images at once in Excel
    today dexterity software will guide you how to quickly insert rows of images at once in excel with the tool ablebit for excel.
  • Excel date function - Usage and examplesPhoto of Excel date function - Usage and examples
    the date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
  • How to lock Excel files from editingPhoto of How to lock Excel files from editing
    you want to send an excel file that can only be read by the recipient and cannot change the data information in the file. in this article, tipsmake.com would like to show you how to lock excel files from editing correctly.
  • How to separate text strings by commas or spaces in ExcelPhoto of How to separate text strings by commas or spaces in Excel
    separating text strings based on commas or spaces is a trick that helps you split the characters in a parameter cell into different clusters based on commas or spaces that parameter cell contains. tipsmake will guide you how to separate text strings by commas or spaces in excel.
  • How to sort data in Excel Ascending and descending, according to ABCPhoto of How to sort data in Excel Ascending and descending, according to ABC
    sorting lists in alphabetical order or descending order is often used in lists of names of people, searching for elements with the greatest number of values. and the arrangement of such data is not difficult with the help of excel in the following article of tipsmake.com.
  • How to fix word errors in Word are spaced, words are far away in WordPhoto of How to fix word errors in Word are spaced, words are far away in Word
    text that has a typo error will make the overall presentation for your article not look good. the reasons for this disruption are several types and their respective fixes, all of which will be covered in the following article by tipsmake.vn.

Office information