In Microsoft Excel, there might be several reasons for the Autofill or Fill Handle option not working properly. In this article, you’ll find these problems that may occur frequently while using the Autofill option and how to fix these issues with suitable examples and illustrations.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Issues That May Cause Autofill Not Working Properly in Excel

1. Getting Repetitions of a Number Instead of a Series While Using Autofill

Here is our first case in the following picture. In Column B, the FIll Handle has been used here to make a series of numbers starting from ‘1’. But we have got the repetitions of 1 in return. To get a series of numbers (1,2,3…), we have to use the Fill Series option manually.

In the picture below, you’re seeing the option Fill Series from the menu. After you select the related option, you’ll find the series of numbers starting from 1 in Column B immediately.

2. Autofill of Series Not Working While Dragging Only Last Cell

Now let’s think of a case where multiple cells are occupying a series of numbers and you have to fill the next cells by following the series.

Let’s assume, we have two numbers ‘1’ and ‘3’ in B4 and B5 respectively. What we want here is to make a series of numbers with a common difference of 2. That means our arithmetic series should look like this: 1,3,5,7,9… 

But if you drag down only 3 from Cell B5 then you’ll find the repetitions of 3 only as shown in the following picture.

To get a proper arithmetic series, we have to select the range of cells available in the Column first. In our example, they are B4 and B5. After selecting the cells, we have to use the Fill Handle option from Cell B5 to drag down to the last Cell B10.

And finally, we’ll get the proper series of numbers without getting any repetition as displayed in the screenshot below.

3. Excel Autofill Not Working in Distant Column in Same Worksheet

In this example, we’ll see what’s wrong with using the Fill Handle option when two distant columns are present in the same worksheet.

Assuming that Column B has a series of numbers starting from 1. And Column D has two numbers only, 1 and 3 in D4 and D5. There is a gap between these two columns which has made these columns distinct from each other.

Now if we try to double-click the Fill Handle option in D5, we’ll find that it’s not working at all. The problem lies within the gap between Column B and Column D. Due to the presence of blank cells in Column C, the Fill Handle with double-click won’t function properly in the distinct column.

Now the data from Column D have been transferred to Column C. So, no gaps are lying now between these two columns. And now use the Fill Handle option with the double-click, you’ll find the series of numbers at once without encountering any complication.

4. Fill Handle Option Is Not Enabled in Excel

Usually, in any version of Microsoft Excel, the Fill Handle option remains activated by default. But sometimes there could occur some issues that may disable the Fill Handle option. In that case, we have to enable the Fill Handle option manually.

What you have to do is go to the Advanced tab in the Excel Options menu first. Then Under the Editing Options bar, mark on the options showing the statements ‘Enable fill handle and cell drag-and-drop’ and ‘Enable AutoComplete for cell values’.

After pressing OK, the Fill Handle should then work perfectly in your spreadsheets.

5. Autofill Not Functioning Properly with Filtered Table

Some users with the older versions of Microsoft Excel have reported that the Autofill option is not working in the filtered table. After removing the Filter option, the Fill Handle has again started to function properly. To remove the Filter from your table, you have to simply press CTRL+SHIFT+L together. After using the Fill Handle, you can hold similar buttons on your keyboard again to reactive the Filter options for the headers in your Excel table.

6. Automatic Calculation Is Turned Off

In the following picture, Cell C4 contains a calculated value that has been done by multiplying the value in Cell B4 with 5.

Now if we use the Fill Handle option to get all other resultant data in Column C, we may find the repeated values instead of getting accurate results. And this problem occurs when the Automatic Calculation remains turned off.

To fix this issue, we have to go to the Formulas tab first. Then from the Calculation Options drop-down, we’ll choose the option ‘Automatic’.

And the return values that were repetitions of 5 previously will turn into the multiples of 5 right away based on the cell values present in Column B.

7. Autofill with Flash Fill Not Working Properly

In the last section, we’ll find out the problem while using the Flash Fill option to return the extracted data. The Flash Fill option looks for a particular pattern in a column for a specified cell value and then extracts data by following the pattern.

For example, we have some text values lying in Column B under the Text header. We have to extract the shorthands of the country names from those text data in Column C by using the Flash Fill option.

To do this, we have to extract the country name manually from the first cell only and input it in Cell C5. Now use the Fill Handle to autofill the column and then select the Flash Fill option from the menu.

In the output column, the return values are not as we expected, right? The problem is the Flash Fill option here has found a pattern that will extract the values from the middle (Between two hyphens) of the texts only. But we needed to extract the alphabets only.

So, if the text data were arranged with a specific pattern as shown in the following picture in Column B, then the extracted values with the use of the Flash Fill option would return all the initials of the country names only.

Concluding Words

I hope all of these issues mentioned above will now prompt you to use the Autofill option properly in your Excel spreadsheet. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

You May Also Like to Explore

How to Use Autofill Formula in Excel (6 Ways)

How to Perform Predictive Autofill in Excel (5 Methods)

How to Autofill Numbers in Excel (12 Ways)

How to Autofill Numbers in Excel without Dragging (5 Quick Methods)

How to Autofill Dates in Excel (3 Suitable Methods)

The post Fix: Excel Autofill Not Working (7 Issues) appeared first on ExcelDemy.