"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 (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:
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.
Recommended Resources:Click on the image to view the details of the training/book.
Microsoft Excel 2010 Formulas
The Ultimate Guide to
The Ultimate Guide to
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 |