Abrar-ur-Rahman Niloy
Dragging or double-clicking Fill Handle Icon to fill up columns is one of the most useful features in Excel. We can replicate formulas or values on a large scale in a very short time with this feature. But there could be issues causing problems while we are working with drag to fill causing it not to work properly, or not at all. This article will go through the solutions for all the problems that can possibly result in Excel drag to fill not working.
Download Practice Workbook
You can download the workbook with the examples we used for this article.
8 Possible Solutions for Excel Drag to Fill Not Working
In this tutorial, we will be going over a total of eight possible solutions. Try each one out in case one is not working. And hopefully, you will find your drag to fill feature working again in Excel.
Solution 1: Enable Fill Handle Feature
Excel Fill Handle Icon for drag to fill may not be working or showing at all when the Fill Handle option is disabled from the settings in the first place. In case it is disabled, follow these steps to enable the Fill Handle option in Excel.
Steps:
- First of all, click on the File tab on your ribbon.
- Then select Options.
- As a result, the Excel Options box will open up. Now, go to the Advanced tab in it on the left of the box.
- Then check the Enable fill handle and cell drag-and-drop option under the Editing options group on the right.
- Finally, click on OK.
This will solve the problem if the drag icon for the Fill Handle isn’t showing at all if you have selected a range in Excel.
Read More: How to Enable Drag Formula in Excel (With Quick Steps)
Solution 2: Change Calculation Options
If your option is checked to enable the Fill Handle icon in Excel settings, but drag to fill is not working for the formulas, this maybe is caused because your Calculations Options aren’t set right.
Usually, the option is set as automatic. So if you have selected the manual option or it was selected for some reason, you will find the Excel drag to fill feature is not working for formulas.
Follow these steps to change it back to automatic.
Steps:
- First, select the Formulas tab on your ribbon.
- Now select Calculation Options from the Calculation
- Then select Automatic from the drop-down menu.
As a result, you will find that the Excel drag to fill feature not working for formulas issue is resolved. Now, the drag to fill feature should work for both series and formulas.
Read More: [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)
Solution 3: Use Other Autofill Options
Sometimes, when you use the Excel drag to fill feature for a series, you will find that it is not working in an intended way. You may want a particular series, but the drag is filling with copies and vice versa.
For example, let’s say we have this cell as below.
Now we want it to be something like this after dragging.
But it seems to fill up like this.
The opposite one might also be true. Either way, follow these steps to fix the problem.
Steps:
- First, select the cell and drag the Fill Handle icon to the end to fill up values.
- As you have finished dragging and releasing the click, you will see the Autofill options appearing on the bottom right of the selection. Now click on it.
- By doing so, a drop-down list will appear. Select the fill option you want from here. In our example, the Copy Cells option is selected. But we need the Fill Series.
- Once you have selected the option, you will find that your drag fill has changed to your desired series.
This way, you will find your Excel drag to fill not working for desired series issue resolved.
Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)
Solution 4: Turn Off Filter
Some users have reported that their Excel drag to fill feature is not working in a table when the filter option is turned on. Accordingly, turning off the filter options will resolve this issue.
If you have filters on your headers, you can simply select the table or column and press Ctrl+Shift+L on your keyboard or follow these steps.
Steps:
- First, go to the Home tab in your ribbon.
- Then select Sort & Filter under the Editing group.
- After that, click on Filter from the drop-down menu to enable/disable filters.
Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)Â
Solution 5: Select All Values Before Dragging Fill Handle
When you drag to fill in Excel and you don’t select all of the values of the series before dragging you may find that it is not working properly. For this reason, you may find that the drag feature is filling the series with wrong series or irrelevant numbers.
For example, let’s say we have to complete the following series.
But before dragging, only the last cell is selected. As a result of the dragging, you will find that your series may look something like this.
Steps:
- Select all of the cells in the series for this particular problem.
- Now click and drag the fill handle icon to the cell you want to extend the series to. As a result, the series will get filled up as intended.
Read More: Fill Down to Next Value in Excel (5 Easy Methods)
Solution 6: Remove Gap Between Columns
If you are double-clicking the fill handle icon to fill up values in a column but there is no adjacent column to it, it simply won’t work. The double-clicking to fill feature uses the column beside it as the reference length. If there isn’t any, it won’t get any reference length. As a result, this Excel Fill feature will not work.
For example, notice the following column.
In this case, the series is in columns B and D. There is a column gap between them. For this reason, if you select the series in column D and double click the fill handle icon it won’t work.
To resolve this issue, remove the gap between them by putting the series side by side.
Now select the second series and double-click the fill handle icon. Hence, you will find that the feature is working perfectly.
Read More: How to Drag Formula Horizontally with Vertical Reference in Excel
Solution 7: Put Enough Values Before Dragging
Sometimes while using the Excel drag to fill feature, you may find that it is not filling the series with appropriate data. Even though you have tried and applied all of the above solutions described. In some cases, the problem lies in just a lack of data for the series.
For example, notice the following two series.
If you select all the data in both the series and drag it to fill the rest of it, you will find the series to be different.
To eliminate this issue, enter enough values in the series first. So that Excel fills up the series with the desired values.
Read More: How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)
Solution 8: Correct Patterns for Autofill
Flash fill is another useful feature to fill out column values quickly. Usually, this feature identifies a pattern from a group of reference cells and the previous entry values and returns the result for the rest of them.
So if there isn’t any general pattern in the dataset in the first place, this feature will not be working properly.
For example, let’s take the following dataset.
In this case, we want to extract the text values with letters only from column B. Now, if we use the flash fill feature to fill out the values for this dataset, the result will be like this.
Here, we can see the value in cell C3 is 49. But our purpose was to extract GER from cell B7. Because of the previous two inputs, where we took the values before the hyphen(-), the Excel flash feature recognizes that as the pattern and puts in what is before the hyphen in the rest of the column.
To resolve this issue with the Autofill feature, unfortunately, the only probable solution is to refine the original texts in the dataset. So that it fits the pattern Excel is supposed to look for. And thus the Autofill to fill feature should be working. Look at the following dataset, the value of cell B7 is modified here and as a result, the Autofill feature is filling up the correct values properly.
Hence, this will resolve the Excel Autofill feature not working properly issue.
Conclusion
These were all the possible solutions for Excel drag to fill not working. Hopefully, one of these solutions has done the job for you and you found the guide helpful and informative. If it still didn’t resolve the issue, or you have any queries, let us know below. For more detailed fixes and guides like this, visit Exceldemy.com.
