Last updated on October 8th, 2023 at 02:33 pm
Absolute and relative cell reference refers to the way you use cell names or range names in formulas and functions. Absolute and relative addressing allows you to copy a formula or function to a new cell. However, these two do not behave the same way when copied. Keep reading to learn:
- What is absolute addressing;
- What absolute cell reference is; and
- How and when to use absolute and relative cell reference.
In the introduction to spreadsheets, we covered cell reference and cell range. so if you are unaware of what these terms mean, be sure to visit that post before going through this one. Additionally, before you look at relative and absolute cell references, if you do not know what formulas and functions are or how to use them, visit what is a spreadsheet function and introduction to spreadsheet formulas.
What is relative cell reference in Excel?
Generally, when you use cell addresses to prepare formulas in Excel, by default, you use relative addressing. As such, each time your formula is copied the name of the next cell is used in the formula. For example:
When the above formula is copied downwards, it will look like this: = sum(C4:F4) then =sum(C5:F5) followed by =sum(C5:F6). As you can see, each time, the row name will change so as to find the total of each student’s scores. However, we do not use absolute addressing to replicate a formula downwards.
If you are interested in learning how to sort, display and analyze information in Excel, visit analyzing data in Excel.
What is absolute cell address?
An absolute cell reference is used when we wish to reuse the information in the cell multiple times in calculations. For instance: Company X has 1000 workers for whom it needs to calculate salaries. Every few years, the tax information in the country changes, and as a result, so do the salary calculations. In this instance, it would be best to use absolute addressing. Let’s look at the table below.
In this table, we are calculating the salary for all employees of Company X based on the tax threshold and the rate. Instead of entering tax rate or threshold into the IF function, we put these figures in one place in our spreadsheet and reuse them by applying absolute cell referencing. In this way, when changes are needed, we would need to make the change in one place only (H1 for the tax threshold and H2 for the tax rate).
Let’s look at the formula in the formula bar. This is the formula in the active cell, cell D2. Because the tax threshold and tax rate are each in only one cell in the spreadsheet, when we drag down to copy the function, we will not want these cell references to change. As such, we put the $ sign before the row name to prevent the row from changing.
‼Absolute cell addressing can be written $C2 or C$2.
Interested in learning some Excel shortcuts? Visit Microsoft Excel features and shortcuts.
When to put $ before the column name or row name
When we copy downwards, it is the row name (the number) that usually changes. As such, to ensure that the row name does not change, we place the $ before the name of the row like this C$2. Likewise, when we copy across, it is the column name (the letter) that would change. So to ensure this change does not happen, we put the $ before the letter like this ($C2)
Are you interested in learning more spreadsheet functions? Visit advanced spreadsheet functions.
Before you go
We try our best to be as accurate as possible. However, if you have any questions or queries, be sure to leave them in the comment section below.