Introduction
Most Excel users will be familiar with the fill handle and many of its straightforward uses, but there are some less obvious but very useful techniques.
Grab the handle
First of all, to use the fill handle, you need to position the cursor over the black blob at the bottom right hand corner of the selected cell, or block of cells. You know when you've got it in the right place because the cursor changes from a hollow cross to a solid cross:
You can then drag the fill handle to other cells. What happens when you do so depends on the contents of the original cell or cells. A formulae or single number will just be copied, as will text unless it is part of a built-in or custom 'list' in which case the list will be extended. Text that begins or ends with a figure will create a series – so 'Year 1' will be extended to 'Year 2', 'Year 3' and so on. You may find dragging dates disappointing. If you enter a month end date, such as 31 Jan 08 and then drag the cell's fill handle, you might expect month end or even year end dates. In fact Excel just creates a series by days – 01 Feb 08, 02 Feb 08 and so on.
Options
This is not the end of the story. If you are using Excel XP or later, after dragging the fill handle an Excel 'smart tag' will appear next to the final cell. Click on this to see a range of options that again depend on the contents of the first cell. In the case of a date or time the list will include such items as Fill Months and Fill Years. Click on the option that your require
Even after you choose an option the smart tag will remain visible giving you the chance to click on it again to choose a different option.
A right drag
If you are using Excel 97 or 2000, all is not lost, although smart tags don't exist in these versions, you can drag the fill handle with the right mouse button. When you release the button a similar list will appear to the smart tag list, although you do only get one go with this method.
Next time we'll look at some other uses of the fill handle including deleting, formatting, setting up your own lists and the fill handle tip that is widely acknowledged as being the best Excel tip in the world.