[Fix] Excel Fill Series Not Working (8 Causes with Solutions)

To demonstrate the solutions to the Excel Fill Series problem, we are using the following data table.

Fix 1 – Check the Fill Handle Option to Fix Excel Fill Series Not Working

Context:

Let’s say we want to calculate a 5% Discount on the prices of the products. We will use the formula in the first cell, then we will use the Fill Series feature to fill up the rest of the cells with this formula.

After hovering the mouse pointer around the bottom right corner of this cell it was supposed to be changed into the Fill Handle tool icon.

The Fill Handle tool didn’t appear because the Fill Handle tool option was disabled.
If we try to drag down the formula throughout all the remaining cells, nothing will change.

Solution:

Go to the File tab.

Select Options.

The Excel Options wizard will pop up.
Select the Advanced option.
In the Editing options, check the Enable fill handle and cell drag-and-drop option.
Press OK.

If you hover your mouse pointer around cell E5, you will now see the Fill Handle tool.
Drag down the Fill Handle tool.

The Excel Fill Series property should work.

Read More: How to Auto Number Cells in Excel

Fix 2 – Selecting Automatic Calculation to Fix a Series

Context:

We have used a formula in one cell, then tried to use the Fill Series feature to fill up the rest of the cells with this formula.

We have used the following formula in cell E5 and then dragged down the Fill Handle tool.

=D5*5%

But, the same value is repeated instead of applying the modified formula to the rest of the cells automatically.

The cause of this problem is selecting the Manual calculation option.

Solution:

Go to the Formulas tab and the Calculation Group.
Click on Calculation Options and check the Automatic option.

You will see that the same repetitive values have been automatically changed because enabling the Automatic option will help to calculate the values using the formulas automatically.

Fix 3 – Removing Filters From the Data Table

Context:

We have enabled the Filter option for our data table by using the Sort & Filter option.

We selected the dropdown sign of the Product column to filter the table based on the column and we randomly unclicked some products to hide their respective rows.

You can see that row numbers 5, 9, and 11 are hidden from this table.
We entered our formula in cell E6 and dragged down the Fill Handle tool.

The fill series has worked here properly.

We cleared the filter from the Product column.

The fill series is incomplete because the formula did not work for the hidden rows.

Solution:

To solve this problem, we have to fill out the series prior to the filtering task.

Remove all filters.
Enter the formula in the E5 cell and drag down the Fill Handle tool.

=D5*5%

Select the dropdown icon of the Product column to filter the table.

You have the filtered data table.

Fix 4 – Using the F4 Key

Context:

Sometimes, the Excel Fill Series feature doesn’t work because the Fill Handle tool stops working properly.

Solution:

Here is the easiest shortcut solution to solve this problem.

Select the cell from which you want to drag down the Fill Handle tool.
Press F4 three times.

This will resolve most issues and you can fill the series without any problem.

The Excel Fill Series worked properly, and we have the values in the 5% Discount column.

Fix 5 – Putting More Values to Get the Desired Series

Context:

We want to fill out the Product ID column with the IDs in a series pattern like 11001, 11004, 11007, ….. etc. with an interval of 3.

We have written the first ID 11001 in cell B5 and then dragged down the Fill Handle tool.

This does not give our wanted series pattern, but is repeating the same values.

Solution:

Insert at least the first two values of your series in the first two cells.
Select these cells and drag down the Fill Handle tool.

We are getting our desired Product IDs.

Fix 6 – Using the Series Option to Get Desired Series

To solve the same value repetition issue as the previous section, here we will use the Series option to have our desired pattern.

Insert the first value of the series in cell B5.

Select that cell and go to the Home tab and the Editing group.
Click on the Fill dropdown and select Series.

The Series dialog box will appear.
Choose and write the following

Series in → Columns
Type → Linear
Step Value → 3 (or any other value by which you want to increase your series)
Stop Value → 11028 (or any other value at which you want to stop your series)

Press OK.

You will get your correct series pattern.

Fix 7. Using the Flash Fill Feature

Context:

Here, we want to combine the Product name and the Salesperson name with a hyphen (-).

We wrote the combination of the Product name and the Salesperson name in cell D5 as Blackberry-John.

In the second cell instead of writing the Product – Salesperson combination we have used SalesPerson – Product combination like Peter-Orange.