Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
  • Resources
  • Login

Excel Formula Tricks: Absolute Reference

May 11, 2016 by Tyrone Pernsley

Excel Formula Absolute ReferenceUsing Excel to its fullest potential requires knowing how to use formulas. Knowing the different formulas is a great start, but there are tips and tricks you can use to make formulas work even better. In today’s tip, we will explore one of those formula tricks: Absolute Reference aka absolute cell reference.

What is An Absolute Reference in Excel?

This term refers to how you identify the cells being referenced in a formula. A cell reference would be specific cell identified in a formula. For example, if we wanted to run a sum on the following Sale & Tax:

We would enter the following Sum formula:

=sum(A4:B4)

Absolute Reference Image 1

The A4 references the cell with the sale amount and B4 references the cell with the tax amount to give us our total of $318.

Now, if you wanted to copy this formula to find that totals for all of sales, you can copy and paste it down (or use the AutoFill option as in the following animation):

Excel Formulas - Absolute Reference Gif 1

This tool works because of Relative reference. The A4 and B4 in the formula changes to A5 and B5 on row 5, and so on. In this case, the two cells being referenced change according to their relation to where the copied formula is being pasted.

This works well when the information relative to the formula changes in each instance. But what do you do when one of the cells doesn’t change location?

For example, let’s say you wanted to find the commission in the following table by multiplying the sale amount against the commission rate:

Absolute Reference Image 2

This works in the first instance, but then when you try to copy it down, the totals won’t work:

Excel Formulas - Absolute Reference Gif 2

This is because the cell being referenced for the commission rate changes as it is still a Relative Reference.

To make it an Absolute Reference, you use the $ symbol to frame the cell you don’t want to change. Then, when you copy and paste the formula, the commission rate cell stays absolute while the sale remains relative to the row the formula is on:

Excel Formulas - Absolute Reference Gif 3

In this case, the absolute reference anchors one part of the formula so that it can be copied and pasted with ease. This really comes in handy when you have lots of rows of data!

We here at Learn Excel Now hope you enjoyed today’s tip on Excel’s Absolute Reference. This should improve your formulas.

Like Learn Excel Now? Don’t forget to subscribe to our newsletter and follow us on social media!

Kevin – Learn Excel Now

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In