Absolute and relative reference in Excel. How to use?

Even if you 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 the addressing in such tables and find out what are the relative, absolute and mixed references. Their work is quite simple, so the easiest way is to consider them in practice. So what is the absolute and relative reference?

Definition

Links to Microsoft Excel - this is not the same as the links on the Internet. The reference is the address of the cell (for example: A1, B10). They are divided into two main types - absolute and relative references. In Excel, addressing is done in cells, so when copying and copying formulas, we often need to save or, conversely, replace the data.

Relative link

This species is found most often. If you did not attend special courses, no one taught you, then you hardly met absolute references. So we start with what's easier.

Absolute and relative references in formulas serve to work with cell addresses, but for opposite purposes. The form we are considering is basic and does not store the addresses of cells in the formula when it is transferred. Let's consider an example.

• Let us give cells with data (columns) - M1, M2, M3, H1, H2, H3. Below them in cell M4 we write a formula, the result of which is the sum of three numbers (M4 = M1 + M2 + M3). It seems easy. Now we need to calculate the contents of cell H4. It is also the sum of the data that is above it. By dragging or simply copying the cell M4, we transfer the formula to H4. As a result, all references in it (M1, M2, M3) will be replaced by H1, H2 and H3, respectively.

This is called relative addressing. The example seems simple and a natural question arises, is it not easier to re-write the formula? No, in large tables and large-scale calculations, in which a variety of functions and operators can be used, it is much more reliable to simply copy and transfer the formula than to re-write the expression.

Absolute link

As already mentioned, absolute and relative references in Excel serve different purposes. The second type of links, considered by us today, is designed to preserve the addressing in 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, you could write this number each time manually, but it's easier to write it to one particular place. Also, we are given two columns of data (M1, M2, P1, P2). In cells M3 and P3, it is necessary to calculate the following formula: M3 = (M1 + M2) * $ A $ 1. Now, when dragging and copying to cell P3, we get the following result: P3 = (P1 + P2) * $ A $ 1. That is, the address of the used cell will not move.

To make the address of the link 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 suggests, 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, this allows you to save the addressing to a column or row, but continue to "slip" over it. Let's consider an example.

So, we have a table with some data. In the column "A" we have a certain value, and in the line "1", the coefficient to be multiplied by. Cells B2, C3 will contain the results. As you can see, the absolute and relative reference is powerless. In such cases, you must use a mixed type.

We write the formula in the first cell B2 = A2 * B1. But to drag it to the right, you need to fix the column "A", and to down, to fix the line "1". Therefore, the correct way to write the formula is B2 = $ A2 * B $ 1. Thus, when dragging a formula, we get a series of values in each of the remaining cells:

• B3 = $ A3 * B $ 1
• C2 = $ A2 * C $ 1
• C3 = $ A3 * C $ 1

As you can see, the absolute and relative reference here would not help. Therefore, in complex calculations it is necessary to use exclusively mixed type. This will allow you to scale the table painlessly, add rows and columns to it. The only thing when working with such types of addressing is always to be very careful, since having made a mistake once in the table with the used mixed link, then it will be very difficult to find the defect.

That's all. We hope, now you are better able to understand what is the absolute and relative reference.