"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: 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.
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.
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.
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 twostep 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 doubleclick the Fill Handle with the Cross Hair cursor to copy the formula down across the rows.
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.
19. When you drag your Cross Hair cursor to the right, you will see a grey rectangle. When the greyrectangle surrounds the F and G column, let go of the mouse.
20. Click in Cell G8 and hit the F2 key to put the cell in Edit Mode.
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!
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 Reallive Example Demonstration
Custom Search

Google Search:
Custom Search
Recommended Resources:Click on the image to view the details of the training/book.Microsoft Excel 2010 Formulas
The Ultimate Guide to
Excel 2007!
The Ultimate Guide to
PowerPoint 2007


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

 Home  Excel 2007 Tutorials  Word 2007 Tutorials  PowerPoint 2007 Tutorials  Outlook 2007 Tutorials 
 Free Templates  Free Clip Arts  Free Tips & Tricks  Free Keyboard Shortcuts  Office 2003 Books   Excel 2003 Tutorials  Word 2003 Tutorials  PowerPoint 2003 Tutorials  Outlook 2003 Tutorials   Office 2010 Tutorials  Excel 2010 Tutorials  Word 2010 Tutorials  PowerPoint 2010 Tutorials 


