A basic understanding of fill handle in Excel could save you some time and make you more productive.
What’s this Fill Handle in Excel?
‘Fill Handle’ is a tool that you can use to autocomplete lists in Excel. For example, if you have to enter numbers 1 to 20 in cell A1:A20, instead of manually entering each number, you can simply enter the first two numbers and use the fill handle to do the rest.
How to Use Fill Handle in Excel?
Let’s first understand where is the fill handle in Excel. Suppose you have the data as shown below:
Here are the steps to use the fill handle to quickly insert numbers up to 20.
Select the data set. Hover the mouse over the bottom-right edge of the selection, you would see a plus icon appear – Click the left button on the mouse and drag it down. Excel identifies a pattern of the first 2 numbers (an increment of 1) and uses that to fill that entire series.
If you have data in the adjacent column, you can also hover the mouse over the right edge of the selection and double click. It will automatically fill the list to the last cell based on the data in the adjacent column. For example, if I double-click on the fill handle (as shown below), it will fill the column till cell A11 (since there is data until cell B11 in the adjacent column.
Examples of Using Fill Handle in Excel
In the above examples, we saw how to use fill handle in excel to complete the list of numbers (that increment by 1). There are many more situations when a fill handle can automatically detect the pattern and fill the cells. Here are the inbuilt fill handle patterns you can use:
Autofill Numbers that Increment/Decrement by 1
As shown above, you can quickly fill cells when the number increments/decrements by 1.
Note that the fill handle works in both directions. You can either fill down by dragging it down or fill up the dragging it upwards.
Autofill Weekday Names
You can use fill handle in Excel to autocomplete weekday names. It could either be the three alphabets nomenclature (Mon, Tue…) or the full name (Monday, Tuesday…).
Note that it fills the cells with 7 weekdays names and then start over with the same name where you started. For example, in the above example, it starts with Monday, and after Sunday it again automatically inserts Monday.
Autofill Dates
You can easily autofill dates using the fill handle in Excel. Any date format that is recognized by Excel can be used by the fill handle.
Auto Fill Options
While fill handle recognizes patterns and fills the list, it also gives some additional options you can use. As soon you drag the mouse (or double click) and autocomplete the list, you will see the Autofill option icon at the bottom right of the list. When you click on this icon, a list of options becomes available. These options are different for different types of data (numbers, dates, days).
Autofill Options for a List with Numbers
When you use the fill handle to autocomplete a list of numbers, you will see the Auto Fill Options icon at the bottom right. Click on that icon to see additional options.
Let’s go through these options:
Copy Cell: If you select this option, it will simply copy-paste the cells. Fill Series: This is the default option where it fills the series based on the pattern it recognizes. In the example below, it fills the cells with numbers incrementing by 1. Fill Formatting Only: This option only fills the formatting and not the values. Fill Without Formatting: This option fills the cells based on the recognized pattern but does not copy the formatting. In the example, below, when this option is selected, it doesn’t apply the border and color to the filled cells. Flash Fill: This is a new feature available in Excel 2013 only. It deduces the pattern to complete the list based on the values in adjacent cells.
Autofill Options for a List with Dates
In case you are using the fill handle to autocomplete dates, additional options become available in the autocomplete options.
Here are the additional options that become available when working with dates:
Fill Days: It will fill the list with days. In the above example, if you select this option, it fills the cells with dates that increment by 1 (which is also the default fill in this case). Fill Weekdays: It fills the cells with weekdays only, and remove the weekends. Fill Months: It fills the cells with incrementing months. In this case, the day number remains the same but the month numbers change. Fill Years: It fills the cells with incrementing years. In this case, the day number remains the same but the year changes.
Autofill Options for a List with Day Names
when working with day names, there are some additional options that appear (as compared with autofill with numbers).
In this case, you can use the Fill Weekdays option to only show weekday names and not the weekends.
What if you can’t find the fill handle in Excel?
Don’t worry if the fill handle is not working in your Excel workbooks. There is an option to enable it (although it is enabled by default, it may happen that you disable it by mistake). Here is how to enable the fill handle in Excel:
Creating Custom Criteria for Autofill
While the inbuilt autofill option is super helpful, you may want to do more by creating custom lists that can be identified by autofill. You can easily do this by creating custom lists and adding them to Excel. Hope you found this tutorial. Let me know your thoughts by leaving a comment below.
10 Excel Data Entry Tips You Can’t Afford to Miss 10 Super Neat Ways to Clean Data in Excel Spreadsheets How to Autocomplete Abbreviations in Excel Fill Down Blank Cells Until the Next Value in Excel (Formula, VBA, Power Query)
