"Excel Relative Cell Reference (Example Demonstration)"


Follow the example closely to understand how the Excel relative cell reference work. This step-by-step example explains the relative cell reference in details.

Create a worksheet as shown below:

Excel relative cell reference worksheet

In the figure above, we can see a formula sitting in cell D2. This formula will be copied down the column so that we can avoid having to create six separate formulas. That formula is using the cell reference B2.

However, from the point of view of the formula, the formula does not see a B2 cell reference, but instead it sees the relative position "two cells to the left of whatever cell the formula is sitting in (housed in)".

Similarly, the cell reference C2, is not really C2, it is "one cell to the left of whatever cell the formula is sitting in (housed in)". Even though you see the cell reference B2 with our eyes, that is not what the formula sees.

Now, let's prove that when you copy the formula down, the formula does not really see "B2 minus C2", but instead it sees "Two cells to the left minus one cell to the left".

  • Click in cell D2 and create the formula: =B2-C2
  • Then, copy the formula down to cell D3. Click on cell D2 and press Ctrl + C (shortcut for Copy), then click on cell D3 and press Ctrl + V (shortcut for Paste).
  • Put the formula in cell D3 into Edit Mode by pressing the F2 key.
  • Excel relative cell reference example
In the figure above, we have proved that "when you copy the formula, the formula does not really see "B2 minus C2", but instead it sees "Two cells to the left minus one cell to the left."

The formula in cell D3 does not have a B2 or a C2 cell reference. When we copied the formula down, the row reference 2 changed to 3. We can see with our eyes that "=B2-C2" changes to "=B3-C3".

This further proves that the original formula was not "B2 minus C2" because if it were, when we copied it down, it would still say "B2 minus C2".

If you are to become great with formulas, you must read formula just as Excel reads them. The formula "=B3-C3" that sits in cell D3 is read as; "two cells to the left minus one cell to the left. Now let's copy the formula down the whole column.

  • Select Cell D3.
  • Double click the Fill Handle with your Cross Hair Cursor to copy the formula down.
  • Put cell D7 into Edit Mode by pressing the F2 key.

Excel relative cell reference example

In the figure above, we can see with our eyes the formula "=B7-C7", but we know that because these are Relative Cell References and we are copying the formula, the proper way to read this formula is "two cells to the left minus one cell to the left.


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 1)

Excel Absolute Cell Reference (Example Demonstration - Part 2)



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

Custom Search


Back to Top

You're viewing the Excel relative 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.