Even if you have recently started working with Microsoft Excel, you probably know that one of its advantages is convenient work with formulas and tables. Therefore, today we will consider addressing in such tables and find out what relative, absolute and mixed links are. Their work is quite simple, so it will be easiest to consider them in practice. So what are absolute and relative references?
Definition
Microsoft Excel links are not the same as web links. A link is a cell address (for example: A1, B10). They are divided into two main types - absolute and relative links. In Excel, addressing occurs by cells, so when transferring and copying formulas, we often need to save or, conversely, replace data.
Relative link
This species is the most common. If you did not go to special courses, no one taught you, then you are unlikely to have met with absolute references. So we'll start with the simpler one.
Absolute and relative references in formulas are used to work with cell addresses, but for opposite purposes. The view we are considering is basic and does not save cell addresses in the formula when it is transferred. Consider an example.
- Let us be given cells with data (columns) - M1, M2, M3, H1, H2, H3. Under them, in cell M4, we write a formula, the result of which will be the sum of three numbers (M4=M1+M2+M3). Everything seems to be simple. Now we need to calculate the contents of cell H4. It is also the sum of the data above it. By dragging or simply copying cell M4, we transfer the formula to H4. As a result, all links in it (M1, M2, M3) will be replaced by H1, H2 and H3, respectively.
This is called relative addressing. The example seems simple and the natural question arises, isn't it easier to rewrite the formula? No, in large tables and large calculations that can use a variety of functions and operators, it is much more reliable to simply copy and paste the formula than to re-compose the expression.
Absolute reference
As already mentioned, absolute and relative references serve different purposes in Excel. The second type of links that we are considering today is designed to preserve the addressing for any changes and movements of the formula.
Let's say you need to solve some physical problem. You have a certain constant written in cell A1. Of course, it would be possible to write this number manually each time, but it is easier to write it in one specific place. We are also given two columns of data (M1, M2, P1, P2). In cells M3 and P3 it is necessarycalculate the following formula: M3=(M1+M2)$A$1. Now, when dragging and copying to cell P3, the result will be P3=(P1+P2)$A$1. That is, the address of the used cell will not move
To make the link address absolute, click on the formula, select the required "link" and press F4. Or manually type in "dollar" symbols.
Mixed
This is the last type found in formulas. As the name implies, this is an absolute and relative reference at the same time. It is easy to guess that in practice it is denoted as $A1 or A$1. Thus, it allows you to keep addressing a column or row, but keep "sliding" over it. Consider an example.
| A | B | ะก | |
| 1 | 3, 5 | 4, 5 | |
| 2 | 70 | ||
| 3 | 80 |
So we have a table with some data. In column "A" we have a certain value, and in row "1", the coefficient by which we need to multiply. Cells B2, C3 will contain the results. As you can see, absolute and relative references are powerless here. In such cases, a mixed type must be used.
Let's write the formula in the first cell B2=A2B1. But to drag it to the right, you need to freeze the column "A", and to move it down, you need to freeze the row "1". Therefore correctthe formula would be written B2=$A2B$1. Thus, when dragging the formula, we will get a series of values in each of the remaining cells:
- B3=$A3B$1
- C2=$A2C$1
- C3=$A3C$1
As you can see, absolute and relative reference would not help here. Therefore, for complex calculations, it is necessary to use exclusively mixed type. This will allow you to painlessly scale the table, add rows and columns to it. The only thing is that when working with these types of addressing, you should always be very careful, because if you make a mistake once in a table with a mixed reference, then it will be very difficult to find a defect.
That's it. We hope you now have a better understanding of what an absolute and relative reference is.
