"Excel Absolute Cell Reference (Example Demonstration - Part 1)"


Follow the example closely to understand how the Excel absolute cell reference work.

Create a worksheet as shown below:

Excel absolute cell reference worksheet

In the figure above, we can see that we have 18 item prices in the range B3:D8. Our goal in the range E3 to G8 is to increase all the prices by 5%. This means we need to create 18 formulas, one for each price.

In cell E3 you can see we have started to create our formula. Currently we see Relative Cell References in our formula that would be read as "three cells to the left times three cells to the left and eight cells below". But is that really the formula we want? To find out, let's see what happens when we copy this formula.

1. Click in cell E3 and create the formula: "=B3*B11"

2. Enter the formula and then copy it down to cell E4 by clicking on cell E3 and press Ctrl + C (shortcut for Copy), then click on cell E4 and press Ctrl + V (shortcut for Paste).

3. With cell E4 selected, use the F2 key to put the cell in Edit Mode.

4. The F2 key not only puts a formula into Edit Mode, put it also gives us a "color coded cell reference map" called Range Finder. Range Finder helps us to audit our formula and track down errors.

Excel absolute cell reference example

In the above figure, we can see that the formula is not correct. The formula we really need to calculate the new price for the head lamp based on the 45% margin is not "=B4*B12", but instead "=B4*B11".

The Relative Cell Reference, B4, which is "three cells to the left", is correct. But the B12 should be B11!

What happened is that the row reference, 11, moved to 12 even though we did not want it to move. We can see that the green Range Finder rectangle is highlighting one cell below our "Increase In Price number, 1.05". This is not correct.

We really want our formula to always be looking at the number in cell B11. We do not want the "B11" part of our formula to be a Relative Cell Reference. We need the "B11" to be "locked" or "absolute" as we copy the formula to the rest of the cells.

Before we see how to "lock" a cell reference, let's see what happens when we copy the formula, not down, but instead to the right.

5. Click the Esc key to get out of Edit Mode.

6. Hit the Delete key to remove the formula from cell E4.

7. Click in E3 and copy the formula over to cell F3.

8. Click in cell F3 and hit the F2 key to show the formula in Edit Mode.

Excel absolute cell reference example

In the figure above, we can see that the formula is not correct. The formula we really need to calculate the new price for the sofa based on the 50% margin is not "=C3*C11", but instead "=C3*B11".

The Relative Cell Reference, C3, which is "three cells to the left", is correct. But the C11 should be B11!

What happened is that the column reference, B, moved to C even though we did not want it to move. We can see that the green Range Finder rectangle is highlighting one cell to the right of our "Increase In Price number, 1.05". This is not correct.

We really want our formula to always be looking at the number in cell B11. We do not want the "B11" part of our formula to be a Relative Cell Reference. We need the "B11" to be "locked" or "absolute" as we copy the formula to the rest of the cells.

Let's see how to lock our cell reference to make an Absolute Cell Reference.

9. Click the Esc key to get out of Edit Mode.

10. Hit the Delete key to remove the formula from cell F3.

11. Click in E3 and create the formula: =B3*B11

We can see that we have a formula that uses two cell references. We already saw that the Relative Cell Reference, B3, which is "three cells to my left", will work perfectly.

When we copy the formula down one row, the 3 will move to a 4 to give us B4; and when we copy the formula to the right one column, the B will move to a C to give us C3. So that Relative Cell Reference will work perfectly.

But the B11 needs to be locked on B11 when we copy it down across the rows and to the right across the columns. We can lock cell references in formulas by placing $ signs (dollar signs) in front of either the row or column reference.

For us we need the cell reference, B11, to be locked in both directions when we copy the formula across the rows and across the columns, so we need our formula to look like this:

=B3*$B$11

When you have a $ sign in front of both the letter (column reference) and number (row reference), this is called an Absolute Cell Reference.

Why a dollar sign? No reason, they just picked a symbol and used it to designate that either the row or column should be "locked" or absolute throughout the copy action.

When putting $ signs into your cell references, instead of typing them in, you can use the F4 key. When the Insertion Point in a formula is touching a cell reference, the F4 key will add the $ signs to the cell reference. Let's see how this works.

12. With the Insertion Point in a formula touching B11, hit the F4 key.

13. Notice the F4 key puts in two $ signs - one for column B column and one for row 11.

Excel absolute cell reference example

Next we need to copy the formula to the entire range E3: G8. This means we need to copy the formula down and then over. This is a two-step process. Let's see how to do it.

14. To put the formula in cell E3 and keep the cell selected use Ctrl + Enter.

15. The first copy step is to double-click the Fill Handle with the Cross Hair cursor to copy the formula down across the rows.

Excel absolute cell reference example

16. A smart tag may appear after the first copy step, but you can ignore it.

17. The second copy step is to click on the Fill Handle with the Cross Hair cursor and drag to the right across the columns.

18. Notice that we are copying a whole column of formulas with our Fill Handle and Cross Hair trick.

Excel absolute cell reference example

19. When you drag your Cross Hair cursor to the right, you will see a grey rectangle. When the grey-rectangle surrounds the F and G column, let go of the mouse.

Excel absolute cell reference example

20. Click in Cell G8 and hit the F2 key to put the cell in Edit Mode.

Excel absolute cell reference example

In the above figure, we can see that our formula works perfectly. The Relative Cell Reference, D8, is looking "three cells to the left" and the Excel Absolute Cell References is looking at Cell B11.

With our knowledge of cell references and our two step copy process, we made 18 calculations with one formula! That is 18 times faster than doing it individually!


How about one step to replace the 2-step process describe in this tutorial? Yes, you can learn how to apply the Ctrl + Enter keyboard trick to enter (or populate) an entire range of cells with formulas, visit Excel absolute cell reference example - part 2.


Related Topics:

4 Reasons Why Mastering Your Excel 2010 Functions Are So Critical!

How to Create Formulas and Discover the 5 Types of Formulas in Excel 2010?

Knowing the different types of Function Argument in Microsoft Excel

Excel Absolute Cell Reference (Example Demonstration - Part 2)

Excel Relative Cell Reference with Real-live Example Demonstration



Can't find what you're looking for? Try Google Search!

Custom Search


Back to Top

You're viewing the Excel absolute cell reference info page, click here to go back to the Home Page

Add To Your Social Bookmarks: add to BlinkBlink add to Del.icio.usDel.icio.us add to DiggDigg
add to FurlFurl add to GoogleGoogle add to SimpySimpy add to SpurlSpurl Bookmark at TechnoratiTechnorati add to YahooY! MyWeb


Google Search:

Custom Search

Recommended Resources:

Click on the image to view the details of the training/book.
Excel 2010 formulas ebook
Microsoft Excel 2010 Formulas


Excel 2007 ebook
The Ultimate Guide to
Excel 2007!


PowerPoint 2007 ebook
The Ultimate Guide to
PowerPoint 2007




XML RSS
What is this?
Add to My Yahoo!
Add to My MSN
Add to Google


Enjoy This Site?
Then why not use the button below, to add us to your favorite bookmarking service?

Copyright © www.msoffice-tutorial-training.com. All Rights Reserved.