The fill handle is an autofill feature in Microsoft Excel. It is a small green square located in the bottom right corner of the active cell [or a selected range of cells]. This feature is used to quickly copy identical values and fill columns and rows.
The fill handle can save a lot of time and prevent mistakes and typos. If you need to copy data and formulas or fill in a series of dates, texts, numbers, and other data, this feature will help you a lot.
So let’s take a closer look at how to use it.
How to duplicate data using the fill handle in Microsoft Excel
One of the fill handle main tasks is to duplicate/copy the cell contents into multiple cells. You can easily copy plain text, numbers, formulas, or other data using the fill handle option.
- So, all you have to do is highlight the cell you want to copy and drag the selection with the fill handle [in the bottom right corner of the highlighted cell] in any direction.
- This will allow you to quickly fill the data from the highlighted cell into the dragged cells.
Alternatively, you can just double-click on the fill handle located, as in our example, in cell F6. Thus, the entire column up to F10 will be filled with the value “5”, because the adjacent column has data up to E9.
What are the autofill options in Microsoft Excel?
When you drag the fill handle, it recognizes the patterns in the data and fills the list, while giving you some additional options you can use. Once you finish dragging the fill handle with your mouse [or double-click] and fill the list, the AutoFill Options icon appears in the lower-right corner of the list.
When you click this icon, it will give you the following options [depending on the data]:
- Copy cells
- This will copy the first cell across the selected cells.
- Fill series
- This option will fill the selected cells with a sequence/series of values, beginning with the initial cell value.
- Fill formatting only
- This one fills the selected range with the formatting of the initial cell, but not the values.
- Fill without
formatting
- This one fills the selected range with values of the initial cell, but not the formatting.
- Flash fill
- This option finds the patterns from data and fills the list according to it.
As you can see, these options are really handy.
How to autofill text values using the fill handle in Excel
The fill handle option can also recognize text values as part of a series, such as the month names, days of the week, and other texts. These can be abbreviated or full names of months, days of the week, etc.
First, enter the abbreviated or full month or weekday name in the first cell. Then use the fill handle to fill in the other cells as shown below.
This is what it looks like for days of the week.
This is what it looks like for the names of the months.
You can also use the fill handle to autofill other text that contains numbers. Just enter text with a number in the first cell and use the fill handle to autofill all other cells.
How to autofill date using the fill handle in Excel
If you want to autofill dates in a cell range, enter the date in the first cell in any format that Excel recognizes and use the fill handle to drag it down to the cell where you want the date to end.
However, there are additional autofill options for dates that become available when you click on the “AutoFill Options” icon at the end of the autofill range. Here you get four advanced options for dates in addition to the options you saw before:
- Fill Days
- This fills the list with days by incrementing by 1.
- Fill Weekdays
- This fills the lists with weekdays only by excluding Saturdays or Sundays.
- Fill
Months
- This option fills the list with incrementing months while the day remains the same across all cells.
- Fill Years
- This option fills the list with years that increment by 1 while the day and month remain the same.
Similarly, you can also set up the cells to be filled with a number sequence.
Read Also:
- How to use the IMAGE[] function in Excel
- How to make all columns in Microsoft Excel to be the same size
- How to build a bubble chart in Microsoft Excel
How to turn on the fill handle option in Microsoft Excel
If you see that the fill handle isn’t working in your Microsoft Excel document, you can easily turn it on. Here’s what you need to do:
- Click on the File tab and select Options.
- Then select Advanced and check the Enable fill handle and cell drag-and-drop checkbox under the Editing options section.
- Finally, just click OK and close the settings dialog box.
That’s it. As you can see there’s nothing complicated about using the fill handle option in Microsoft Excel. It really saves your time and makes work with excel easier.