Why Does My Excel Formula Change When I Copy It? Absolute vs Relative References
- Feb 14
- 5 min read
One of the most common frustrations I hear when I deliver Excel training goes something like this:
You write a formula.
It works perfectly.
You copy it to another cell…
…and suddenly the result is wrong.
The numbers change.
The total doesn’t add up.
Or worse, you get an error message.
At that point, many people assume Excel is being unpredictable.
In reality, Excel is being very consistent. It’s just following a rule that most people were never properly shown.
If your Excel formula changes when you copy it, it’s usually because of how cell references work.
Excel Isn’t Changing Your Formula Randomly
When you copy a formula in Excel, the formula itself doesn’t randomly alter.
Instead, Excel adjusts the cell references based on where you paste it.
This behaviour is called relative referencing.
Excel assumes that when you copy a formula, you want it to behave in a similar pattern in the new location.
For example:
If you write:
=A1+B1in cell C1, Excel understands that you’re adding the two cells immediately to the left.
If you copy that formula down to C2 (either by copying and pasting, or by dragging the green square in the bottom right corner of the cell), Excel adjusts it to:
=A2+B2
That’s not a mistake – that’s Excel doing exactly what it’s designed to do.
Most of the time, this is helpful.
It allows you to write one formula and quickly apply it across an entire column.
The problem arises when you don’t want Excel to adjust something.
The Real Issue: Relative vs Absolute References
There are two main types of cell references in Excel:
Relative references
Absolute references
By default, Excel uses relative references.
A Relative reference changes when you copy it to another cell.
An Absolute reference stays fixed, no matter where you copy it.
This is where confusion often begins.
When Relative References Work Perfectly
Relative references are ideal when you’re repeating the same calculation across rows or columns.
You can write one formula, and then copy it hundreds or thousands of times.
For example:
Adding values across multiple rows
Calculating totals for different months
Multiplying price by quantity for multiple products
In these cases, you want Excel to adjust the row number as you copy the formula down.
Relative referencing saves time and prevents manual errors.
When Things Start to Break
Problems usually happen when your formula refers to:
a tax rate
a fixed percentage
a single reference cell
or a lookup table
For example, we want to work out salary increases for the team. Everyone is getting a 5% salary increase which we have written up in cell C3.
To working out the increase, we simply multiply the person's salary by the increase percent.
=C6*C3
That works fine for the first person, but if we try to copy the formula down to everyone else we run into a big problem.
We see that although it works find for Brandon, for everyone else things are not right.
Steven is getting nothing, Evelyn is getting an error, and everyone else is getting extremely large increases - which might be nice but are obviously not correct!
What's happened?
When we copied the formula down, Excel also moved the cell references down.
The cell reference pointing at the salary has correctly moved down to the next person, but the cell reference pointing at the increase has also moved down - which is why Steven is getting nothing (his salary is being multiplied by zero).
Evelyn's salary is being multiplied by text - giving the #VALUE! error.
George, meanwhile, is having his salary multiplied by Brandon's. George might be very happy with his billion-pound increase, but it's probably not right!
Excel hasn’t broken. It’s just applying the relative rule consistently.
How to use Absolute References in Excel
To stop Excel from adjusting a reference when copying, you need to make it absolute.
You do this by adding dollar signs to the reference.
For example:
=C6*$C$3The dollar signs tell Excel to keep that cell reference fixed - no matter where you copy the formula.
Now, when you copy the formula down, C6 will change to C7, C8, and so on, but $C$3 will stay fixed.
The other big advantage of having formulas like this where multiple formulas refer back to one cell is that if you change the Percent Increase number, every one of the Salary Increases will automatically update.
This small change makes a big difference.
A Quick Way to Apply It
You don’t need to manually type dollar signs.
In Excel, you can:
Click into the formula
Put the cursor next to or inside the cell reference
Press F4
Note: on some keyboards, especially laptops, if you press F4 it will adjust the brightness or the volume. You may need to hold down the Function key (Fn) and then press F4.
Excel will cycle through the available reference types each time you press F4.
Relative (A1)
Absolute ($A$1)
Mixed (A$1 or $A1)
For most everyday situations, fully absolute references ($A$1) are what people need when something should stay fixed.
Mixed References (Briefly Explained)
There are also mixed references, where either the row or the column is fixed, but not both.
For example:
$A1 (column fixed, row changes)
A$1 (row fixed, column changes)
These are extremely useful in more advanced spreadsheets, especially when building tables that expand across rows and columns.
If you’re just starting out, though, the key thing to understand is this:
If something should not move when copied, it needs to be locked.
Why This Feels So Frustrating
Many people are self-taught in Excel.
They learn through trial and error, copying formulas from colleagues or online forums.
No one ever sits down and explains how references actually behave.
So when formulas “break”, it feels random and unpredictable. That uncertainty chips away at confidence.
Once you understand how referencing works, Excel suddenly feels much more logical.
Instead of fearing what might happen when you copy something, you can predict it.
That confidence alone makes spreadsheets far less stressful.
A Simple Test to Build Confidence
If you’re unsure how a formula will behave when copied, try this:
Write the formula in one cell.
Before copying it, ask yourself:
Which parts should move?
Which parts should stay fixed?
Lock anything that shouldn’t change.
This quick pause prevents most common reference mistakes.
The Bigger Picture
Understanding relative and absolute references is one of the key Excel skills that unlocks everything else.
Many other frustrations in Excel trace back to this one concept.
When formulas don’t return the expected result, it’s often a referencing issue rather than a calculation problem.
Once this clicks, people often say something like:
“I wish someone had shown me this years ago!”
It’s not a complicated idea. It’s just one that’s rarely explained clearly in the workplace.
Final Thoughts
If your Excel formula changes when you copy it, Excel isn’t malfunctioning.
It’s following a consistent rule about how cell references behave.
Learning when to let references move – and when to lock them in place – gives you far more control over your spreadsheets.
It also removes one of the biggest sources of confusion for everyday Excel users.
Understanding referencing is just one of the core building blocks I cover in my Excel training courses.
The focus isn’t on memorising features, but on building confidence so spreadsheets feel logical rather than unpredictable.
If you’d like to explore that further, check out the outlines for my Excel courses or get in touch for an informal chat about how I could help you or your team.