Absolute link in Excel fixes the cell in the formula

The advantages of absolute references are difficult to underestimate. They often have to be used in the process of working with the program. Relative references to cells in Excel are more popular than absolute ones, but they also have their pros and cons.

In Excel, there are several types of links: absolute, relative and mixed. This also includes "names" for whole ranges of cells. Consider their possibilities and differences in practical application in formulas.



Absolute and relative links in Excel

Absolute references allow us to fix a row or column (or row and column at a time) to which the formula should refer. Relative references in Excel change automatically when you copy a formula along a range of cells, both vertically and horizontally. A simple example of relative cell addresses. Let us calculate the volume of the sphere in Excel:

1. Fill the range of cells A2: A5 with different radii.
2. In cell B2, enter the formula for calculating the volume of the sphere, which will refer to the value of A2. The formula will look like this: =(4/3)*3.14*A2^3
3. Copy the formula from B2 along column A2: A5.

As you can see, the relative addresses help to change the address in each formula automatically.

It is also worth to point the regularity of changes in references of formulas. Data in B3 refers to A3, B4 to A4, and so on. Everything depends on where the first introduced formula will refer, and its copies will change the references relative to its position in the range of cells on the sheet.

Now instead of numbers we use absolute references:

The result of the calculation is the same, but the formulas are more flexible to the changes. It is enough to change the value in one cell and the whole column is recalculated automatically. See the following example.



Use of absolute and relative links in Excel

Fill in the plate, as shown in the picture:

Description of the source table. In cell A2, there is an actual euro exchange rate against the dollar for today. In the range of cells B2: B4 are the amounts in dollars. In the range C2: C4 will be the amount in euros after the conversion of currencies. Tomorrow the course will change and the task of the plate will automatically recalculate the range of C2: C4 depending on the change in the value in cell A2 (that is, the euro rate).

To solve this problem, we need to enter a formula in C2: = B2 / A2 and copy it to all cells in the C2: C4 range. But here there is a problem. From the previous example, we know that when copying, relative references automatically change addresses relative to their position. Therefore, an error occurs:

Concerning the first argument, this is quite acceptable. After all, the formula automatically refers to the new value in the column of the table cells (dollar amounts). But the second indicator we need to fix on the address A2. Accordingly, it is necessary to change the relative reference to the absolute in the formula.

How to make an absolute reference in Excel? It is very simple to put the $ (dollar) symbol before the line or column number or before the both of them. Below, consider all 3 options and determine their differences.

Our new formula should contain at once 2 types of links: absolute and relative.

1. In C2, enter another formula: = B2 / A $ 2. To change links in Excel, double click on the cell with the left mouse button or press the F2 key on the keyboard.
2. Copy it to the other cells in the C3:C4 range.

Description of the new formula. The dollar symbol ($) in the address of references fixes the address in the new copied formulas.

Absolute, relative and mixed references in Excel:

1. $ A $ 2 - address of the absolute reference with fixation on columns and rows, both vertically and horizontally.
2. $ A2 is a mixed link. When copying a column is fixed, and the row is changed.
3. A $ 2 is a mixed link. When copying a row is fixed, and the column is changed.

For comparison: A2 is a relative address, without fixation. During the copying of the formulas, the row (2) and the column (A) automatically change to the new addresses relative to the location of the copied formula, both vertically and horizontally.

Note. In this example, the formula can contain not only a mixed link, but the absolute result: = B2 / $ A $ 2. The result will be the same. But in practice, there are often cases when you cannot do a thing without mixed references.

Helpful advice. To avoid entering the dollar symbol ($) manually, after specifying the address, press F4 repeatedly to select the required type: absolute or mixed. It's fast and convenient.