Understanding Absolute Cell Reference

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about Absolute Cell Reference. An Absolute Cell Reference is a cell reference that contains dollar signs ($) before column letter and row number (i.e. $A$1). The dollar signs ensure that cell $A$1 is fixed when formula is copied down or across using the fill handle. To apply the absolute cell reference, hold down the Fn key and execute F4 once on the keyboard. By default, formulas operate what is called Relative Cell Reference (i.e. A1).

Aside from Relative &b Absolute Cell References, we have what is called Mixed or Partial Cell References such as $A1 and A$1.

Exercise: Albion Fuchs Inc., management decided to give 8% bonuses to each of its employee. The bonus is calculated by multiplied by the Annual Salary for each employee by Bonus percentage in cell L7.

To calculate the Total Package, in cell I6, execute the formula:     =H6*L7

Click Enter and copy down the formula using Fill Handle.

In the caption above, the first employee receives ₦104,000 as bonus. However, we discovered that when we copy the formula down, we have ₦0. Furthermore, cell L6 (which is the original cell that housed the 8% bonus value), is moving relatively. To fix this problem so that cell L6 do not move down, we have to apply absolute cell reference.

To fix this problem: Double click on cell I6 and click anywhere close to cell L6

Execute F4 key once.

Execute CTRL + Enter and copy the formula down

Problem fixed!

To calculate Total Package: In cell J6, executive: =SUM(H17:I17) and copy the formula down


Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *