Contains paid Promotion

How to set absolute, relative and mixed references
in Excel correctly

The core competency of Excel is to create and calculate relationships between cells and their contents.

However, Excel is not to be confused with a calculator.

So there are always uncertainties how to deal with cell relations properly, and above all where exactly the difference between the absolute and relative and then mixed cell references, and of course also where and how to use them properly.

Content:

The absolute references in Excel
The relative references in Excel
The mixed references in Excel

Subscribe here for free and anonymous RSS feed

How to set absolute, relative and mixed references in Excel correctly

The core competency of Excel is to create and calculate relationships between cells and their contents.

However, Excel is not to be confused with a calculator.

So there are always uncertainties how to deal with cell relations properly, and above all where exactly the difference between the absolute and relative and then mixed cell references, and of course also where and how to use them properly.

Content:

The absolute references in Excel
The relative references in Excel
The mixed references in Excel

Subscribe here for free and anonymous RSS feed

1. The absolute references in Excel

The absolute reference in Excel describes a fixed (ie absolute) reference point at which all cells which were included in the calculation via the copy control are oriented.

To make the whole thing a little more plastic here is a small example:

We have a table with net amounts, VAT and gross amounts, and a cell where the tax rate is 19%.
With the exception of the net amounts and the cell with the 19%, all other cells are empty and should be calculated with the corresponding function via copy control.

Position the cursor in the first cell next to the net amounts
Enter the following function: = C6 * $ F $ 6
Confirm with Enter

As we can see, the calculation is made correctly and the sales tax is calculated out of the € 100. Surely you have also noticed the $ characters we have placed before and after the column letters.

With this we have achieved that this cell with the VAT rate is now set as an absolute cell. And instead of having to re-enter this formula for every tax rate calculation, we can simply use the copy control by marking the first calculated cell and then double clicking on the small gain of that cell.

As a result, all cells in the table are always automatically calculated using the absolute reference.

See picture: (click to enlarge)

In order to calculate the totals using the copy control, we click in cell E6 and enter there as a function =C6+D6

And this time without $ sign in front of and behind the column letter. Then simply double-click (optionally also click and drag) on the gain at the cell edge, and the entire column with the gross amounts will be calculated automatically.

With that, we have already made the bridge to the relative references, which we would like to discuss in more detail in the next section.

The absolute reference in Excel describes a fixed (ie absolute) reference point at which all cells which were included in the calculation via the copy control are oriented.

To make the whole thing a little more plastic here is a small example:

We have a table with net amounts, VAT and gross amounts, and a cell where the tax rate is 19%.
With the exception of the net amounts and the cell with the 19%, all other cells are empty and should be calculated with the corresponding function via copy control.

Position the cursor in the first cell next to the net amounts
Enter the following function: = C6 * $ F $ 6
Confirm with Enter

As we can see, the calculation is made correctly and the sales tax is calculated out of the € 100. Surely you have also noticed the $ characters we have placed before and after the column letters.

With this we have achieved that this cell with the VAT rate is now set as an absolute cell. And instead of having to re-enter this formula for every tax rate calculation, we can simply use the copy control by marking the first calculated cell and then double clicking on the small gain of that cell.

As a result, all cells in the table are always automatically calculated using the absolute reference.

See picture:

In order to calculate the totals using the copy control, we click in cell E6 and enter there as a function =C6+D6

And this time without $ sign in front of and behind the column letter. Then simply double-click (optionally also click and drag) on the gain at the cell edge, and the entire column with the gross amounts will be calculated automatically.

With that, we have already made the bridge to the relative references, which we would like to discuss in more detail in the next section.

2. The relative references in Excel

The relative references in Excel are not fixed but flexible and interrelated.

In the following example we would like to make this a bit more transparent:

We have a table listing the number of recruitment agencies in different branches and quarters. Now to form the sums of the exchanges you could now in each cell with the question mark simple = sum (C8: C11) and so on enter, or you can also use the copy control with a relative reference (without $ sign) again.

Mark cell C12
Enter function = sum (C8: C11) and confirm with Enter
Mark the cell again and with the mouse left mouse button pressed down at the cell border reinforcement (hold down) and then drag it to G12

As a result, all values have been calculated in one go, without having to reenter each function over and over again.

See picture: (click to enlarge)

If the result of the cell C8 is now extended to G8 by means of copy control, then the cells of the columns via this function always behave relative to each other (in a dependent relationship).

Thus, in our example, we will change from “C8:B11” to “D8:D11” etc.

The relative references in Excel are not fixed but flexible and interrelated.

In the following example we would like to make this a bit more transparent:

We have a table listing the number of recruitment agencies in different branches and quarters. Now to form the sums of the exchanges you could now in each cell with the question mark simple = sum (C8: C11) and so on enter, or you can also use the copy control with a relative reference (without $ sign) again.

Mark cell C12
Enter function = sum (C8: C11) and confirm with Enter
Mark the cell again and with the mouse left mouse button pressed down at the cell border reinforcement (hold down) and then drag it to G12

As a result, all values have been calculated in one go, without having to reenter each function over and over again.

See picture:

If the result of the cell C8 is now extended to G8 by means of copy control, then the cells of the columns via this function always behave relative to each other (in a dependent relationship).

Thus, in our example, we will change from “C8:B11” to “D8:D11” etc.

3. The mixed references in Excel

Finally, let’s take a closer look at the mixed references in Excel.

As the name suggests, relative and absolute references are used here to work with the copy control. You can not just set a single cell, but either a column or a row as absolute.

In our example, we have created a table in which 5 tenants and their cost sharing in cold rent, heating costs and water costs are listed. And now an increase of these costs by copy control in the horizontal direction by entering a single function.

Select cell D19
Enter the function =Sum(=SUM(D14+$C7)
Confirm with Enter
Mark cell D19 again and pull on the right bottom reinforcement with the left mouse button pressed down to D21
Then proceed from cell D21 again at the cell amplification extend the entire label to cell H21

Here, instead of an absolute cell reference, we did not place a $ sign in front of and behind the column letter, but just before it. This step tells Excel that only the column, but not the row, is absolute.

See picture: (click to enlarge)

As you can see, we’ve saved a considerable amount of time by entering just one function in combination with Excel’s copy control.

The use of absolute, relative and mixed references is thus a very grateful companion especially for large tables where many calculations with reference points are made.

Of course, the whole goes in the horizontal direction, if the table structure is stored differently.

For this purpose, we have the table times so constructed that they perform the first copy control not as before vertically, but horizontally (based) on the structure of the cost increases, and then drag the marked area down.

The $ sign is now not placed before the column letters, but only behind it to set not the column, but the line as absolute.

See picture (click to enlarge)

Finally, let’s take a closer look at the mixed references in Excel.

As the name suggests, relative and absolute references are used here to work with the copy control. You can not just set a single cell, but either a column or a row as absolute.

In our example, we have created a table in which 5 tenants and their cost sharing in cold rent, heating costs and water costs are listed. And now an increase of these costs by copy control in the horizontal direction by entering a single function.

Select cell D19
Enter the function =Sum(=SUM(D14+$C7)
Confirm with Enter
Mark cell D19 again and pull on the right bottom reinforcement with the left mouse button pressed down to D21
Then proceed from cell D21 again at the cell amplification extend the entire label to cell H21

Here, instead of an absolute cell reference, we did not place a $ sign in front of and behind the column letter, but just before it. This step tells Excel that only the column, but not the row, is absolute.

See picture:

As you can see, we’ve saved a considerable amount of time by entering just one function in combination with Excel’s copy control.

The use of absolute, relative and mixed references is thus a very grateful companion especially for large tables where many calculations with reference points are made.

Of course, the whole goes in the horizontal direction, if the table structure is stored differently.

For this purpose, we have the table times so constructed that they perform the first copy control not as before vertically, but horizontally (based) on the structure of the cost increases, and then drag the marked area down.

The $ sign is now not placed before the column letters, but only behind it to set not the column, but the line as absolute.

See picture: