 |
|
|
AVOID TYPING IN EXCEL
In all the previous examples we have always said to type in the reference to a particular cell. While this is fine when typing in very simple references it is completely unnecessary. What Excel allows us to do is to use the mouse pointer to type the reference for us. This is known as the click and point method. I strongly suggest forming this habit early on as good habits are as hard to break as bad ones! To see what we mean try this simple example:
- Delete the contents of cells A1:B10 and cell D1.
- Type any data into cell A1.
- In cell B1 type = and move your mouse pointer outside of B1.
- Place it over cell A1 and left click then push Enter.
You will see that Excel has placed the cell address =A1 in our cell for us. Using this method you are much less likely to make mistakes. From now on this is the method we will use in all examples.
There is one other way we can create a reference to another cell and this is via the Paste Link button on the Paste Special dialog box. We can see this method by simply selecting any cell then copying it and selecting the cell we wish to create the reference in, right click and select Paste Special then click the Paste Link button. If you look in the Formula bar you will see that Excel has created an absolute reference to the copied cell.
Toggle Through Absolute and Relative References
Another good habit to form is to use Function key 4 (F4) to toggle through absolute to relative references. This again will save typing and help prevent errors.
Try this
- In cell B2 type = and then move your mouse pointer over cell A1 then select it.
- Now click within the Formula bar so the mouse insertion point is either
- immediately before A1.
- Between the A and the 1.
- Immediately after A1.
- Now push the F4 button and your reference should change to absolute i. e. $A$1
- Push F4 again and you will get a relative column absolute row reference i. e. A$1
- Push F4 again as you will get an absolute column relative row reference; ie. $A1
- Push F4 again and we will return to our original relative reference; ie. A1.
So as you can see, by pushing F4 we can toggle through relative to absolute reference easily.
Referencing Other Worksheets
So far we have looked at how to reference a cell on the same Worksheet, but it is common to reference cells on other Worksheets. This method is again made very simple by using the point and click method. To reference a cell on another Worksheet do this:
As you will see, Excel will place in the Sheet name and the cell. If you select the cell containing the reference and look in the Formula bar you will see a reference similar to: =Sheet2!C7. Note Excel uses the ! (Exclamation mark) after the Worksheet name, this is how Excel knows that that Sheet2 is the name of a Worksheet.
Go To Free Excel Training Lesson 18 . Back to Previous Lesson
Go to Excel Basic/Level 1 Training Index
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139. 00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help
