Software

Five tips for working efficiently with Excel's fill handle

The fill handle is a remarkably useful Excel tool -- and it has quite a few tricks up its sleeve. Here are some techniques everyone should know about (but not everyone does).

Excel's fill handle is the small black square in the bottom-right corner of the selected cell or range. This handle provides several methods of copying values. It's flexible and can almost instantly fill thousands of rows -- you just need to know when to drag, when to click, and when to hold down an alternate key to fulfill its potential.

1: Copy values

Most users learn how to use the fill handle to copy values early on. Select the value you want to copy. Then, grab the fill handle by clicking it and holding down the left mouse key. Drag it in the appropriate direction, as shown in Figure A, and Excel copies the value to the selected cells.

Figure A

Use the fill handle to quickly copy values.

To increment values, enter enough values to distinguish a pattern. For instance, you might enter 1 in A1 and 2 in A2 to fix a pattern that increments by 1 -- 1, 2, 3, 4, and so on. If you enter 1 and 3, the fill handle will increment by 2 -- 1, 3, 5, 7, and so on. Holding down the [Ctrl] key while dragging the fill handle will also force an incremental series.

2: Get drop-down help

Alone, the fill handle doesn't always do what you want. When this is the case, you have two options:

  • Right-click the fill handle and drag to display the context menu shown in Figure B.
  • Left-click and drag to display the context menu shown in Figure C.

Figure B

Not all options are valid for the source value.

Figure C

Determine the copy action after you drag.
If you choose Series, Excel displays the dialog shown in Figure D. You can create just about any series you need via this one dialog.

Figure D

Use the Series dialog to handle series that are more complex.

3: Double-click

Sometimes, the target range is large enough that the fill handle is a bit awkward. Can you imagine dragging the fill handle for thousands of rows? When this is the case, double-click the fill handle instead of dragging it. The rules are simple:

  • When you double-click the fill handle, Excel uses the values in the column to the left to determine how far to copy. Excel will stop when it encounters a blank cell.
  • If there are no values to the left, Excel will use the values in the column to the right.
  • If there are no values to the left or right, this trick doesn't work.

4: Include text

If you enter text with a number, Excel's fill handle will accommodate the word, and in an intuitive manner. For instance, Excel knows that there are only four quarters. If you copy Quarter 1 to five cells, it will repeat the series, as shown in Figure E. If you don't want a series but want to copy the actual text, hold down [Ctrl] while dragging. (The [Ctrl] key forces Excel to do the opposite of its default behavior.)

Figure E

The fill handle created all these series.

5: Insert cells

Hold down the [Shift] key while dragging the fill handle to insert cells. For example, you could select A3:F3, right-click the selection, choose Insert, select an insert option, and click OK. You can also hold down [Shift] and drag the fill handle. The number of rows you drag through determines the number of rows Excel inserts, as shown in Figure F.

Figure F

By dragging, select the number of rows that you want to insert.

More Excel tips

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
debbie.deanr
debbie.deanr

Try this. With data as it appears in Figure B, enter your name in cell D2. Select D2 & double-click the fill handle. Now your name should appear is cells D2 thru D8. Really handy for filling a column with formulas.

debbie.deanr
debbie.deanr

Using Excel 2007 and 2010, I can't get it to work.

Ulodesk
Ulodesk

I have been trying these out for ten minutes and can't understand how the double-click functions. Maybe I don't understand target range. What am I selecting, if anything, first? Can you give an example, please?

jbenton
jbenton

if there are already cells filled below your selection then these will be updated with the current cell until a blank is encountered the other two working scenarios will fill until either the adjacent series runs out or a filled cell is encountered below the clicked cell this is a brilliant feature and always under-used

ssharkins
ssharkins

You must have data in the column to the left or right for double-click to work. Enter 1 in cell A1 and use the fill handle to copy it to A5--you have the value 1 in cells A1:A5. In B1 enter =A1+1. That formula will return 2. With cell B1 selected, double-click the fill handle. it will copy the formula, using the data to the left as its gauge for how many rows to fill. If you stopped at A5, the fill handle stops copying the formula at B5. Let me know how that goes.

pirateeye
pirateeye

Try this. With data as it appears in Figure B, enter your name in cell D2. Select D2 & double-click the fill handle. Now your name should appear is cells D2 thru D8. Really handy for filling a column with formulas.

Editor's Picks