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**hide

**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.

**Read More:** **Different Types of Cell References in Excel**

**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.

**Related Content:** **How to Use Cell References in Excel Formula**

**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.

**Similar Reading:**

**Mixed Cell Reference in Excel****How to Reference Cell in Another Sheet Dynamically in Excel****Difference Between Absolute and Relative Reference in Excel****How to Reference a Cell from a Different Worksheet in Excel****How to Reference Cell by Row and Column Number in Excel**

**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.

**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.

**Related articles**

**How to Display Text from Another Cell in Excel****How to Reference Text in Another Cell in Excel****How to Keep a Cell Fixed in Excel Formula****How to Use Variable Row Number as Cell Reference in Excel****How to Find and Replace Cell Reference in Excel Formula****How to Use Cell Value as Worksheet Name in Formula Reference in Excel****How to Use OFFSET for Cell Reference in Excel****How to Hard Code in Excel**