Relative, Absolute and Mixed Cell Reference in Excel

Understanding how cell references in Excel work is a cornerstone of effective spreadsheet use. Whether you’re building formulas that auto-adjust to changes, locking a specific cell, or creating dynamic worksheets, the types of cell references—relative, absolute, and mixed—play a vital role. This guide will break down each type of cell reference, show you how to switch between them using tools like the Excel F4 shortcut and provide practical examples to help you apply them effectively in your formulas. By the end, you’ll have a clear understanding of how these references enhance accuracy and flexibility in your Excel tasks.

What is a Cell Reference in Excel

A cell reference (or cell address) is a combination of a column letter and a row number that identifies a specific cell in a worksheet. These references are essential for formulas and functions, allowing you to retrieve, calculate, or manipulate data.

For example:

• A2 refers to the cell in column A and row 2.
• B5 refers to the cell in column B and row 5.

These cell addresses can be used in formulas like =A2+B5 to calculate or copy values between cells.

For example:

Types of Cell Reference in Excel

Understanding various cell references primarily makes it easier for us to use Excel formulas and avoid unexpected formula errors. When copying and pasting Excel formulas, this is quite useful. Based on various use situations, Excel offers three main types of cell references, including:

• Relative Cell Reference
• Absolute Cell Reference
• Mixed Cell Reference

1. Relative Cell Reference 

Relative cell references are the default in Excel. They change automatically when a formula is copied to another cell, maintaining the relative position between cells. There is no dollar ($) sign in the relative reference for the cell. 

How to Use Relative Cell References in Excel (With Examples)

Let's see how to use the Relative Cell references through examples,

Example 1: 

If you refer to cell B1 from cell E1, actually you would be referring to a cell that is 3 columns to the left (E-B = 3) within the same row number 1. 

When it is copied to other locations present in a worksheet, the relative reference for that location will be changed automatically. (because relative cell reference describes offset to another cell rather than a fixed address as In our example, offset is : 3 columns left in the same row).

Example 2:

If you copy the formula = C2 / A2 from the cell "E2" to "E3", the formula in E3 will automatically become =C3/A3.

When to Use Relative Cell References in Excel

When you need to develop a formula for a set of cells and the formula needs to make a reference to a relative cell reference, relative cell references come in handy.

When this occurs, you can create the formula in one cell and copy it before pasting it into every other cell.

2. Absolute Cell Reference 

When copying or using AutoFill, there are times when the cell reference must stay the same. A column and/or row reference is kept constant using dollar signs. So, to get an absolute reference from a relative, we can use the dollar sign ($) characters.

To refer to an actual fixed location on a worksheet whenever copying is done, we use absolute reference. The reference here is locked such that rows and columns do not shift when copied. 

How to Use Absolute Cell References in Excel

Below is an example depicting how to use Absolute Cell References in Excel.

Example:

To lock the reference A2, use $A$2 in a formula like =C2/$A$2. When copied to other cells, $A$2 will remain fixed.

G2 = C2/$A$2, when copied to G3, G3 becomes = C3/$A$2

 Note: C3 is 4 columns left to G3 in the same row.

Here, original cell reference A2 is maintained whenever we copy G2 to any of the cells. So I3 = E3/$A$2 because E3 comes from the relative reference (4 columns left to the current one) & /$A$2 comes from the absolute reference.

Therefore, I3 = E3//$A$2 = 12/10 = 1.2

What Does the Dollar ($) Sign Do

When the row and column numbers are preceded by the dollar symbol ($), it becomes absolute (i.e., stops the row and column number from changing when copied to other cells). Dollar ($) before the row fixes the row & before the column fixes the column.

Practical Use Cases for Absolute References in Excel

When you need a fixed value in formulas, such as tax rates, constants, or specific cell addresses. You can also use Absolute Cell Reference Keyboard Shortcut F4.

3. Mixed Cell Reference 

Mixed references combine relative and absolute references. You can lock either the row or the column, depending on your needs.

Here, the Dollar ($) before the row number fixes/locks the row & before the column name fixes/locks the column.

Example:

When we fix the only row: If we have G2 = C2/A$2 then :

We used $ before the row number, so we are locking the only row here. When G2 is copied to G3, G3 = C3/A$2 (not C3/A3) because the row has been fixed already.

Here, whenever we copy G2 to any other cell, always the divisor will refer to a fixed row 2 (column vary according to the concept of relative reference)

So, when G2 is copied to I3, I3 = E3/C$2 because E3 comes from the relative reference (4 columns left to the current one) & C$2 comes from the absolute reference for row & relative reference for Column (6 Columns left to the current one)

Other Useful Ways to Use Cell References and Addresses

Now that we are familiar with the basics of using Cell References in Excel, let's see some other ways of using cell references.

Relative and Absolute References for Dates

We can use relative and absolute cell references to calculate dates.

• Relative Reference Example: =Order Date + Days to Deliver
• Absolute Reference Example: =Today() - Age in Days

Example: To Calculate the Date of Delivery online from the given date of the order placed & no of days it will take to deliver :

Here, We calculate the Date of Delivery by = Order Date + No of days to deliver. We used Relative cell reference so that individual product delivery dates can be calculated.

Absolute cell references for calculating dates :

Example: To Calculate the Date of Birth When the age is known is a number of days using Current date can be done by making use of absolute reference. 

Here, We calculate DOB by = Current Date - Age in days. The Current date is contained in the cell E2 & in subtraction, we fixed that date to subtract from the days.

Whole Column Reference 

To refer to all cells in a specific column in Excel, use the column letter twice with a colon in between, like B:B for column B. For example, to find the sum of all values in column B, type =SUM(B:B). Unlike a range like B1:B10, this approach automatically includes any new data added to the column, saving you the need to update the range manually.

Whole Row Reference 

You will want to refer to all the cells inside a particular row when operating with an Excel worksheet with any number of columns. Simply type a row number twice with a colon in between to refer to the entire row, for example, 2:2.

Example: You may want to find the sum of a row of data in certain cases. While you can do this with a regular cell range, such as =SUM(A2 : J2), you will need to change the cell range if your spreadsheet grows in size.

Excel, on the other hand, has a cell range that does not require the column letter and takes all the cells in the row in action. If you wanted to find the sum of all the values in row 2, for example, you would type =SUM (2:2). You can add as much data as you want to your spreadsheet without having to change your cell ranges if you use this type of cell range.

Refer to an Entire Column, Excluding the First Few Rows 

To refer to the entire column excluding the first few rows, you need to specify the range as we give in a normal fashion. We know that the Excel worksheets can have only 1,048,576 rows. (To check this, go to an empty cell & press: Ctrl + Down arrow Key)

So, we can do the sum of the entire column B except for the first 5 rows by = SUM(B6:B1048576).

Using a Mixed Entire Column Reference in Excel 

You can also create a mixed entire-column reference, say for example $B:B. But, practically, it is difficult to find a situation where it would be used.

Example:

How to Switch between Different Cell References

The $ sign can be manually typed in an Excel formula to adjust a relative cell relation to absolute or mixed. You can also speed things up by pressing the F4 key. You must be in formula edit mode to use the F4 shortcut. The steps are :
Firstly, choose the cell that contains the formula. Then, by pressing the F2 key or double-clicking the cell, you can enter Edit mode. Select the cell reference in which you want to make changes. Then, switch between four-cell reference forms by pressing F4.

Example: When you select a cell having only relative reference (i.e., no $ sign), say = B2:

• The first time when you press F4, it becomes =$B$2
• The second time when you press F4, it becomes =B$2
• The third time when you press F4, it becomes=$B2
• The fourth time when you press F4, it becomes back to the relative reference=B2

B2 --Press F4--> =$B$2  --Press F4--> =B$2 --Press F4--> = =$B2  --Press F4--> =B2

So, using F4, you do not require to manually type the $ symbol.

Cell References Across Worksheets

Cell reference in Excel are not limited to just the current worksheet. Excel allows you to reference cells from other worksheets in the same workbook. Follow the below steps to learn Cell References Across Worksheets:

• Type = in a cell on the current worksheet.
• Switch to another sheet and click the desired cell.
• Press Enter to complete the reference.

Example: =Sheet2!A1 references cell A1 on "Sheet2".

Key Points to Remember

• Relative references adjust automatically when formulas are copied.
• Absolute references keep the referenced cell constant.
• Mixed references lock either the row or column.
• Use the F4 key to quickly toggle between reference types.

Also Read:

• Top Excel Formulas and Functions You Should Know 2024
• Top 100+ Excel Shortcut Keys List (A to Z) [2024 Updated]
• Conditional Formatting in Excel

Conclusion

Mastering the use of relative, absolute, and mixed cell references unlocks the true potential of Excel for managing complex data. These references provide the foundation for creating adaptable and accurate formulas across single or multiple worksheets. By applying the examples and tips shared here, you’ll streamline your workflows and improve efficiency. Take the time to experiment with these techniques, and watch your Excel skills reach a new level.