"Excel Absolute Reference (Example Demonstration - Part 2)"


This second part of Excel absolute reference example is a substitute for our two step copy process that you has learn in Excel Absolute Cell Reference - Part 1.

In this tutorial, we want to see how to apply the Ctrl + Enter keyboard trick to enter (or populate) an entire range of cells with formulas.

This trick will allow us to enter the formula into all the cells (E3:G8) at one time and will avoid having to use the two step copy process that we used in the part 1. Avoiding the two step copy process can save time!

1. Highlight the range E3:G8.

2. Hit the Delete key to delete all the formulas.

3. Notice that the upper left cell is light colored and the rest of the highlighted cells are a darker color. The light colored cell is called the Active Cell.

Excel absolute cell reference worksheet

In the above figure, we have highlighted the range E3:G8: all of these cells will get the same formula.

Because all the cells get the same formula, we can create our formula in the Active Cell (light-colored cell, namely E3), and use the keyboard shortcut Ctrl + Enter to populate all the cells with the formula.

4. In cell E3 create the formula:

"Three Cells To The Left Times B11"

5. The description I just gave of the formula is the "idea" or "concept" of the formula. This is the way you should think of it when you create formulas like this. Explicitly, here is the formula:

=B3*$B$11

Excel absolute cell reference example

It is important to note that we must build the formula from the point of view of the Active Cell, E3. This means that if the formula has a Relative Cell Reference that is "three cells to the left", the Relative Cell Reference you put in the formula must be "three cells to the left" of the Active Cell.

6. To populate all the cells with the formula from the Active Cell use:

Ctrl + Enter

Excel absolute cell reference example

In the above figure, we can see that the keyboard shortcut Ctrl + Enter populated all the highlighted cells with the formula. Anytime you have a range of cells that will get the same formula, number or word, you can use this method for entering the same item into a range of cells.

When you use the Ctrl + Enter method to enter a formula into a range of cells, you want to verify that you actually entered the correct formula.

To do this, check the lower right corner to see if that formula is correct. If that one is correct, you can infer that all the rest are correct also. One fast way to more from corner to corner in a highlight range is to use the keyboard shortcut Ctrl + period.

7. With the Active Cell in the upper left corner, to move to the lower corner use the keyboard shortcut:

Hold Ctrl and tap the Period key two times.

Excel absolute cell reference example

8. To verify that the formula is correct, hit the F2 key to put the formula into Edit Mode.

9. The formula looks correct.

Excel absolute cell reference example

When learning how to use this Ctrl + Enter trick to populate a range of cells with a formula, remember: it is a substitute for copying. So it is always important when you are creating the formula to think about what cell references are required for the "copying" action.


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?

Excel Absolute Cell Reference (Example Demonstration - Part 1)

Knowing the different types of Function Argument in Microsoft Excel

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 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.