Relative and Absolute Cell Address in the Spreadsheet

Get FREE Advanced Excel Exercises with Solutions!

We can use the cell addresses of other workbooks and applications as a reference. In Excel, there are two types of cell addresses one is a relative address another is an absolute address. Actually, everyone does not know the relative and absolute cell addresses in the spreadsheet. In this article, we will see the definitions, uses, and examples of relative and absolute cell addresses in the spreadsheet.

When copied and filled into other cells, relative and absolute references act differently. When a formula is copied to another cell, the relative references change. On the other hand, absolute references do not change regardless of where they are duplicated.

Table of Contents Expand

How to Use Relative Cell Address in the Spreadsheet

In Excel, a relative reference is a form of a cell reference. When the formula is copied to another cell, the reference changes. When computations must be repeated, relative cell references are employed.

Step 1: Inspect the Preferred Address Style in the Spreadsheet

In a spreadsheet, the default cell reference is relative. It’s only a combination of column name and row number, with no dollar ($) sign in between.

Step 2: Copy the Cell and Paste in Appropriate Cell

When we copy a formula from one cell to another, the relative reference changes.

Step 3: Make Sure the Copied References are Correct

It is obvious that while copying a formula cell, this will be in a relative cell address. But sometimes, it might happen that we copy the absolute cell reference instead of the relative cell reference. And this will be a very big problem while working in excel. So before copying the cell make sure that the reference cell is correct or not.

Example of Relative Cell Address in the Spreadsheet

Let us understand the concept of relative cell address in Excel with an example. For this, we are going to use the dataset below. Calculating a total price from a unit price and quantity across several items. Column B contains the items and the unit prices for each item are in column C, the quantity of every item is shown in column D. And we want the total price in column E.

In the example below, we want to develop a formula that multiplies the price of each item by the amount. We may build a single formula in cell E5 and then replicate it to the remaining rows instead of writing a separate formula for each row. We’ll utilize relative references to ensure that the formula appropriately calculates the sum for each item.

Firstly, select the cell where the formula will be entered. In our example, we’ll select E5.
Secondly, to compute the required value, enter the formula.

=C5*D5

After that, on the keyboard, press Enter. The formula will be applied, and the result will appear in the cell.
Now, in the bottom-right corner of the selected cell, look for the Fill Handle. We’ll look for the Fill Handle for cell E5 in our example.
Then, fill the cells by clicking and dragging the Fill Handle over them.

Additionally, if we want to look at the formula of each cell which is relative. We can see that in column F, all the rows are relative to other rows. For example, each cell references are relative like the formula below, C5*D5, C6*D6, and so on. As we can see all the rows of the formula are relative.

To double-check the formulae in the filled cells, double-click them.
Depending on the rows, each cell’s related cell references should be different.

How to Use Absolute Cell Address in Spreadsheet

Using absolute references in Excel might be a simple approach to increase the efficiency of our work. Here is how to add absolute references to our document.

Step 1: Check the Selected Reference Style

Try to make sure before adding an absolute reference if the formula is advantageous to our project or not. The most common scenarios where the absolute reference comes in handy are when several cells need access to the same value. We can specify a specific row, column, or cell for the formula to reference. This allows us to copy and paste it rapidly into other cells without having to re-enter the formula or manually alter it to target the correct cell.

Step 2: Add a Dollar Symbol Before the Cell Address in the Spreadsheet

To change a relative cell reference to an absolute cell reference, place a dollar sign before the column letter or row number we want to refer to. For example, if we have numerous items in the same row that we want to correspond to their separate columns, our reference would be “A$1.” No matter where we paste the formula, putting a dollar sign in front of both the letter and the number provides a totally absolute reference to one cell. “$A$1” would be our reference in this situation.

Step 3: Copy and Paste the Cell into Appropriate Cells

After we have created the formula using absolute references, right-click on the cell and select Copy, or click on the cell and press CTRL + C, depending on our operating system. Select the cell where we want to paste the absolute reference formula and press CTRL + V or right-click and choose paste. The formula is instantly pasted into our spreadsheet document, and any relative references are updated to correspond to the number of rows or columns we shifted, while absolute references remain unchanged.

Step 4: Check the References Which Copied

Consider testing the references, after pasting the copied cell into all the required cells on our spreadsheet. Fill in the numbers in each of the cells that the absolute formulas relate to, then compare the results to our manual calculations.

Example of Absolute Cell Address in the Spreadsheet

In the example below, we’ll compute the sales tax for each item in column E using cell B13, which provides a tax rate of 12 percent. We’ll need to make cell $B$13 an absolute reference to ensure that the reference to the tax rate remains consistent, even when the formula is duplicated and filled into other cells.

First, select the cell. In this case, we’ll choose cell E5.
To compute the required value, enter the formula. In our example, we’ll type the below formula for making $B$13 an absolute reference.

=(C5*D5)*$B$13

Then, press Enter. The formula will work and the result will appear in the cell.
In the bottom-right corner of the selected cell, look for the Fill Handle. We’ll look for the Fill Handle for cell E5 in our example. Fill the cells by dragging the fill handle over them, cells E6:E10 in our example.

After that, release the mouse.

In addition, to see if the cell address is absolute or not, look at column F which contains the formula of each Sales Tax calculation. And we can see the absolute value of the tax rate denoted as $B$13.

Next, again for calculating the total price with tax, use the formula.

=(C5*D5)+E5

Then, press Enter.

Now, drag the fill handle again to copy the formula.

To double-check the formulae in the filled cells, double-click on them. The absolute reference for each cell should be the same, however, the other references should be relative to the cell’s row.
When creating an absolute reference over many cells, remember to include the dollar symbol ($). When copied to other cells, Excel misinterpreted it as a relative reference, resulting in an inaccurate result.

Things to Remember for Relative and Absolute Cell Address in Spreadsheet

Formulas, charts, functions, and a mix of functions and other commands frequently employ them.
When copying to a separate cell, it’s critical to keep a cell reference consistent.
In Excel, press the F4 key to make a cell an absolute cell reference.
It saves a lot of time in calculations and is especially beneficial in complex formulas.

Summary of Relative and Absolute Cell Address in the Spreadsheet

Every cell in Excel has a relative reference by default.
Type “=A1+A2” in cell A3, copy and paste the formula in cell B3, and the formula will change to “=B1+B2” automatically.
When you use absolute references, the cell address does not change when you copy the formula.
The dollar sign ($) keeps the row and column references constant in absolute references.

Download Practice Workbook

You can download the workbook and practice with them.

Relative and Absolute Cell References.xlsx

Conclusion

Hope now you understand how to use relative and absolute cell addresses in the spreadsheet. If you have any questions, suggestions, or feedback please let us know in the comment section.

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel