Excel Cell References – Relative, Absolute & Mixed – A1, $A$1, $A1, A$1

If you work in Excel regularly, you may have seen formulas like these:

 A1:A7

 $A$1:$A$7

 $A1:$A7

 A$1:A$7

At first, they look confusing.

Why does Excel offer so many ways to refer to the same cell?

The reason is simple:

Different reference types give you control over what should change and what should stay fixed.

Excel adjusts cell references when formulas are copied.

This article explains what each reference type means, why it exists, and when to use it.

Why Cell References Matter in Excel?

When you write a formula in Excel and copy it to another cell, Excel automatically adjusts the cell references.

Sometimes this is helpful.

Sometimes it completely breaks your formula.

To avoid this, Excel provides different types of cell references so you can decide:

•  what should move
•  and what should remain fixed

Types of Cell References in Excel:

Excel has three main types of cell references:

• 1. Relative reference → A1
• 2. Absolute reference → $A$1
• 3. Mixed reference → $A1 or A$1

Let’s go through them one by one.

1. Relative Reference – A1 (Default)

This is the default reference used by Excel.

 What it means:

 Both the row and column can change.

 Excel adjusts it when you copy the formula.

Example:

Cell B2 formula:

=A2

Copy it down to B3 → Excel changes it to:

=A3

Excel assumes:

> “You moved one row down, so Ill move the reference down too.”

When to use?

• Row-wise calculations
• Percentages
• Totals per row
• Everyday formulas

Example use case: Total cost calculation

S.NO	PRODUCT	COST	NO OF PIECES	TOTAL COST
1	APPLE	10	1	10
2	BANANA	5	4	20
3	ORANGE	15	2	30

Formula in E2:

=C2*D2

When you copy this formula down, Excel automatically adjusts the cell references:

• In E3, the formula becomes =C3*D3
• In E4, the formula becomes =C4*D4

Each row calculates its own total correctly based on the values in the same row.

This happens because C2 and D2 are relative references, which change automatically when the formula is copied.

2. Absolute Reference – $A$1 (Fully Locked):

An absolute reference is fully locked.

 What it means:

• The column is fixed
• The row is fixed

 The reference does not change when copied

 Example:

=SUM($A$1:$A$7)

No matter where this formula is copied, the range stays the same.

When to use?

• Fixed values (tax rate, discount rate)
• Constants
• Lookup ranges
• Ranking formulas

Practical example: GST calculation

S.NO	PRODUCT	COST	NO OF PIECES	TOTAL COST
1	APPLE	10	2	20
2	BANANA	5		10
3	ORANGE	15		30
		SUB TOTAL		60
		GST	18%	10.8

In this table:

• Cell D2 contains the number of pieces, which is the same for all products.
• Cell D6 contains the GST rate (18%).

Formulas Used:

• Total Cost (in E2):=C2*$D$2
• GST Calculation (in E6):=E5*$D$6

Why Absolute References Are Used?

• $D$2 (number of pieces) stays fixed when the formula is copied down.
• $D$6 (GST rate) stays fixed for all calculations.
• Only the product cost (C2, C3, C4) changes row by row.

When you copy the formula down, Excel changes only what is allowed to change.

 In E3, the formula becomes:

    =C3*$D$2

In E4, the formula becomes:

   =C4*$D$2

The cost value (column C) changes for each row.

The quantity in $D$2 stays the same because it is locked.

This way, Excel calculates the total correctly for every product using the same number of pieces.

3. Mixed Reference – $A1 or A$1 (Partially Locked)

Sometimes in Excel, you don’t want everything to change when you copy a formula.

You want part of the cell reference to stay fixed, and the rest to move.

That’s exactly why mixed references exist.

There are two types:

 $A1 → column stays fixed, row can change

 A$1 → row stays fixed, column can change

Mixed references are useful when a formula needs to work both across columns and down rows.

Let’s look at a simple, real-life example.

Example: Product Price × Daily Quantity:

Imagine you have product prices and different Quantities for each day.

• Product prices are in column B
• Day Quantities are in row 2

 You want to calculate values for each product on each day

Product	Rates	Day 1	Day 2	Day 3
Quantity		80	100	75
Apple	10
Banana	8
Orange	10

Formula (entered in C3):

=$B3*C$2

How This Formula Works?

This single formula uses both types of mixed references.

 $B3 – Price column stays fixed

 Excel always takes the price from column B

 When copied down, the row changes:

   $B3 → $B4 → $B5

This ensures each product uses its own price.

 C$2 – Quantity row stays fixed

 Excel always takes the quantity from row 2

 When copied across, the column changes:

   C$2 → D$2 → E$2

This ensures each day uses the correct quantity.

 What Happens When You Copy the Formula

Copy right (days change):

•  =$B3*C$2
•  =$B3*D$2
•  =$B3*E$2

Copy down (products change):

•  =$B4*C$2
•  =$B5*C$2

Excel automatically picks:

• the correct product price
• the correct daily quantity

 Final Result:

Product	Rates	Day 1	Day 2	Day 3
Quantity		80	100	75
Apple	10	800	1000	750
Banana	8	640	800	600
Orange	10	800	1000	750

 Why Mixed References Are Needed

• Normal references change too much.
• Absolute references don’t change at all.
• Mixed references do exactly what we need.
• They lock only what should stay fixed and allow the rest to move.

Final Understanding:

> Mixed references are needed only when formulas move in two directions.

• Copying only down → mixed references often not needed
• Copying only across → mixed references often not needed
• Copying both down and across → mixed references become essential

One Easy Trick to Remember Cell References Easily:

> The $ symbol locks what comes after it:

Reference	What Stays Fixed	What Changes	When to Use
A1	Nothing	Row + Column	Simple row-wise formulas
$A$1	Row + Column	Nothing	Constants (tax, rate, limits)
$A1	Column only	Row	Compare against one fixed column
A$1	Row only	Column	Header or rate-based calculations

How Excel Behaves When You Copy Formulas?

Reference	Copy Down	Copy Right
A1	Row changes	Column changes
$A$1	No change	No change
$A1	Row changes	Column stays
A$1	Row stays	Column changes

Pro Tip: Use the F4 Key:

Instead of typing $ manually:

1. Click the cell reference

2. Press F4

Excel cycles through:

A1 → $A$1 → A$1 → $A1 → A1

Cell references may look small, but they control how reliable your formulas are.

Once you understand them, your formulas become:

• more accurate
• easier to copy
• safer to reuse

This is one of the most important Excel concepts to learn — and once mastered, it makes everything else easier.