Introduction

This time we're continuing our look at some of the wonderful things that can be done with Excel's fill handle.

Clear a cell

You might not have spotted this one before. If you drag the fill handle back over the selected cell or block of cells rather than extending outside the range, the cell or cells will be 'greyed' out, and when you release the mouse the contents of the cells will be cleared:

To format or not to format

When you drag the fill handle and click on the smart tag, or when you drag it with the right mouse button, you should find the list of options includes the ability to Copy Cells, Fill Formatting Only and Fill Without Formatting. Copy cells copies both the contents and format of the original cell. The other two options allow you (not surprisingly!) to either just copy the formatting, or copy the contents without copying the formatting:

Lists

If you go to the Tools option on the Excel menu and select Options, then the Custom lists tab (Excel 2007: Office button, Excel Options, Popular, Edit Custom Lists… button) you can see any lists already available in Excel. If you type an item from a list into a cell, you can then use the fill handle to extend the list to include the other items in adjacent cells. You can also create your own lists, so if you frequently need to refer to the same list of individuals or office locations for example. You can type the list directly into the 'List entries:' box, separating each item with a comma or pressing 'Return' or 'Enter' after each item and then click the 'Add' button. Alternatively, if you already have the list in a block of cells in a worksheet, you can select that block of cells before choosing Tools, Options, Custom Lists. You can then click on the Import button to turn the contents of those cells into a custom list.

Getting to the bottom of things

One of the most irritating things that Excel can inflict upon you is the galloping selection problem. Many of us will have experienced 'scroll rage' when aiming to extend a selection to cell C127 and ending up on cell C4562 before overshooting back to C15 on the way back and then bouncing back to C589 and so on. This can happen when trying to copy a cell to the bottom of a block of cells using the fill handle. If there are no gaps in the list then an alternative approach is to double-click on the fill handle. In our example we need to copy cell C2 down to cell C1511. We've used Window, Split so we can see the top and bottom of our list. Double-clicking on the fill handle of C2 should copy the formula all the way down the list to row 1511.