"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.
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 (lightcolored 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
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
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.
8. To verify that the formula is correct, hit the F2 key to put the formula into Edit Mode. 9. The formula looks correct.
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.
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 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 


