Contains paid Promotion

Advertisement

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.

Absolute und Relative Bezüge in Excel
Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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.

Absolute und Relative Bezüge in Excel
Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. The absolute references in Excel

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)

Absoluter Zellbezug Abb.1
Absoluter Zellbezug Abb.2
Advertisement

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:

Absoluter Zellbezug Abb.1
Absoluter Zellbezug Abb.2
Advertisement

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

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)

Relative Bezüge in Excel Abb.1
Relative Bezüge in Excel Abb.2

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.

Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

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:

Relative Bezüge in Excel Abb.1
Relative Bezüge in Excel Abb.2

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.

Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige
4x CHIP TESTSIEGER Handyversicherung_600x500
Anzeige

3. The mixed references in Excel

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)

Gemischte Bezüge in Excel Abb.1
Gemischte Bezüge in Excel Abb.2

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.

Anzeige

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)

gemischter Bezug-Kopierrichtung Horizontal
Anzeige
Print Friendly, PDF & Email

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:

Gemischte Bezüge in Excel Abb.1
Gemischte Bezüge in Excel Abb.2

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.

Anzeige

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:

gemischter Bezug-Kopierrichtung Horizontal
Anzeige
Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-09-28T14:00:23+00:00By |Categories: Excel 2016, Office 2016|Tags: , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok