How to Use Fill Handle in Excel (All You Need to Know)

The Fill Handle tool is used to fill or extend data or series in Excel. It is a plus-sign cursor (+).

For example, in the following GIF, we have an Excel series with only two entries: -1 and 2. We want to fill it up to 20.

  • Select the cells and drag them using the fill handle tool.

fill handle in Excel


What Is Fill Handle in Excel?
How Do I Enable/Disable Fill Handle in Excel?
Why Do We Need to Use Fill Handle? When Not to Use It?
How to Use Fill Handle in Excel: 7 Suitable Examples
    ⏵To Fill Data Quickly from Adjacent Cells
        ⏵Fill Data Quickly for Multiple Cells
    ⏵To Fill Down Blank Cells Until Next Value
    ⏵To Copy a Formula to the Left, Right, Up, and Down
    ⏵To Drag Formula Horizontally With Vertical Reference in Excel
    ⏵To AutoFill Number Series in Excel
    ⏵To Insert Patterned List Easily in Excel
    ⏵To Fill Days, Months, or Years Automatically in Excel
Can We Use Fill Handle from the Keyboard?
What to Do If Excel Fill Handle Does Not Work?
    ⏵Turn on Automatic Workbook Calculation
What Are the AutoFill Options (Fill Handle Options) in Excel?
    ⏵AutoFill Options for a List with Numbers
    ⏵Autofill Options for a List with Dates
    ⏵AutoFill Options for Formatting
    ⏵AutoFill Options for Flash Fill
How to Create Custom List for AutoFill in Excel?


What Is Fill Handle in Excel?

The Fill Handle is a time-saving feature that automatically fills cells with values up to a point. Use it for data entry and manipulation.

You can find the icon by moving the mouse cursor to the bottom-right corner of a selection.

fill handle icon in Excel

There are two ways to use the Fill Handle in Excel:

  • You can click on it and drag it to a point to fill in values.

drag method of fill handle

  • You can double-click when the icon changes to a plus sign.

double click method of fill handle

It will give you the same result.

drag method result

Note: The double-clicking method fills the range up to the length of its adjacent range.

The Fill Handle doesn’t work on a Protected Sheet.

Fill handle not working in protected worksheet

Click on the GIF to get a detailed view


How Do I Enable or Disable the Fill Handle in Excel?

  • Click on File from the ribbon.

selecting file tab in Excel

  • Select Options.

selecting options from backstage view

  • Select the Advanced tab in the Excel Options dialog box.
  • Check or uncheck the Enable fill handle and cell drag and drop option depending on whether you want to enable or disable the feature.
enabling fill handle from options

Click on the image for a detailed view

Note: Selecting the Alert before overwriting cells option will help you prevent the replacement of data if you accidentally click and drag the fill handle icon.

Why Do You Need to Use Fill Handle? When Not to Use It?

The Fill Handle is a useful feature of Excel because of its advantages. Some of its advantages include:
– Can autofill patterns.
– Provides a quick method for copying and pasting data.
– Provides efficiency while using formulas, so that we don’t have to manually insert each one.
– Can fill data in all directions by dragging.

However, it also has some disadvantages.
– It is highly dependent on the pattern you have already provided. So, it may require some additional inputs before getting the desired output.
– Can’t fill “irregular” numeric patterns like 1,1,2,2,3,3,…
– Can’t skip cells while dragging the fill handle down.
– Ineffective against data with no patterns, such as text values.
– Can return wrong outputs for formulas when references aren’t put properly.


How to Use Fill Handle in Excel: 7 Suitable Examples


Example 1 – Fill Data Quickly from Adjacent Cells

There are some codes in column B. We want to create a new one in column C, where each code is one number higher than the adjacent one.

dataset of copying code using fill handle

  • Select cell B6 and drag the fill handle to C6 to fill in the next value.

copying cell using fill handle

  • Select cell B7 and drag it to cell C7 to get the next value.

copying second cell using fill handle

  • Repeat for the other cells.

all cells copied using fill handle


Case 2 – Fill Data Quickly for Multiple Cells

Suppose we want to create a table of Day No, Date and Day from the following data. We want the next value in every row.

fill handle for multiple cells dataset

  • Select the range B6:D6.
  • Move your cursor to the bottom-right of the selection until the fill handle icon appears.
  • Click and drag it to the point you want to fill.

filling multiple cells using fill handle


Example 2 – Fill Down Blank Cells Until the Next Value

In the following dataset, we need to fill “Sales” up to cell C10, “Marketing” up to cell C13, and “IT” in cell C16.

dataset for filling down until next value

  • The fill handle copies text values. Select cell C6 and drag the fill handle down to cell C10, and it will fill the range with “Sales”.

dragging fill handle until next value

  • Selecting cell C11 and dragging it to C13 will fill the range with “Marketing”.

dragging second value to copy

  • Doing the same for C15 and C16 will fill the range with “IT”.

dragging all values to copy until next value


Example 3 – Copy a Formula to the Left, Right, Up, or Down

Here is a dataset showing different products sold in different store locations. We want to sum the total sales based on both locations and products. We need to sum them vertically and horizontally for that.

fill handle in all directions dataset

Fill Formula Down:

  • Use the following formula in cell I6:
=SUM(C6:G6)

sum formula for total cells in stores

  • Select the cell again, move your cursor to the bottom-right of the selection, and drag the fill handle down.

dragging fill handle downward

Fill Formula Up:

  • Use the formula in cell I13:
=SUM(C13:G13)
  • Move your cursor to the bottom-right of the selection.
  • Click and drag up to cell I6.

dragging fill handle upward

Fill Formula to the Right:

  • Use the SUM formula in cell C15:
=SUM(C6:C13)
  • Move the cursor to the bottom-right of the selection until it turns into the fill handle icon.
  • Click and drag the cursor to the right to fill in the formula.

dragging fill handle to the left

Fill Formula to the Left:

  • Use the formula in the rightmost cell (G15).
=SUM(G6:G13)
  • Move your cursor to the bottom-right of cell G15 to get the fill handle icon.
  • Click and drag it to the left to fill in the formula.

dragging fill handle to the right


Example 4 – Drag Formula Horizontally With a Vertical Reference in Excel

For the same dataset of product sales in different store locations, let’s say we want to find out the sales amount of each item in “Rock Island”.

dataset for dragging horizontal formula with vertical reference

  • Insert the following formula in cell C16:
=VLOOKUP($B$16,$B$6:$G$13,COLUMN(C6)-1,FALSE)

VLOOKUP formula

  • Select the cell again and click and drag the fill handle to the right until cell C16.

dragging VLOOKUP formula horizontally with vertical reference


Example 5 – AutoFill a Number Series in Excel

We want to create a normal divergent series, an odd number series, a geometric series of 5, and a decreasing series.

AutoFill number series dataset

  • If we select these cells and drag them down, it will create only copies of the existing values.

all values copying from single value

  • Insert another row of values in the original dataset.

second entry for number series

  • Select the range B6:E7 and drag it down. The fill handle will pick up the pattern and autofill accordingly.

AutoFill number series working properly


Example 6 – Insert a Patterned List Easily in Excel

Let’s look at another dataset where we want to fill in alphanumeric values while maintaining a pattern.

Fill handle for patterned list dataset

  • Insert the first value manually.

First value inserted manually for patterned list

  • Select the cell and drag it down. Excel will automatically maintain the pattern of filling in the next ID.

Fill handle for patterned list


Example 7 – Fill Days, Months, or Years Automatically in Excel

We want to fill up the following table with Days, Months, or Years using the Fill Handle feature.

Dataset for filling days, months, and years automatically

Filling Days:

  • Insert “Sunday” in cell B6.

first entry for filling days

  • Select the cell and drag the fill handle down to cell C12.

Filling days using fill handle

Fill Every Other Day:

  • Insert two values (e.g. Sunday and Tuesday) in cells C6 and C7.

Initial entries for filling every other day

  • Select both cells and drag the selection down to get the series.

Filling every other day using fill handle

Fill Months:

  • Insert the first month in cell D6.

First entry for filling months

  • Select the cell and drag it down to get all the months in order.

Filling months in Excel using fill handle

Fill Years:

  • Insert two values.

Initial entry for filling years

  • Select both cells and drag them down to get the years.

Filling years in Excel using fill handle


Can You Use the Fill Handle from the Keyboard?

There are keyboard shortcuts to fill values- Ctrl+D and Ctrl+R to fill down and right. The method doesn’t work for filling up a series.

For example, there is a formula in cell D6 in the following image dividing the value of cell B6 by C6. We want to fill the formula up to cell D9.

Quotient formula

  • Select the cells up to cell D9 and press Ctrl + D to see the result.

Replicating formula using fill handle shortcut

Note: To fill in horizontally to the right, select the horizontal range and press Ctrl + R.

What to Do If Excel Fill Handle Does Not Work?

Fix 1 – Check Cell References

In the following example, we have used a division formula in cell D6 and filled it in using the Fill Handle.

Quotient formula for distributed cell

If we check the next cell, the references have already shifted down one cell with the selection (B7 and C7 instead of B6 and C6).

Quotient formula replicating for all cells

Usually, this is helpful while working with formulas. However, this may cause issues when we need to work with the same cell reference in every formula.

Take the following dataset, for example. We wanted to divide each number by 4. It returned errors after filling with the same formula because there were no values involved in the C7:C10 range.

Formula messing up for improper cell reference

Solution:

We can fix this issue by using Absolute Cell References. Instead of using B6/C6, we can insert B6/$C$6 in cell D6.

Correct cell reference for the quotient formula

This gives the same result for cell D6, but when we fill it down using the fill handle, it won’t return an error.

Formula working properly for fill handle with proper references

By using the Dollar ($) signs, we have indicated to use the same reference every time while autofilling.

Note: You can insert the dollar sign either manually or by pressing F4 to toggle between different reference states, like fixing a column, row, or both.

Fix 2 – Turn on Automatic Workbook Calculation

Sometimes the series may end up repeating formulas instead of replicating the formulas for the next cell.

In the following example, we have used a formula to calculate the revenue from different accessory sales in cell E6. When we drag the formula down, the fill handle copies the same value instead of calculating its respective revenue.

Result copying because of manual workbook calculation

This is because the Calculation Options was set to Manual in the workbook.

Solution:

Turn the Calculation Options into Automatic and the formulas will be calculated according to their respective cells. You will find it under the Calculation group in the Formulas tab.

Turning on automatic workbook calculation

The fill handle will fill down the next value it picks up, depending on the data.

Fill handle working properly because of workbook calculation settings


What Are the AutoFill Options (Fill Handle Options) in Excel?

To control outputs in these scenarios, Excel has different AutoFill options. This is available in the box-shaped sign at the bottom-right of the filled range right after we drag the Fill Handle.

Autofill options position in a range


Option 1 – AutoFill Options for a List with Numbers

When we drag the fill handle from one number, we usually get the copied value of the number.

Same value copied from one initial value

  • Click on AutoFill Options from the bottom-right and select Fill Series.

Fill series option from autofill

  • The numbers will change into a series.

Series filling instead of copying

  • If we take two values, it fills up a series.

Series filling automatically by taking two initial values

  • If we want to copy the same value instead of creating a series, we need to select Copy Cells from AutoFill Options.

Copy cells feature in autofill

  • The initial numbers will be copied after that.

Values copying even with two initial values


Option 2 – Autofill Options for a List with Dates

In the following dataset, we have set a date in cell C6. After dragging the fill handle down for ten cells, we get the next ten dates.

Autofilling next dates

AutoFill Weekdays:

Now let’s take an example with the same starting date. But we want the next weekdays to appear only after filling.

  • Drag the fill handle down as usual for this and select Fill Weekdays from the AutoFill options.

Fill weekdays feature in autofill

  • It will ignore the Saturdays and Sundays in the range and fill out the range with the rest of the dates.

Autofilling weekdays

AutoFill Months:

For example, you want the 5th of June to appear after the 5th of May.

  • Drag the fill handle down from the first date and select Fill Months from the AutoFill Options.

Fill months option in autofill

  • You will notice the days remain the same as the first one, but the month values change to the next.

Autofilling months in dates

AutoFill Years:

  • You can drag a date and select the Fill Years option from AutoFill Options.
Fill years option in Autofill

Click on the image for a detailed view

  • This will keep the days and months the same but fill in the next years subsequently.

Autofilling years in dates


Option 3 – AutoFill Options for Formatting

If you have a formatted range and drag it with the Fill Handle, it will usually fill the values along with formatting.

Fill handle copying values and formattings

Fill Formatting Only:

  • Let’s take the same initial values (C6 to C8) and drag them down. Click on the AutoFill Options, and you will see the Fill Formatting Only option.

Fill formatting only option in autofill

  • Click on it, and you will see the values from the fill disappear.

Filling formatting only using fill handle

  • The format (text color in this case) remains for the cell. Insert a random value in this range, and you will notice it is transforming into a green-colored text.

Manually inserted value maintaining the format

Fill Without Formatting:

  • If you do the same with the same initial values and select the Fill Without Formatting option from the AutoFill Options, you can get the values only without any formats.

Fill without formatting option in autofill

  • This will be the result.

Filling without formatting using fill handle


Option 4 – AutoFill Options for Flash Fill

Let’s look at the following dataset, where we want to separate the full name into a last name and first name.

Dataset for separating text

  • Insert some values manually first in cells C6 and C7. In cell C8, Excel will suggest the result after it picks up the pattern.

Separating text using flash fill

  • After pressing Enter, you will get the final result.

Texts separates using flash fill

Here is how we can achieve that with the AutoFill Options using the Fill Handle:

  • Type out the first value manually.

Manually inserting separated values for fill handle

  • Select the cell and drag it down. It will copy the text first.

dragging fill handle from separated values copying the formula

  • Click on the AutoFill Options and select Flash Fill.

Flash fill option in autofill

  • The texts will be separated after that.

Values separated using fill handle


How to Create a Custom List for AutoFill in Excel?

We have a list of employees, and we need to repeat the list over and over again.

Original list to create custom list from

  • Select the File tab.

selecting file tab in Excel

  • Select Options.

selecting options from backstage view

  • In the Excel Options, select the Advanced tab.
  • Select Edit Custom Lists under the General section.
Edit custom lists option in Excel

Click on the image for a detailed view

  • Select the range from the spreadsheet that you want to save as a custom list, as shown in the image below, and click on Import.

Selecting range from the sheet for custom list

  • The list will appear under List entries.
  • Click OK once you are done.

List appearing in the option box

  • Close all the boxes and go back to where you want to use the custom list.
  • Insert a value and drag the Fill Handle down. It will fill the cells according to the existing list.

Custom list appearing using fill handle

  • You can start with any value and drag it down. The Fill Handle will insert the next value from the list.

Custom list appearing from any initial value


Download the Practice Workbook


Fill Handle in Excel: Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo