Fill Handle tool is used to fill or extend data or series in Excel. It is a plus-sign cursor (+). We can see it by moving the cursor to the bottom-right corner of a selected cell or range.
In this article, you will learn everything about the Fill Handle feature in Excel.
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. Just select the cells and drag them using the fill handle tool.
In this blog post, you will learn all the details related to the Excel Fill Handle feature.
– What Fill Handle is in Excel
– How to enable/disable the feature
– Why to use Fill Handle
– Basic uses of the feature
– General fixes to its common problems
– AutoFill options for the fill handle feature
– Creating custom lists for AutoFill
We have used Excel for Microsoft 365 in this tutorial. You can apply the knowledge to any other Excel version too.
⏷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?
⏵Check Cell References
⏵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. We use it for data entry and manipulation.
Usually, we can find the icon when we move the mouse cursor to the bottom-right square of a selection.
There are two ways to use the Fill Handle in Excel.
- You can either click on it and drag it to a point to fill in values.
- Or, you can double-click when the icon changes to a plus sign.
It will give you the same result.
Keep in mind that the Fill Handle doesn’t work on a Protected Sheet. This is because you can’t make any changes to the cells of that sheet.
Click on the GIF to get a detailed view
How Do I Enable or Disable the Fill Handle in Excel?
If the cursor doesn’t change into a plus sign when you hold it at the bottom-right of a selection, chances are that the Fill Handle feature is disabled.
You can easily turn the feature on or off by following these steps.
- Click on File from the ribbon.
- This will open up the backstage view. Select Options from there.
- Now 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.
Click on the image for a detailed view
Why Do We 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 some 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 most of the text values.
– Can return wrong outputs for formulas when references aren’t put properly.
How to Use Fill Handle in Excel: 7 Suitable Examples
Previously, we mentioned the basic way of using the fill handle tool in Excel. Now we will go into its application in different scenarios and get different outputs.
1. To Fill Data Quickly from Adjacent Cells
By default, the fill handle will fill in the next values in a series. This works for numbers and alphanumeric values.
For example, 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.
- Select cell B6 and drag the fill handle to C6 to fill in the next value.
- Now select cell B7 and drag it to cell C7 to get the next value.
If we do that for all of the cells, we can get all the values.
Fill Data Quickly for Multiple Cells
We can also fill multiple cells at a time. 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.
Here is how we can use the fill handle for this scenario.
- Select the range B6:D6.
- Then 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.
2. To Fill Down Blank Cells Until Next Value
In this section, we will fill down blank cells until the next value. In many cases, you might come across a dataset where only one cell contains data, and the cells below it remain blank until the next value appears.
In the following dataset, we need to fill “Sales” up to cell C10, “Marketing” up to cell C13, and “IT” in cell C16.
- As mentioned earlier, fill handle copies text values. When we select cell C6 and drag the fill handle down to cell C10, it will fill the range with “Sales”.
- Selecting cell C11 and dragging it till C13 will fill the range with “Marketing”.
- Doing the same for C15 and C16 will give us “IT”.
3. To Copy a Formula to the Left, Right, Up, and Down
In all of our previous examples, we have dragged the fill handle down to either copy or fill the next value in the downward direction. However, the fill handle can be used in all directions.
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.
Click on the image for a detailed view
We are going to use the SUM function to get the total sales. Here is how we can fill them in all directions:
Fill Formula Down:
- Use the following formula in cell I6.
=SUM(C6:G6)
Click on the image for a detailed view
- Now select the cell again, move your cursor to the bottom-right of the selection, and drag the fill handle down.
Click on the image for a detailed view
Fill Formula Up:
- Use the formula in cell I13 this time.
=SUM(C13:G13)- Then move your cursor to the bottom-right of the selection.
- Instead of dragging it down, click and drag it up to cell I6 this time.
Click on the image for a detailed view
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.
Click on the image for a detailed view
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.
Click on the image for a detailed view
4. To Drag Formula Horizontally With Vertical Reference in Excel
We can drag the fill handle horizontally with vertical references too. 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”.
Click on the image for a detailed view
This is where we are going to use a combination of VLOOKUP and COLUMN functions. After that, we will drag the fill handle to use the formula horizontally with vertical references.
- Insert the following formula in cell C16.
=VLOOKUP($B$16,$B$6:$G$13,COLUMN(C6)-1,FALSE)
Click on the image for a detailed view
- Now select the cell again and click and drag the fill handle to the right until cell C16.
Click on the image for a detailed view
5. To AutoFill Number Series in Excel
Another useful application of the fill handle feature is to create a number series. However, dragging one cell value with the fill handle only creates a copy of that cell. We need multiple values to start with before creating a series.
Let’s take the following image, for example. We want to create a normal divergent series, an odd number series, a geometric series of 5, and a decreasing series.
If we select these cells and drag them down, it will create only copies of the existing values.
Instead, insert another row of values in the original dataset.
Now select the range B6:E7 and drag it down. This way, the fill handle will pick up the pattern and autofill accordingly.
6. To Insert Patterned List Easily in Excel
Now let’s look at another dataset where we want to fill in alphanumeric values while maintaining a pattern.
- For that, insert the first value manually.
- Now select the cell and drag it down. Excel will automatically maintain the pattern of filling in the next ID.
7. To Fill Days, Months, or Years Automatically in Excel
The Fill Handle feature of Excel can pick up other patterns too. We want to fill up the following table with Days, Months, or Years using the Fill Handle feature.
Filling Days:
- Insert “Sunday” in cell B6.
- Select the cell again, and drag the fill handle down to cell C12.
Fill Every Other Day:
- For this, we need to insert two values (e.g. Sunday and Tuesday) in cells C6 and C7.
- Now select both the cells and drag it down to get the series.
Fill Months:
- Insert the first month in cell D6.
- Select the cell and drag it down to get all the months in order.
Fill Years:
- To get the years in order, you also need to insert two values first.
- Now select both of the cells and drag them down to get the years.
Can We Use Fill Handle from the Keyboard?
There are keyboard shortcuts to fill values- Ctrl+D and Ctrl+R to fill down and right. This only works when we are filling up a formula or copying a cell. 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.
Now select the cells up to cell D9 and press Ctrl+D to see the result.
What to Do If Excel Fill Handle Does Not Work?
You may end up finding the fill handle feature acting up like formulas not filling accordingly or the fill isn’t working as intended. This is mainly due to using inaccurate cell reference types and workbook calculation settings.
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.
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).
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.
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.
This gives the same result for cell D6, but when we fill it down using the fill handle, it won’t return an error.
By using the Dollar ($) signs, we have indicated to use the same reference every time while autofilling.
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.
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.
Now the fill handle will fill down the next value it picks up, depending on the data.
What Are the AutoFill Options (Fill Handle Options) in Excel?
The Fill Handle has three main purposes: copy, fill in the next value, and fill formulas. However, entries can have different meanings and different purposes. For example, a number can indicate both date and numeric values. A series that starts with 1, 2 may need to be 1, 2, 1, 2 instead of 1, 2, 3, 4, etc.
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.
Here are more details about the available AutoFill options in different scenarios.
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.
However, we can change that in the autofill options. Click on AutoFill Options from the bottom-right and select Fill Series.
The numbers will change into a series now.
If we take two values, it fills up a series.
If we want to copy the same value instead of creating a series, we need to select Copy Cells from AutoFill Options.
The initial numbers will be copied after that.
2. Autofill Options for a List with Dates
Unlike numbers, the fill handle always fills dates to match the next value.
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.
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 after that.
- It will ignore the Saturdays and Sundays in the range and fill out the range with the rest of the dates.
AutoFill Months:
Suppose you want to fill in the next month on the same date. For example, you want the 5th of June to appear after the 5th of May. You can use the AutoFill option for that too.
- Drag the fill handle down from the first date, and select Fill Months from the AutoFill Options.
- You will notice the days remain the same as the first one, but the month values change to the next.
AutoFill Years:
- Similarly, you can drag a date and select the Fill Years option from AutoFill Options too.
Click on the image for a detailed view
- This will keep the days and months the same, but fill in the next years subsequently.
3. AutoFill Options for Formatting
The AutoFill options of the Fill Handle can toggle with formatting too. If you have a formatted range and drag it with the Fill Handle, it will usually fill the values along with formatting.
Here is how we can use it differently:
Fill Formatting Only:
- Let’s take the same initial values (C6 to C8), for example, and drag it down. After clicking on the AutoFill Options, you will see the Fill Formatting Only option.
- Click on it, and you will see the values from the fill disappear.
- However, 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.
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.
- This will be the result.
4. AutoFill Options for Flash Fill
The Flash Fill feature of Excel automatically picks up a pattern from the data of the adjacent cells. With the Fill Handle feature, you can accomplish what a Flash Fill does too.
For example, let’s look at the following dataset, where we want to separate the full name into a last name and first name pattern.
You have to insert some values manually first in cells C6 and C7. In cell C8, Excel will suggest the result after it picks up the pattern.
After pressing Enter, you will get the final result.
Here is how we can achieve that with the AutoFill Options using the Fill Handle:
- Type out the first value manually.
- Then select the cell and drag it down. It will copy the text first.
- Now click on the AutoFill Options and select Flash Fill.
- The texts will be separated after that.
How to Create Custom List for AutoFill in Excel?
We have covered different types of filling options for the Fill Handle. It can create different days, months, years, and number patterns and copy the same value. But what if we want to use a custom list for AutoFill? Generally, these custom lists don’t follow a pattern.
Take the following image, for example. We have a list of employees, and we need to repeat the list over and over again.
We can save it as a custom list and use the Fill Handle to drag and fill for the next usage. Here is how we can do that:
- First, select the File tab.
- Select Options from the backstage view.
- In the Excel Options, select the Advanced tab.
- Select Edit Custom Lists under the General section.
Click on the image for a detailed view
- Now 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.
- The list will appear under List entries. Click OK once you are done.
- 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 now fill the cells according to the existing list.
- You can start with any value and drag it down. The Fill Handle will insert the next value from the list.
Download Practice Workbook
That concludes our discussion on what the Fill Handle feature is, how to enable/disable and use it. We have shown how we can copy a value, fill the next value, fill values in every direction, and create different numeric and alphanumeric series using the Fill Handle.
We have also covered how it interacts with different dates and how to create different date patterns. Two common issues while working with the Fill Handle- invalid cell references and wrong workbook settings are also covered in this article. Lastly, we discussed the manipulation of the Fill Handle values using the AutoFill options and how we can create a custom list in Excel for AutoFill.
Fill Handle in Excel: Knowledge Hub
- How to Fill Formula Down to Specific Row in Excel
- How to Drag Formula and Ignore Hidden Cells in Excel
- How to Use Fill Handle to Copy Formula in Excel
- How to Drag Cells in Excel Using Keyboard
- How to Drag Formula in Excel with Keyboard
- How to Enable Drag Formula in Excel
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
