Absolute Reference in Excel: Definition, Formula, and Examples

How to Use Absolute References in Excel

Using an absolute reference is easy once you've done it a few times. Here's the full process, broken down step by step.

Step 1: Write Your Formula Normally First

Don't worry about locking anything yet. Just write the formula.

Say you want to multiply a price in B2 by a tax rate sitting in A1 =B2*A1.

This works fine for one cell. The problem shows up when you copy it.

Drag this down to B3, B4, B5, and Excel shifts both references at once.

• B2 correctly becomes B3, B4, B5. That's what you want, since each row has a different price.
• A1 also shifts to A2, A3, A4. But your tax rate only exists in A1.

Every row below the first one now pulls from the wrong cell. No error shows up. The numbers just come out wrong.

Step 2: Lock the Cell that Should Not Move

Add a dollar sign before the column letter and before the row number, =B2*$A$1.

Now when you copy this formula:

• B2 still changes normally. It becomes B3, B4, B5 as you move down.
• $A$1 stays exactly the same in every row, no matter how far you copy it.

Step 3: Use F4 Instead of Typing Dollar Signs

Typing dollar signs works, but F4 is faster.

Click on the cell reference inside your formula bar, right where you typed A1. Press F4. Excel adds the dollar signs for you.

Keep pressing F4, and it cycles through four options:

Press	Result	What's Locked
1st	$A$1	Row and column
2nd	A$1	Row only
3rd	$A1	Column only
4th	A1	Nothing

You won't always need both locked. Sometimes just one is enough, especially in tables where a formula moves in two directions.

Step 4: Copy or Drag the Formula

Once the reference is locked, you're ready to copy it.

• Use Ctrl+C and Ctrl+V, or
• Drag the small square at the bottom right of the cell, called the fill handle.

The locked part stays exactly where it is. Everything else adjusts on its own.

Also read: Microsoft Excel Tutorial for Beginners: Complete Step-by-Step Guide

Examples of Absolute References in Excel

The best way to understand absolute references is to see them in action. Below are a few real, everyday examples showing exactly when and why you'd lock a cell.

Example 1: Applying a Fixed Tax Rate

Say you run a small shop. You have a list of product prices in column B, and a fixed 18% GST rate sitting in cell A1.

To calculate tax for each product, you'd write this formula in C2 =B2*$A$1

Then drag it down for every row.

Product	Price (B)	Tax Rate (A1)	Formula in C	Result
Shirt	500	18%	=B2*$A$1	90
Shoes	1200	18%	=B3*$A$1	216
Bag	800	18%	=B4*$A$1	144

Notice $A$1 never changes. Only B2, B3, B4 shift, since those are relative.

Example 2: Calculating Commission

Suppose your company pays a flat 5% commission on every sale. The commission rate sits in cell D1.

Formula in column C =B2*$D$1

• B2 changes for each row, since every salesperson has a different sale amount.
• $D$1 never changes, since it's one fixed rate for everyone.

Example 3: A Simple Multiplication Table

This is where mixed references usually show up.

• Row 1 has numbers 1 to 10 across the top.
• Column A has numbers 1 to 10 down the side.
• You want one formula that fills the entire table.

Formula in B2 =$A2*B$1

• $A2 keeps the column locked to A, but lets the row move as you copy down.
• B$1 keeps the row locked to 1, but lets the column move as you copy sideways.

Drag this one formula across and down, and it fills the entire multiplication table correctly.

Example 4: Converting Currency

Say column B has prices in dollars, and cell F1 has the current USD to INR exchange rate.

Formula in column C =B2*$F$1

Every row pulls the same exchange rate from F1, no matter how many rows you add later.

Want to build a stronger foundation in data before mastering DBMS concepts? Check out the Executive Post Graduate Certificate Programme in Data Science & AI from IIITB, India's #1 Post Graduate Certificate in Data Science & AI, designed to make you a true Data Pro.

Absolute vs Relative vs Mixed References in Excel

Excel gives you three ways to write a cell reference. Each one behaves differently once you copy or drag a formula to other cells. Knowing which one to use is what keeps your formulas correct instead of pulling the wrong numbers.

Here's a quick comparison of all three, followed by a proper explanation of each.

Reference Type	Example	Row Behavior	Column Behavior	What Happens When Copied
Relative	A1	Changes	Changes	Both the row and column shift to match the new cell
Absolute	$A$1	Locked	Locked	Stays exactly the same in every cell
Mixed (row locked)	A$1	Locked	Changes	Row stays fixed, column shifts
Mixed (column locked)	$A1	Changes	Locked	Column stays fixed, row shifts

The Simple Way to Remember This

The dollar sign locks whatever comes right after it.

• Put it before the column letter, and the column stays fixed.
• Put it before the row number, and the row stays fixed.
• Put it before both, and nothing moves at all.

For most everyday spreadsheet work, relative and absolute references are enough. Mixed references become useful once your sheets grow bigger and a formula needs to stay correct while moving both across and down.

Also read: Excel Worksheet for Practice

Common Uses of Absolute References in Excel

Now that you've seen how absolute references work, let's look at where they're used most often in real work, and the mistakes people commonly make while using them.

1. Tax and GST Calculations: When a business applies one fixed tax rate to a whole list of prices, the tax rate cell is locked so it never shifts.

2. Discounts and Offers: A store running a flat 20% discount on every product uses one locked cell for the discount rate, applied across the entire price list.

3. Commission and Bonus Sheets: Sales teams often use one fixed commission percentage for every employee. Locking that cell keeps the formula accurate no matter how many rows get added later.

4. Currency Conversion: When converting a list of prices from one currency to another, the exchange rate is usually kept in one cell and locked, so every row uses the same rate.

5. Budget and Expense Templates: A fixed monthly budget or a fixed interest rate in loan calculations is often locked using an absolute reference, so it applies consistently across every row.

6. Lookup Formulas: Functions like VLOOKUP or INDEX-MATCH often use absolute references for the lookup range, so the range doesn't shift when the formula is copied down.

Also read: Ribbon in MS Excel: Features, Tips, and Customisation

Mistakes of Absolute Reference in Excel

Here's where absolute references show up most often in real spreadsheets.

• Tax and GST calculations: A fixed tax percentage gets applied to a whole list of prices. The tax cell stays locked so it never moves.
• Discounts and offers: A shop running a flat discount, say 20% off everything, locks that one rate and applies it across the full price list.
• Commission and bonus sheets: When every employee earns the same commission percentage, that rate is locked in one cell so it stays accurate as more rows get added.
• Currency conversion: One exchange rate cell gets locked and used to convert an entire column of prices from one currency to another.
• Budget and loan templates: A fixed monthly budget or a fixed interest rate stays locked so it applies the same way to every row in the sheet.
• Lookup formulas: Formulas like VLOOKUP or INDEX-MATCH usually lock the range they're searching through, so the range doesn't shift as the formula gets copied down.

Also read: Data Validation in Excel

Mistakes People Make With Absolute References

Even after learning how absolute references work, small slip-ups still happen. Here's what usually goes wrong.

1. Copying Before Locking the Cell

This is the number one mistake. Someone builds a formula, it works fine in the first cell, but they forget the dollar signs. The moment it's copied down, the fixed value starts sliding into the wrong cells, and every row after the first gives a wrong answer.

2. Locking the Wrong Half

In tables that run both across and down, like a multiplication grid, it's easy to lock the row when the column needs locking, or the other way around. The formula still runs, but the numbers come out scrambled.

3. Locking Things That Shouldn't be Locked

Not everything needs a dollar sign. If a value is meant to change with each row, locking it by mistake freezes it, and the formula just repeats the same answer over and over instead of calculating anything new.

4. Losing track of F4

Pressing F4 cycles through relative, absolute, and both mixed options. Press it one time too many, or too few, and you end up with a lock you didn't actually want, without noticing right away.

5. Never Double-Checking After Dragging

A formula gets dragged down fifty rows, and nobody looks back to confirm it worked. If a reference wasn't locked the way it should've been, the error can sit there unnoticed for a long time.

A Simple Way to Catch These Mistakes Early:

• Before copying anything, ask which value is supposed to stay fixed and which one is supposed to change.
• After dragging a formula, open two or three cells and check the formula bar to see if the lock held where it should.
• Prefer F4 over typing dollar signs by hand. It's quicker and leaves less room for typing mistakes.

Also read: Top 15 Ways to Improve Excel Skills [Actionable Tips]

Conclusion

Absolute references keep one cell fixed while the rest of your formula adjusts automatically. Once you understand how the dollar sign works, and use F4 to apply it quickly, you'll stop running into broken formulas when copying or dragging across a sheet.

Whether it's tax, discounts, currency conversion, or lookups, the rule stays the same: lock what should stay fixed, leave the rest relative.

Want personalized guidance on AI and upskilling? Speak with an expert for a free 1:1 counselling session today.