Excel's Powerful Copy Tool—The Fill Handle

A Shortcut Method for Copying Data to Contiguous Cells

Aug 4, 2009
Joe Nowak

Fill handle - Joe Nowak

The Microsoft Excel Fill Handle will enable the quick copying of values, formulas, sequential labels and more. Use of this tool will lead to faster spreadsheet creation.

The Microsoft Excel fill handle provides a variety of ways to copy data within a spreadsheet.

Before the electronic spreadsheet came along, ledgers that held budgets and scientific tables used repetitive data and formulas. In a budget this could be something as simple as entering $456.00 each month for a car payment.

With each entry the risk of human error exists. Along comes the electronic spreadsheet and now those repetitive entries can be made by the computer with a copy and paste command used by almost all software applications.

Basic Use of the Fill Handle

Taking the example above of listing a car payment of $456.00/month for an entire year, entering the number 12 times lends itself to an accidental transposition error and entering $465.00 for a month. To avoid this problem and quickly enter the data for the twelve month period follow the steps below.

Enter the correct number into the first cell (example C3 contains $456.00). Be sure to hit the Enter key or click the check mark next to the formula bar
Make sure C3 is the active cell. It will have a darker or different color border than the other cells.
In the lower right corner of this border you will see a small black dot. Place the pointer on this dot and the pointer will change into a thin black plus sign.
Press and hold down the left mouse button and drag down another eleven cells and release the mouse button.
The $456.00 appears in the selected cells.

The fill handle will work the same way with formulas and functions. It treats absolute and relative references in formulas just like using a standard copy and paste operation. This method can be used to copy data in rows or columns.

Fill Handle is Quick

You created a budget for the coming year. Each of the 12 months will be a row heading. Budget categories will represent column headings. The raw data is entered for all twelve months and now you want to sum each row.

Use the autosum button to add the elements of January.
Place your pointer on the fill handle.
This time don’t press and hold. Instead perform a double click.
Excel will copy the sum function or formula down automatically. It stops when it finds a blank cell on the left and right.

This can only be used to copy data vertically. The drag and release method must be used for horizontal fills.

Fill Handle is Smart

Not only does the fill handle recognize absolute and relative references, it also knows things.

Type January into a cell.
Place the pointer on the fill handle and drag down or across.
February, March, April and the other months appear in order.

The same happens with the days of the week.

These are a few of the copy and paste techniques available when using the fill handle. Using these copy and paste techniques will make your spreadsheet use faster and more accurate.