How to use Fill Handle in Excel

Fill Handle is a feature in Excel that enables you to auto-complete a list in a row/column by simply dragging it using your mouse.

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:

How to Use Fill Handle in Excel - Data

Here are the steps to use the fill handle to quickly insert number 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 – How to Use Fill Handle in Excel - Plus Icon SmallHow to Use Fill Handle in Excel - Plus Icon
  • 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.How to Use Fill Handle in Excel - Drag fill 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 till cell B11 in the adjacent column.

How to use Fill Handle in Excel - Double Click

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 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.

How to use Fill Handle in Excel - increment Decrement

Note that fill handle works in both the 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…).

How to use Fill Handle in Excel - Weekday Names

Note that it fill 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.

How to use Fill Handle in Excel - Dates

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.how to use fill handle in excel - Autofill Option Icon

When you click on this icon, a list of options becomes available. These options are different for different types of data (numbers, date, day).

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.

how to use fill handle in excel - Autofill Options Numbers

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 options only fills the formatting and not the values.
  • Fill Without Formatting: This options 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 Numbers

In case you are using the fill handle to autocomplete dates, additional options become available in the autocomplete options.

How to use Fill Handle in Excel - Autofill Options Dates

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).

How to use Fill Handle in Excel - Autofill Options Days

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 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:

  1. Go to File –> Options.
  2. Select Advanced from the left pane in the dialogue box.how to use fill handle in excel - advanced
  3. In the Editing Options section, ensure that you have the Enable fill handle and cell drag-and-drop option checked.how to use fill handle in excel - Enable Fill Handle
  4. Click OK.
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 it to Excel.

See Also: Creating Custom Lists in Excel for Autofill.

Hope you found this tutorial. Let me know your thoughts by leaving a comment below.

Related Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)