What is the series feature 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.

This Tutorial Covers:

• What’s this Fill Handle in Excel?
• How to Use Fill Handle in Excel?
• Examples of Using Fill Handle in Excel
  • Autofill Numbers that Increment/Decrement by 1
  • Autofill Weekday Names
  • Autofill Dates
• Auto Fill Options
  • Autofill Options for a List with Numbers
  • Autofill Options for a List with Dates
  • Autofill Options for a List with Day Names
• What if you can’t find the fill handle in Excel?
• Creating Custom Criteria for Autofill

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:

1. Go to File –> Options.
2. Select Advanced from the left pane in the dialogue box.
3. In the Editing Options section, ensure that you have the Enable fill handle and cell drag-and-drop option checked.
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 them 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.

You May Also Like the Following Excel Tutorials: 

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

3 thoughts on “How to use Fill Handle in Excel”

1. mamthas

   April 2020 at 6:47 pm

   thanks

2. Ernie Hollingsworth

   May 2019 at 10:39 pm

   Your the brain…no one else had this answer. Thanks.

3. Deepa Sharma

   May 2016 at 10:31 pm

   If you are looking for time related tip, In this quick tutorial, you will learn how to automatically populate current time http://talkproductivity.xyz/auto-fill-time-cells/

   Where is the series option in Excel?

   Click anywhere in your chart. next to the chart. On the Values tab, check or uncheck the series or categories you want to show or hide. Click Apply.

   Which feature of Excel is used to generate a series?

   The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.

   What is trend in Excel Fill series?

   Fill in a series for a growth trend automatically In a growth series, the starting value is multiplied by the step value to get the next value in the series. The resulting product and each subsequent product are then multiplied by the step value.

   What is the total series in Excel?

   To see the value of the sum we will add it as a series. In the Design ribbon choose Select Data. Clicking on Add will bring up a dialog box where you need to add the series name (Total), followed by the range from which values ​​are drawn (B5: D5). After clicking OK the sum will appear as a new series in the chart.