The absolute, relative and mixed references in excel
The core competency of Excel is to create and calculate relationships between cells and their content.
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 also mixed cell references, and of course also where and how to use them properly.
The absolute, relative and mixed references in excel
The core competency of Excel is to create and calculate relationships between cells and their content.
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 also mixed cell references, and of course also where and how to use them properly.
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 also noticed the $ characters we put in front of and behind 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 automatically calculated using the absolute reference.
See picture: (click to enlarge)
In order to calculate the total amounts via 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 actually 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 also noticed the $ characters we put in front of and behind 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 automatically calculated using the absolute reference.
See picture:
In order to calculate the total amounts via 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 actually 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) enter and so on, or you can use again the copy control with a relative reference (without $ sign).
- 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 at the bottom right and then pull 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, the cells of the columns via this function always behave relative to each other (in 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) enter and so on, or you can use again the copy control with a relative reference (without $ sign).
- 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 at the bottom right and then pull 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, the cells of the columns via this function always behave relative to each other (in dependent relationship).
Thus, in our example, we will change from “C8: B11” to “D8: D11”, etc.
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.
- Mark 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 selected area down.
The $ sign is now not placed in front of the column letters, but only behind it, not to set 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.
- Mark 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 selected area down.
The $ sign is now not placed in front of the column letters, but only behind it, not to set the column, but the line as absolute.
See picture:
Search the net for other topics:
About the author:
Search the net for other topics:
About the author:
Popular Posts:
The Xreference function – difference to Sreference and Wreference
Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.
Custom Formatting Excel – Number Format Codes Excel
Wir erläutern an einem praktischen Beispiel die Bedeutung und die Einsatzmöglichkeiten der benutzerdefinierten Formatierung in Excel. Zahlenformatcodes in Excel sind kein Hexenwerk!
Simply insert Excel spreadsheets into Word Documents
So you can easily insert Excel spreadsheets into Word and link them together to get a dynamic document.
Microsoft Excel: Create vacation planner 2022 with public holidays and weekends
We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.
Create annual calendar 2022 in Microsoft Excel
In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.
Create individual charts in Microsoft Excel
Charts are created quickly in Microsoft Excel. We explain how you can customize them, and also swap (transpose) the axes.
Monthly Bestseller:
Popular Posts:
The Xreference function – difference to Sreference and Wreference
Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.
Custom Formatting Excel – Number Format Codes Excel
Wir erläutern an einem praktischen Beispiel die Bedeutung und die Einsatzmöglichkeiten der benutzerdefinierten Formatierung in Excel. Zahlenformatcodes in Excel sind kein Hexenwerk!
Simply insert Excel spreadsheets into Word Documents
So you can easily insert Excel spreadsheets into Word and link them together to get a dynamic document.
Microsoft Excel: Create vacation planner 2022 with public holidays and weekends
We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.
Create annual calendar 2022 in Microsoft Excel
In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.
Create individual charts in Microsoft Excel
Charts are created quickly in Microsoft Excel. We explain how you can customize them, and also swap (transpose) the axes.
