Absolute and Relative Cell Address in Excel
Excel spreadsheet functions to use the address of the cell as a data source (usually but not 100%). In Excel, we can use two types of cell references.
- The Relative cell address in Excel
- The Absolute cell address in Excel
Why are these two types of cell address in Excel? In my view, primarily due to the copy formulas containing cells address. In some formulas, we just need to let him keep his position address and an address can be changed due to copy the formula. I hope that I will explain this difference.
In Excel, there are two options copying formulas or cell.
- Copy the formula down or up
- Copy the formula to the right or to the left
When copying Excel formula is changed to address the cell or range of cells.
Depending on which option we use and what we want to get the formula you need to use one of two types of cells address. If you copy the formula down or up then we can change the number of rows in the address of a cell or range of cells. If you copy the formula to the right or to the left then change the header of the column (letter) to the addresses that are typed in the formula. If we want to fix the number of rows or the columns then in front of a letter or number we set the dollar sign ($).
Relative Cell Address in Excel
Relative cell address is the basic address specific cells in Excel. eg: A1. This is the address of the first cell on the worksheet, which is located in the upper left corner of the worksheet and indicates the intersection of column A and a row of number '1'. This intersection 'column' and 'row' is the same for all other columns and rows. In each cell can enter some information, be it text or value or a formula that returns a certain result.
In the picture below you see in column 'D' formula =A2+B2 in cell 'C2' after copying down. Note that changing the number of rows and columns of labels remain the same. So they used a relative cell address A2 and B2.
In the picture below you see the formula =100+J2 in Excel cell "J3". In the formula, the value '100' is added to the value of the cells 'J2'. I copied this formula to the right and back of the cell 'N3' I have a new formula =100+N2. So Excel is a fixed value of '100' but changed its label column K, L, M, N during copying because the 'J2' relative cell address.
The Absolute and Relative References a Cell Address in Excel
Absolute address in Excel determines the dollar sign ($). This sign is placed in front of the letters column or row number in Excel formula. Notice in the image below in cell 'A2' contains a value (number 2) in column 'B' is a formula. I want to add up the value of the 'A2' with each number in column 'B'. Therefore, I created a formula that contains absolute cell address 'A2' ($A$2) and the relative address of the cell 'B2'. After you copy the formula down Excel automatically changes the relative row of and address all depends on in which the row formula.
Absolute address some Excel cell or range of cells can be faster to write by placing the cursor inside/near the address of a cell or range of cells (in the formula field, see picture above) and press the F4 key on your keyboard. For the first pressing F4 address changes in absolute $A$2, for the second pressing changes the address in the mixed A$2, again repeatedly pressing F4 address changes in the $A2, again repeatedly pressed (fourth consecutive pressing F4) returns the address of the relative A2.
In the picture below you see a fixed value in cell 'I2'. This value I want to add up the values in each column, so I copied the formula right. For a fixed value I set an absolute cell address 'I2' ($I$2). After copying the formula to the right, the results are changed depending on the values in columns. So Excel is a fixed value in the cell 'I2' and adder with values in the columns of 'J3 .... N3'.
Absolute addresses the range of data in Excel replaces the definition of a range of cells called
When we need to create a formula with a range of data or more cells then use absolute cell or range of data ($A$10:$G$17).
Instead of the absolute address ranges of cells can be defined name for a specific data range or the cells and use it in a formula. This is for me the best way to of work with formulas. I always when I have the opportunity to define the name of a cell or range of cells to use in formulas. So I am sure that I will not go wrong during of work and easier to I perceive the name in the formula than absolute addresses. The use of designated cells when creating the formula and defining the cell range names I wrote in the tutorial at the link.