Fix: Excel Autofill Not Working (7 Issues)

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.

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

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.

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

Read more: Automatic Numbering in Excel


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.

Autofill of Series Not Working While Dragging Only Last Cell

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.

Autofill of Series Not Working While Dragging Only Last Cell

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

Autofill of Series Not Working While Dragging Only Last Cell

Read more: How to Fill Down to Last Row with Data in Excel


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.

Excel Autofill Not Working in Distant Column in Same Worksheet

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.

Excel Autofill Not Working in Distant Column in Same Worksheet


Similar Readings:


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.

Fill Handle Option Is Not Enabled in Excel


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.

Automatic Calculation Is Turned Off

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.

Automatic Calculation Is 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’.

Automatic Calculation Is Turned Off

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.

Automatic Calculation Is Turned Off


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.

Autofill with Flash Fill Not Working Properly

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.

Autofill with Flash Fill Not Working Properly

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.

Autofill with Flash Fill Not Working Properly


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

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

1 Comment
  1. Sir still this auto-fill is not working in my excel sheet after all the above requirement. What will be the reason?

Leave a reply

ExcelDemy
Logo