Back To Basics: Using Absolute References in Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel uses a powerful vector system for managing formulas. Each cell can be linked to many others using formulas. In a simple way of explaining this, 2 cells can be added together in a third cell using a formula that references them. The columns are named alphabetically and the rows numerically.

Thus the first cell is known as A1.

Here are two numbers in cells.

imageimageIt is easy to create a formula that adds 2 cells, E.g., =A1+A2. Entering this formula in cell A3 effectively sums the first 2 cells into the 3rd cell.

 

 

Now lets show these numbers as percentages of the total. This can be done by dividing the first cell into the total like thus: =A1/A3. Lets put this formula into cell B1. This gives a result of 36%. However if we copy this cell B1 to B2 we encounter the concept of relative cell addresses. A1/A3 copied down one cell becomes A2/A4. While we want A1 to become A2, we don't want to change A3 to A4. This is because when cells containing links to cells in the formulas are copied, they convert the cell references relative to where they came from.

imageimageimage

An absolute reference on the other hand never changes when it is copied.

If we were to make the A3 reference absolute, when we copy the cell it will remain as A3. This is done by using $ signs in front of the Row and Columns.

 

More advanced understanding of Absolute References.

In actual fact the absolute references can be performed in 2 dimensions. Thus we can 'lock' references in either a row or a column without effecting the other. This is useful when creating formulas in tables. Here is what Excel Help has to say on this:

For a formula being copied:image

If the reference is:

It changes to:

$A$1 (absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) column and absolute row) $A$1

A$1 (relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row)

C$1

$A1 (absolute column and relative row)

$A3

A1 (relative column and relative row)

C3

 

Last little trick.

I love keyboard shortcuts. In this case when editing a cell that contains a formula reference, E.g., F22, make sure the cursor is somewhere on the cell reference and then click F4 key. This will cycle you through all the possible options, $F$22, F#22, $F22, F22.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Anonymous's picture

woo thats all i can say

woo thats all i can say everything that he is saying its like another language to me i have tryed to understand but im just like seen with a wuestionmark on my for head