The CTRL key. That dull, grey-beige looking key that lives an unassuming, lonely life on the edge of your keyboard actually leads an exciting double life in Excel. Now, this is not going to be a life changing experience for you but it may bring a touch of fun into otherwise unexciting everyday tasks.
Copying and Moving
Copying and Moving Worksheet Cells
 |
| Drag and Drop |
Cut and Paste is most peoples's favourite method of moving data from one cell to another. A simpler alternative is Drag and Drop; point to any of the cell's borders, wait for the arrow pointer to display and then drag your data to another cell.
If you want to drag your selection onto another worksheet, drag down to the sheet tabs and then hold down the ALT key to switch over to the other worksheet.
If you want to drag your selection onto another worksheet, drag down to the sheet tabs and then hold down the ALT key to switch over to the other worksheet.
 |
| Copying cell data |
To change your move into a copy (Copy and Paste), hold down the CTRL key as you drag. You should see that a plus sign is displayed next to the arrow pointer. Keep your CTRL key held down until you have released the mouse button.
Cell drag and drop is usually enabled. Should you find that the arrow pointer does not display then you need to turn it on. The settings is in Excel Options (File tab), Advanced section, Editing options: Enable fill handle and cell drag-and-drop.
Copying and Moving Columns and Rows
 |
| Moving a column |
Drag and Drop works in exactly the same way for moving or copying entire columns or rows. Just click the column letter or row number first to select it and then point to the edge of the selection. Drag to move or CTRL+Drag to copy.
Copying and Moving Worksheets
 |
| Copying Sheets |
You've already figured it out; the drag and drop method is equally effective for copying and moving entire worksheets. Point to the sheet tab and click, the document icon will display. Now drag to the left or right hand side to move the worksheet into a new position in the workbook.
To make a copy of an entire worksheet, point to the sheet tab and click. When the document icon displays, hold down the CTRL key and you will see a plus sign displayed on the icon. Now, drag the sheet to the left or right hand side to create a copy. Try doing that with Copy and Paste.
 |
| A copied sheet |
On some keyboards you need to hold down the CTRL first before clicking the sheet tab.
 |
| Copying worksheets from one workbook to another |
And the Shift Key
 |
| Hold down SHIFT as you drag... |
When you drag and drop one cell onto another you usually overwrite the data in the destination cell or range of cells.
But when you hold down the SHIFT key as you drag then you insert cells into the destination range without overwriting data.
But when you hold down the SHIFT key as you drag then you insert cells into the destination range without overwriting data.
 |
| ...to shift cells down |
Watch the I-beam as you drag, the vertical I-beam means Shift cells right and the horizontal I-beam means Shift cells down.
In the illustrations we took the 450 cell value over to the right and dragged it into the E5 cell position and the rest of the cell range moved down to accommodate the data insertion.
In the illustrations we took the 450 cell value over to the right and dragged it into the E5 cell position and the rest of the cell range moved down to accommodate the data insertion.
Filling Data with the Fill handle
Numbers
 |
| Number sequence |
The Fill handle is that little black cross-shaped mouse pointer that appears at the lower right corner of the active cell. Not unreasonably, it is popularly known as the "little black cross". When you drag the Fill handle down or across you fill the data from the active cell across or down: formulas are copied, numbers are copied as constants and text stored in an Custom list, such as days of the week, triggers the list sequence.
When you hold down the CTRL key as you drag you will see a plus sign displayed on the black cross and this reverses the normal behaviour of the fill. For example, if you drag a cell containing the value of 1 then the number 1 will remain constant but if you hold down the CTRL key then you will generate the sequence of values: 1,2,3,4 etc.
When you hold down the CTRL key as you drag you will see a plus sign displayed on the black cross and this reverses the normal behaviour of the fill. For example, if you drag a cell containing the value of 1 then the number 1 will remain constant but if you hold down the CTRL key then you will generate the sequence of values: 1,2,3,4 etc.
Text
 |
| Custom Lists |
Normal text is copied as a constant but Custom list text is entered in the order of the custom list. Custom lists can be very handy; for example you type "March" into a cell then you drag to enter the other months of the year.
But this can be irritating if you wish the month name to remain constant. This is where you hold down the CTRL key to reverse the usual action. Type in "March" and then CTRL+Drag to enter "March" into the cells below.
Excel Custom lists can be viewed, edited and set up in Options (File tab), Advanced section, scroll down to General and click the Edit Custom Lists control.But this can be irritating if you wish the month name to remain constant. This is where you hold down the CTRL key to reverse the usual action. Type in "March" and then CTRL+Drag to enter "March" into the cells below.
Repeating Patterns
 |
| Repeating |
Sequences of numbers may be generated by creating a seed pattern in a few cells and then selecting this seed pattern and dragging to extend the sequence. For example a plus 10 sequence is seeded by typing 10 and 20 into consecutive cells, then you select the two cells and drag to generate the sequence 10,20,30,40,50 etc.
Repetitions of the seed pattern are obtained by holding down the CTRL key as you drag. The sequence of values is then repeated; 10,20,10,20,10,20 etc.
Repetitions of the seed pattern are obtained by holding down the CTRL key as you drag. The sequence of values is then repeated; 10,20,10,20,10,20 etc.
Text values can be treated in much the same way, enter and select an initial pattern of text and then CTRL+Drag to create repetitions of the original. In the illustration below, we wanted the headings "Budget" and "Estimate" repeated across the top of a financial report. Instead of Copy and repeated Pastings the headings were dragged across to the right with the CTRL key held down.
 |
| Repeating Headings |
Making Selections
Cells
 |
| CTRL key for multiple selection |
A multiple selection of cells is where you select one range of cells and then you hold down the CTRL key and keep it held down as you continue to add to your initial selection and create a collection.
If you make a mistake then there's nothing to be done other than to click a cell to clear your current selection and start over again.
This sort of selection is so useful for cell formatting as you can apply your formats to the entire selection in one fell swoop instead of having to format each range individually and invaluable where you want to plot cell ranges which are not immediately adjacent to each other on an Excel chart.
Columns and Rows
 |
| Adjusting the width of alternate columns |
With multiple rows selected you can apply actions such as deletion to all the selected rows or with multiple columns selected, as in the illustration, you can change the width of alternate columns in your worksheet. The more you use collections the more ideas you will have. Not all actions can be applied to a multiple selection but it is always worth experimenting.
Worksheets
In the illustration below we coloured alternate sheet tabs red in one go by holding down the CTRL key and clicking on the relevant sheet tabs. Right-click any selected tab to access the Tab Color command.
 |
| Colouring alternate sheet tabs |
And the Shift Key again
 |
| SHIFT key for block selection |
This feels very awkward if you never tried it before as you can not stop yourself from dragging but if you practice the technique on small ranges of cells then you will rapidly master it. And then you can apply in so many different situations: selecting huge ranges of cells, ranges of sheet tabs etc.
 |
| Click, SHIFT, Click |
Extend Mode
A final word on multiple selection, using your keyboard. The CTRL key with a normal mouse is the easiest thing in the world, you hold the key down with your left hand and select with your right hand. But left-handers don't always have such an easy time of it and some touch pads can make you wish that you had a third hand. This is where you need Extend Mode with the keyboard.Press function key F8 to open extend mode and then select your first cell range either using your mouse or by using your keyboard: hold down the SHIFT key and extend the selection by pressing your arrow keys.
 |
| Extend Selection |
I can assure you that this is a lot easier to do than it is to read about it.
No comments:
Post a Comment