EXCEL: AVOID TYPING

Lesson 17 - Excel: Avoid Typing Whenever Possible. Use Your Mouse Pointer

AVOID TYPING IN EXCEL

In all the previous emamples 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. https://fbskip.com/ To reference a cell on another Worksheet do this:

• Type An = In Any Cell.
• Then Using The Mouse Pointer Click The Name Tab Of Another Worksheet (Sheet2 In This Case).
• Select The Cell You Wish To Reference (C7 In This Case) Then Push Enter.

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.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Go back to:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)