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.

Editor's Picks