OzGrid

Lesson 17 - Excel: Avoid Typing

< Back to Search results

 Category: [Excel]  Demo Available 

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

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References

See also:

Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

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)