Excel's 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.
These instructions apply to Excel for Microsoft 365 and Excel versions 2019, 2016, 2013, and 2010. The fill handle works in conjunction with the mouse. Fill handle uses include:
Try this easy example within your own Excel spreadsheet.
When you copy data with the fill handle, by default, any formatting applied to the data is copied as well. You can 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. Selecting the AutoFill Options button opens a list of options that include:
Selecting Fill without formatting will copy data with the fill handle but not the source formatting. Formulas copied using the fill handle will automatically update to use data in their new location if you created them 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. As an example: Instead of entering the actual numbers into the formula in H1 to create this formula, = 11 + 21use cell references instead, and the formula becomes: =F1 + G1In both formulas, the answer in cell H1 is 32, but because the second formula uses cell references, you can copy it using the fill handle to cells H2 and H3, and it will give the correct result for the data in those rows. 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 twos, that you want to use. Here is a prime example of Excel's auto-fill feature:
Excel also has preset lists of names, days of the week, and months of the year, that can be added to a worksheet using the fill handle. As an example, here is how you might add the days of the week within your worksheet.
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.
Excel also allows you to add your own lists of names such as department names or worksheet headings for use with the fill handle. You can add a list to the fill handle by typing in the names manually or by copying them from an existing list in a worksheet.
If you are looking to import custom autofill lists based on the content on your spreadsheets, follow these steps for a more hands-on approach:
Thanks for letting us know!
Tell us why! |