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)