Software training expert Karen Roem offers handy tips to help you 'Tame your computer' in a weekly series for Cambridge Network members .
Tame your computer: get repetitious
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.
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.
* 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.
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.