Relative and Absolute Reference in Excel

Understand the difference between relative and absolute reference in Excel with real-life examples. Learn when and how to use them like a pro!

If you’ve ever copied a formula in Excel and noticed the result changing unexpectedly – you’re not alone! That’s where relative and absolute references come into play.

These references are at the heart of every Excel formula and understanding them is a game-changer, especially when you’re working with lots of data.

In this post, we’ll explain both in simple words, with real-life examples to help you confidently use them in your work.

What Are Cell References in Excel?

Before diving deep, let’s quickly recap what a cell reference is.

When you write a formula like =A1 + B1, you’re telling Excel to use values from cells A1 and B1. But when you copy that formula to another cell, Excel may change those references based on where you paste it.

This is where the concept of relative and absolute references matters.

---

What is a Relative Reference in Excel?

A relative reference changes when you copy the formula to another cell.

Example:

Suppose you have the following data:

A	B	C
Price	Qty	Total
100	2	=A2*B2
150	1

In C2, you type:
=A2*B2
Now, if you copy this formula down to C3, Excel automatically adjusts it to:
=A3*B3

This is relative reference in action – Excel adjusts based on the row or column where you’re pasting the formula.

👉 Real-Life Use:
Used in most basic calculations like totals, discounts, or multiplying values across rows.

---

What is an Absolute Reference in Excel?

An absolute reference does not change when you copy the formula.

Syntax:

We use a dollar sign ($) to lock the row, column, or both:

• $A$1 – Locks both column and row.
• A$1 – Locks only the row.
• $A1 – Locks only the column.

Example:

Let’s say you have a fixed tax rate in cell D2:

A	B	C	D
Price	Qty	Total	Tax Rate
100	2	=A2*B2*$D$2	5%

Here, you want to multiply price × quantity × tax rate. The tax rate is always in D2.

👉 If you copy the formula down, using $D$2 keeps the tax rate reference fixed, no matter where the formula is pasted.

---

Relative vs Absolute Reference – Key Differences

Feature	Relative Reference	Absolute Reference
Adjusts when copied	✅ Yes	❌ No
Use case	Row-wise calculation	Fixed value like tax rate
Syntax Example	A2	$A$2

---

Mixed Reference in Excel

What if you want to lock only the row or column?

That’s where mixed references come in:

• $A2 → Locks column A but row can change
• A$2 → Locks row 2 but column can change

Example:

In a multiplication table, where you want the top row and left column to stay fixed:

A	B	C	D	E
1	2	3	4	5
2
3
4

Formula in B2:
=$A2*B$1
This locks the row heading (1) and column heading (A).

---

Real-Life Examples

Example 1: Applying a Fixed Bonus to Salaries

Name	Salary	Bonus Rate	Final Salary
John	40000	10%	=B2*$C$2

Here, C2 holds the fixed bonus rate. Use $C$2 so it doesn’t change as you copy the formula down.

---

Example 2: SUMIF with Absolute Reference to Calculate Region Wise Sales Qty and Value

Scenario:

You have a sales transaction sheet with Region, Sales Quantity, and Sales Value.

Raw Data:

Region	Sales Qty	Sales Value
North	100	12000
South	150	18000
North	80	9600
East	90	11200
South	120	15000
North	130	15600

Goal:

Create a summary table that shows total quantity and total value for each region, using a single formula that can be dragged across and down:

Summary Table:

Region	Total Qty	Total Value
North
South
East

---

Formula in B2 (Qty for North):

=SUMIF($A$2:$A$7, $A10, B$2:B$7)

Now, drag right to column C and down to other regions.

---

Explanation:

Part	Description
$A$2:$A$7	Locked criteria range – fixed so it doesn’t change while dragging
$A10	Dynamic region name – locked column (A) but row adjusts
B$2:B$7	Dynamic metric column – column adjusts when dragged right, but row range stays fixed

This allows the formula to:

• Automatically sum Qty or Value based on the column it’s in
• Pick the correct region as per row
• Work as a dynamic matrix formula — write once, drag in all directions

---

Real-Life Use Cases:

• Summarize Sales Qty and Sales Value by region, category, product group
• Compare planned vs. actuals
• Create MIS summaries for multiple KPIs side-by-side

Also Read SUMIF & SUMIFS in Excel

---

Example 3: Fetch Data Using VLOOKUP with Absolute Reference + MATCH

Imagine you have a master table of employee details like this:

Employee Data (Sheet2)

Emp ID	Name	Department	Salary
101	Rohan	Sales	40000
102	Neha	HR	45000
103	Amit	IT	50000

You want to fetch this information into another sheet using VLOOKUP, but write the formula only once and drag it across and down.

Formula Sheet (Sheet1):

Emp ID	Name	Department	Salary
101
102

You can use the formula like this in B2:

=VLOOKUP($A2, Sheet2!$A$2:$D$4, MATCH(B$1, Sheet2!$A$1:$D$1, 0), 0)

Explanation:

• $A2 → Lock column A so that when dragging across, the employee ID stays the same.
• Sheet2!$A$2:$D$4 → Fully locked range of the employee table (absolute reference).
• MATCH(B$1, Sheet2!$A$1:$D$1, 0) → Dynamically finds the correct column index by matching the column heading like “Name”, “Department”, etc.
• 0→ Ensures an exact match.

Now, drag the formula to the right and down, and it will pull the correct data for each employee without rewriting the formula.

This is a powerful and scalable technique used in MIS reporting, HR databases, and sales records where lookup tables are involved, and multiple columns need to be fetched.

Also Read: VLOOKUP in Excel and Match in Excel

How to Switch Between Reference Types?

In Excel:

• Select your formula
• Press F4 after selecting the cell reference to toggle:
  • A1 ➝ $A$1 ➝ A$1 ➝ $A1 ➝ back to A1

This shortcut saves a lot of time!

---

Summary

Understanding cell references is essential when working with Excel formulas. Here’s a recap:

• Relative Reference (A1) – Changes based on position.
• Absolute Reference ($A$1) – Always refers to the same cell.
• Mixed Reference (A$1 or $A1) – Partially locked.

Tip: Use absolute references when you’re working with fixed values like tax rates, commission %, or constants in formulas.

Final Thoughts

Learning when and how to use relative and absolute references will help you write more powerful, error-free Excel formulas. It’s a small concept with a huge impact—especially in MIS reporting, financial models, and data analysis.

So next time you copy a formula, keep an eye on those $ signs!

FAQs – References in Excel

What’s Next?

In the next post, we’ll learn about the Charts in Excel