Dan Butler's
TNPC Newsletter

Annoyances Alley - Drag-and-Drop Mouse Tricks

by TNPC Staff

Annoyances Alley is a tiny glimpse into the Annoyances series of Office 97 related books from Woody Leonhard, Lee Hudspeth, and T.J. Lee.

This is just one of the detailed tips related to optimization and customization that appears in "Excel 97 Annoyances" published by O'Reilly & Associates (ISBN 1-56592-309-X) and reprinted here with permission.

"Drag-and-Drop Mouse Tricks

When you are working with data that you need to quickly move or copy from one area on your sheet to another, and these areas are reasonably close by, the mouse is your best tool.

Start by selecting a cell or contiguous range of cells, and touch the mouse pointer to any edge of the selection (except the fill handle). The mouse pointer will change from the selection crosshair to the northwest mouse pointer shape when you are right on the selection edge. With this pointer, you can click and hold down the left mouse button and drag the selection to a new location (see Figure 4-38). Notice that the destination coordinates are shown on-screen as you drag the selection.

When you release the mouse button, the selected cells are moved to the new coordinates. Most users are first introduced to this technique accidentally when they have a selection and are trying to select a different range. The mouse touches a selection edge and before they know it they've moved the original selection and are wondering why Excel hates them. This technique is the equivalent of a cut and paste operation.

A word of caution to all the Word users out there... in Word, when you drag-and-drop selected text with the mouse the clipboard is bypassed. This is handy because anything you have captured to the clipboard stays there even though you are moving text around in your Word document (and most Windows applications in general). But in Excel, when you drag-and-drop with the mouse, Excel runs the selected content through the clipboard and the clipboard is cleared at the end of the operation. You can't rely on any content remaining on the clipboard.

If you hold down the Control key while you perform the drag-and- drop in Excel, you will copy the cells instead of move them. The northwest pointer takes on the little plus symbol denoting that you are now doing a copy operation.

Hold down the Shift key when you do a drag-and-drop, and when you release the mouse button, Excel performs an insert paste as opposed to a plain paste. This inserts your selection at the drop point, pushing cells down or right. See Figure 4-39.

Excel displays a gray indicator showing you where the data will be inserted and what the destination address will be. In this example, all the entries shift to accommodate the new location of the Salaries label. If you have data all the way out at the end of the column (or row), this technique won't work, since Excel will never shift data off the worksheet and into the void.

Hold down both the Control and the Shift keys when you drag-and- drop and you'll get a copy operation combined with an insert paste.

Now all you have to do is memorize which key does what when you do a drag-and-drop on your sheet with the mouse, right? Well, you certainly can if you want to. Table 4-3 summarizes the different combinations. (See "Excel 97 Annoyances" p. 125, Table 4-3 "Drag- and-Drop Keyboard Combos" -- Ed.)

There is another technique that gives you the most flexibility and does not require you to memorize any keyboard+mouse combinations.

Just use the right mouse button when you click-and-drag. When you drop the selection you'll get a pop-up menu that lets you choose from a number of options on how to complete the operation. See Figure 4-40. You can decide if you want to move, copy, shift or not shift cells in the destination range, and you can create links (a formula reference back to the source) or a new hyperlink (a shortcut back to the source).

If you open the current workbook in a separate window (Window / New Window) and arrange them both on-screen (Window / Arrange All), you can drag-and-drop between different sheets in the same book. Ditto for going between workbooks. Just get both windows displayed on-screen. Annoyingly enough, you can't drag across a split screen in Excel 97; however, you can in Word."

Annoyances titles are available for order at the Office Annoyances Web site:
http://www.PRIMEConsulting.com/annoyances/
or at the publisher's site:
http://www.ora.com/annoyed/