Excel Fill Handle

Copy Data, Formula, Formatting and more with the Fill Handle in Excel

excel-2013-fill-handle.jpg
Copy Formulas with the Fill Handle. © Ted French

Fill Handle Overview

The fill handle is a multipurpose, small black dot or square in the bottom right corner of the active cell that can save you time and effort when it is used to copy the contents of one or more cells to adjacent cells in a worksheet.

Its uses include:

  • copying data and formatting
  • copying formulas
  • filling cells with a series of numbers - such as odd or even numbers
  • adding the days of week or month names to a worksheet
  • adding custom lists of commonly used data - such as department names or report headings - to a worksheet

Working the Fill Handle

The fill handle works in conjunction with the mouse. To use it:

  1. Highlight the cell(s) containing the data to be copied or, in the case of a series, extended
  2. Place the mouse pointer over the fill handle - the pointer changes to a small black plus sign (+)
  3. Press and hold down the left mouse button
  4. Drag the fill handle to the destination cell(s)

Copying Data without Formatting

When data is copied with the fill handle, by default any formatting applied to the data - such as currency, bold or italics, or cell or font color changes; are copied as well.

To copy data without copying the formatting, after copying data with the fill handle Excel displays the Auto Fill Options button below and to the right of the newly filled cells.

Clicking on this button opens a list of options that include:

  • Copy cells
  • Fill formatting only
  • Fill without formatting

Clicking on Fill without formatting will copy data with the fill handle but not the source formatting.

Example: Copying Formatted Data with the Fill Handle

  1. Enter a formatted number - such as $45.98 - into cell A1 of the worksheet
  2. Click on cell A1 again to make it the active cell
  1. Place the mouse pointer over the fill handle (small black dot in the bottom right corner of cell A1)
  2. The mouse pointer will change to a small black plus sign ( + ) when you have it over the fill handle
  3. When the mouse pointer changes to the plus sign, click and hold down the mouse button
  4. Drag the fill handle to cell A4 to copy the number $45.98 and the formatting to cells A2, A3, and A4
  5. Cells A1 to A4 should now all contain the formatted number $45.98

Copying Formulas with the Fill Handle

Formulas being copied using the fill handle will update to use the data in their new location if they have been created using cell references.

Cell references are the column letter and row number of the cell where the data used in the formula is located - such as A1 or D23.

In the image above, the cell H1 contains a formula that adds together the numbers in the two cells to the left.

Instead of entering the actual numbers into the formula in H1 to create this formula,

= 11 + 21

cell references are used instead and the formula becomes:

=F1 + G1

In both formulas, the answer in cell H1 is: 32, but the second formula, because it is created using cell references it can be copied using the fill handle to cells H2 and H3 and it will give the correct result for the data in those rows.

Example: Copy Formulas with the Fill Handle

This example uses cell references in the formulas, therefore all cell references in the formula being copied will update to reflect their new location.

  1. Add the data seen in the image above to cells F1 to G3 in a worksheet
  1. Click on cell H1.
  2. Type the formula: = F1 + G1 into cell G1 and press the Enter key on the keyboard
  3. The answer 32 should appear in cell H1 (11 + 21)
  4. Click on cell H1 again to make it the active cell
  5. Place the mouse pointer over the fill handle (small black dot in the bottom right corner of cell H1)
  6. The mouse pointer will change to a small black plus sign ( + ) when you have it over the fill handle.
  7. When the mouse pointer changes to the plus sign, click and hold down the left mouse button
  8. Drag the fill handle to cell H3 to copy the formula to cells H2 and H3.
  1. Cells H2 and H3 should contain the numbers 72 and 121 respectively - the results of the formulas copied to those cells.
  2. If you click on cell H2 the formula = F2 + G2 can be seen in the formula bar above the worksheet
  3. If you click on cell H3 the formula = F3 + G3 can be seen in the formula bar

Adding a Series of Numbers to Cells with the Fill Handle

If Excel recognizes the cell contents as part of a series it will auto fill other selected cells with the next items in the series.

To do so, you need to enter enough data to show Excel the pattern - such as counting by two's that you want to use.

Once you have done this, the fill handle can be used to repeat the series as often as needed.

Example Using the Fill Handle to Add a Series of Data:

  1. Type the number 2 in cell D1 and press the Enter key on the keyboard
  2. Type the number 4 in cell D2 and press Enter
  3. Select cells D1 and D2 to highlight them
  4. Click and hold down the mouse pointer on the fill handle in the bottom right corner of the cell D2
  5. Drag the fill handle down to cell D6
  6. Cells D1 to D6 should contain the numbers : 2, 4, 6, 8, 10, 12

Adding the Days of the Week with the Fill Handle

Excel has preset lists of names - the days of the week and the months of the year that can be added to a worksheet using the fill handle.

To add the names to a worksheet, you just need to tell Excel which list you want added and this is done by typing the first name in the list.

To add the days of the week for example,

  1. Type Sunday into cell A1
  2. Press the Enter key on the keyboard
  3. Click on the cell A1 again to make it the active cell
  4. Place the mouse pointer over the fill handle in the bottom right corner of the active cell
  5. The mouse pointer will change to a small black plus sign ( + ) when you have it over the fill handle
  6. When the mouse pointer changes to the plus sign, click and hold down the mouse button
  7. Drag the fill handle to cell G1 to auto fill the days of the week from Monday to Saturday

Excel also contains a pre-set list of the short forms for the days of the week such as Sun, Mon, etc.

as well as both full and short month names - January, February, March and Jan, Feb, Mar that can be added to a worksheet using the steps listed above.

To Add a Custom List to the Fill Handle

Excel also allows you to add your own lists of names - such as department names or worksheet headings for use with the fill handle. A list can be added to the fill handle either by typing in the names manually or by copying them from an existing list in a worksheet.

Typing the new Auto Fill list yourself

  1. Click on the File tab of the ribbon (Excel 2007 click on the Office button)
  2. Click on Options to bring up the Excel Options dialog box
  3. Click the Advanced tab (Excel 2007 - Popular tab) in the left hand pane.
  4. Scroll to the General section of the options list in the right hand pane (Excel 2007 - Top options section at the top of the pane)
  5. Click the Edit Custom List button in the right hand pane to open the Custom List dialog box.
  6. Type the new list in the List entries window
  7. Click Add to add the new list to the Custom Lists window in the left hand pane
  8. Click OK twice to close all dialog boxes and return to the worksheet
  9. Test the new list by typing the first name in the list and then use the fill handle to add the rest of the names to the worksheet

To import a custom Auto Fill list from your spreadsheet

  1. Highlight the range of cells in the worksheet containing the list elements - such as A1 to A5
  2. Follow steps 1 to 5 above to open the Custom List dialog box.
  3. The range of cells previously selected should be present in the form of absolute cell references - such as $A$1:$A$5 - in the Import list from cells box at the bottom of the dialog box.
  4. Click the Import button.
  5. The new Auto Fill list appears in the Custom Lists window
  6. Click OK twice to close all dialog boxes and return to the worksheet
  7. Test the new list by typing the first name in the list and then use the fill handle to add the rest of the names to the worksheet