Tame your computer: get repetitious


Software training expert Karen Roem offers handy tips to help you 'Tame your computer' in a weekly series for Cambridge Network members .

This week she describes a way of quickly repeating a formula down a column in Microsoft Excel* ...

A few weeks ago I told you about the shortcut many course participants mention ... the if you missed it.

It's also one of of my favourites (as well as one of my current co-presenter's, as I spotted earlier this week) but to be honest there is one even better ... double-clicking the 'fill handle' in Excel.

You've probably used Excel's fill handle (the small black square in the lower-right corner of a cell) to copy formulas into adjacent cells, by dragging the fill handle over the empty cells that you want to autofill. However,

Did you know ...?

You can use the fill handle to automatically enter the formula in the whole column as long as cells in the adjacent column contain data.

Here's how:

1. Enter the formula as normal.

2. Point to the fill handle (the small black square in the lower-right corner of the cell) containing the formula (the mouse pointer will appear as a thin black cross when it is properly positioned).

3. Double-click the fill handle.

Excel will automatically copy, paste, and calculate the formula (including any formatting) down the column and will stop at the first blank cell.

For example, if you want to calculate a person's age based on a list of dates of birth you have in column A:

1. Enter the dates in cell A1, A2, A3, A4, A5 etcetera.

2. Select the cell in which the age is to appear (say, B1) and enter the following formula: =DATEDIF(A1, TODAY(), 'y').

3. Press ENTER.

4. Select cell B1. 4. Double-click the fill handle in the lower-right corner of cell B1.

Excel will automatically fill column B with the calculated age based on the date of birth in column A and stop at the first blank cell.

  • Karen Roem is running a special training session on 'Excel with Excel' in Cambridge on Thursday, 20 October - discounted for Cambridge Network members. More details from:

    * Unless stated otherwise, these tips were written for Microsoft Office 2000. Most of what is covered however will also apply to earlier and later versions.

    Roem logo

    30 June 2005

    Karen Roem offers software training and support through her company Roem Ltd. Contact her by email Karen@roem.co.uk or visit her website at www.roem.co.uk.

    Karen Roem
    Microsoft Office-ionado

    Roem Ltd – software training and support