SoundBytes - Getting a grip on the Excel fill handle

The monthly e-zine from Net Technical Solutions

Getting a grip on the Excel fill handle

Have you seen the fill handle in the bottom right-hand corner of an Excel cell? Did you know that this useful feature can automatically populate cells with custom lists and formulae?

The fill handle

The Fill handle is the small black spot in the bottom right-hand corner of the active cell. To use the fill handle, position your cursor over the black square until it converts to a cross.

It is now ready for you to click and drag across or down to fill the adjacent cells with a copy of the selected cell, or in the case of ‘list’ items, with the members of that list - because Excel recognises that you are likely to be trying to run a sequence.

Block filling

You can also use the fill handle on ranges of cells. When a block of cells is selected, the fill handle will appear at the bottom right hand corner of the block. Dragging the fill handle will copy the whole block down or across, or in the case of lists, the other members of the list. Also, if the block contains a series of numbers e.g. 1,2,3 then that series will be continued.

Lists and custom lists

When Excel recognises items from a list, it will continue to populate cells as you drag the fill handle down or across accordingly. Some of these are built in - such as days of the week or months or can you add your own ‘lists’ to Excel such as staff names, regions, towns etc.

To add a ‘custom list’ in Excel 2003, choose Tools, Options, and the
‘Custom lists’ tab.
To add a ‘custom list’ In Excel 2007 click the Microsoft Office Button , and then click Excel Options. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.

Then:
1)	In the Custom Lists box, click NEW LIST, and then type the entries in the List entries box, beginning with the first entry. Press ENTER after each entry.
2)	When the list is complete, click Add. The items in the list that you selected are added to the Custom lists box.
3)	Click OK twice.

You will find the days and months already in the ‘Custom lists’ section. To add your own, just type the required entries, in the required order by pressing the Enter key, into the ‘List entries’ box and click the Add button.

Alternatively, you can use the Import button to import a list from a range of cells within your current workbook - just click in the ‘Import list from cells’ text box and select the range of cells that contains the list you want to turn to a custom list. Once set up, the list will be available for all workbooks.

Fill handle right drag

If you use the right mouse button to drag, rather than the traditional left mouse button a handy menu will appear with options which can be very useful.

As an example, we want to set up a series of columns showing the same day and month but changing the year. We have entered the first date, and when we drag the fill handle to the right, it extends the dates but by days rather than years. From this fill menu, we can choose ‘Fill Years’ to only update the years without modifying the days and the months.

(If you are using XP, you can drag with the left mouse button as normal, and then use the ‘Smart tag’ to choose the sort of fill you wish to carry out).

Several different fill options are available. Most are self explanatory, but if you want to set up more complicated series for either numbers or dates you may need to investigate the ‘Series’ option.

Fill handle double-click

This is a very useful feature. If you have a table of data and you want to add an entire column of formulae for example, you can enter the first formula in the top cell of the column, and then double click the fill handle of this cell to automatically copy the contents to the bottom of the table - this can be much easier than manually dragging your cursor down for long tables.

Visit our website

Return to main e-zine

Net Technical Solutions Ltd.
Wesley Chambers,
Queens Road, Aldershot,
Hants, GU11 3JD

Tel: 0845 0034567
Fax: 0845 0034543
E-mail: sales@ntsols.com
Website: www.ntsols.com