Skip to main content
  • Login
Cart
Exceljet logo Exceljet
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
Cart
  • Login
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
  • Login

You are here

Home › Videos › What's an absolute reference?

What's an absolute reference?

Transcript 

An absolute reference is a cell reference that intentionally won't change when a formula is copied. There are many situations where absolute references are helpful. Probably the most common case is where you want to use a value in a specific cell in multiple calculations. This might be an hourly rate, a currency conversion ratio, or a constant of some kind. In cases like this, you need a way to tell Excel not to change a cell reference.

Let's take a look.

Recall from an earlier lesson that copying and pasting the formula in cell D11 to other locations caused the cell references to change.

To illustrate how absolute cell references work, let's convert our formula to use absolute references and try again.

To make a reference absolute, you need to add dollar signs to the address: one to lock the row, and one to lock the column. You can do this manually or using the keyboard shortcut F4.

Let's first convert the relative reference B9 to an absolute reference manually by adding a dollar sign in front of the B and in front of the 9.

Now let's copy and paste the formula to see how cell references are affected. As you can see, the address B9 is now fixed and has not been changed. The relative reference D6 has been changed.

Let's undo and convert the relative address D6 to an absolute address; this time with a keyboard shortcut. Just put the cursor in or next to the reference and press F4.

You'll see dollar signs added to both the column and to the row.

Now both cell references are absolute and won't change when we copy the formula. Let's give it a try.

Notice that we get the same result each time. This is because each copy of the formula is still referencing the exact same cells as the first copy.

To summarize, relative references change when formulas are copied to new locations; absolute references don't.

In the next lesson, we'll look at a situation where this behavior is useful.

Master Excel with practice. Download this worksheet (and dozens more) in our Core Excel course. Learn by doing.

Dave Bruns Profile Picture

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Microsoft Most Valuable Professional Award  

Exceljet Training

  • Excel 101
  • Excel Fundamentals
  • Conditional Formatting
  • Essential Formulas
  • Advanced Formulas
  • Pivot Tables
  • Excel Tables
  • Excel Shortcuts
  • Excel Charts
  • Course Bundles
I absolutely love your method of explaining Excel formulas and tips.
Shane
More Testimonials

Get Training

Quick, clean, and to the point training

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas
Exceljet logo Exceljet
Quick, clean, and to the point

Resources

  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles

About Us

  • About
  • Testimonials
  • Topics
  • Contact
  • Donate

Newsletter Sign-up

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
© 2012-2025 Exceljet. Terms of use
  • Visit our Twitter page