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

With the help of the Fill Series feature of Excel, a lot of time can be saved and the calculation process becomes faster, but unfortunately, sometimes you can face Excel Fill Series not working problems. In this article, we will try to find the source of problems along with the solutions. So, let’s get started with the main article.

Download Workbook


8 Fixes of Excel Fill Series Not Working Problems

For demonstrating the solutions of the Excel Fill Series problem, we are using the following data table.

Excel fill series not working

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


1. Excel Fill Series Not Working Due to Unchecking the Fill Handle Option

Context:

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

Excel fill series not working

To do this, we have used the following formula in cell E5 and 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.

=D5*5%

enabling fill handle tool

Here, the Fill Handle tool didn’t appear because of disabling the Fill Handle tool option.
So, if we try to drag down the formula throughout all the remaining cells then nothing will change.

enabling fill handle tool

Solution:

➤ Go to the File Tab.

Excel fill series not working

➤ Select Options.

enabling fill handle tool

After that, the Excel Options wizard will pop up.
➤ Select the Advanced option.
➤ Among the Editing options, check the Enable fill handle and cell drag-and-drop option and finally press OK.

enabling fill handle tool

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

enabling fill handle tool

In this way, the Excel Fill Series property will work finely and you will get your desired values with correct formulas.

Excel fill series not working

Read more: Fix: Excel Autofill Not Working


2. Excel Fill Series Not Working Due to Not Selecting Automatic Calculation

Context:

For calculating a 5% Discount on the prices of the products we will use the formula in the first cell, then we will try to use the Fill Series feature for filling up the rest of the cells with this formula.

Excel fill series not working

Here, we have used the following formula in cell E5 and then we will drag down the Fill Handle tool.

=D5*5%

automatic calculation

But we can see that here the same value is repeating instead of applying the formula to the rest of the cells automatically.

automatic calculation

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

Solution:

➤ Go to Formulas Tab >> Calculation Group >> Calculation Options Dropdown >> Check the Automatic option.

Excel fill series not working

After that, 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.

automatic calculation

Read more: [Fixed!] AutoFill Formula is Not Working in Excel Table


3. Excel Fill Series Not Working Due to Filtered Data Table

Context:

Here, we have enabled the Filter option for our data table by using the Sort & Filter option of Excel.

Excel fill series not working

Then, we have selected the dropdown sign of the Product column to filter the table on the basis of this column and after that, we have randomly unclicked some products to hide their respective rows.

filtered data

After this procedure, you can see that row numbers 5, 9, and 11 are hidden from this table.
Later, we have entered our formula in cell E6 and dragged down the Fill Handle tool.

filtered data

Seemingly, the fill series has worked here properly.

filtered data

But, when we clear the filter from the Product column,

filtered data

then we can see that the fill series is incomplete because the formula did not work for the hidden rows.

Excel fill series not working

Solution:

To solve this problem, we have to fill out the series prior to the filtering task.
➤ Enter the formula in the E5 cell and drag down the Fill Handle tool.

=D5*5%

filtered data

After completing the cells of the 5% Discount column with the formulas, select the dropdown icon of the Product column to filter the table like the previous way.

filtered data

Here, you have the filtered data table.

filtered data

And if you clear the filters from the table, it will not affect the fill series anymore.

Excel fill series not working

Read more: Applications of Excel Fill Series


4. Pressing F4 Three times to Solve Excel Fill Series Not Working Problem

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 3 times.

Excel fill series not working

It will resolve the issue and now you can fill the series without any problem.

pressing F4

Finally, the Excel Fill Series worked properly and you have got the values in the 5% Discount column.

pressing F4


Similar Readings


5. Putting Enough Values to Get Desired Series

Context:

Here, 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.

Excel fill series not working

So, we have written the first id 11001 in cell B5 and then dragged down the Fill Handle tool.

giving enough values for a series

Unfortunately, it is not giving our wanted series pattern rather than it is repeating the same values.

giving enough values for a series

Due to insufficient values which can help Excel to determine the pattern and fill out the series, this problem has happened.

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.

giving enough values for a series

Finally, we are getting our desired Product Ids by using the Excel Fill Series property.

Excel fill series not working


6. Using Series Option to Get Desired Series

Here, we also fill out the Product Id column with a series pattern 11001, 11004, ….. etc. like the previous one.

Solution:

To solve the same value repetition issue as the previous section, here we will use the Series option to have our desired pattern.
➤ Put down the first value of the series in cell B5.

using Series option

➤ Select that cell and go to Home Tab >> Editing Group >> Fill Dropdown >> Series Option.

using Series option

After that, the Series dialog box will appear.
➤ Choose and write the followings

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.

using Series option

In this way, you will get your correct series pattern.

Excel fill series not working


7. Excel Fill Series Not Working Solution with Flash Fill Feature

Context:

Here, we want to combine the Product name and the Salesperson name with a separator as a hyphen (-), and to do this we can use the Flash Fill feature of Excel.

Excel fill series not working

At first, we wrote the combination of the Product name and the Salesperson name in cell D5 as Blackberry-John.

flash fill feature

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

flash fill feature

Then for the next cells we have used the Flash Fill feature by going through the Home Tab >> Editing Group >> Fill Dropdown >> Flash Fill Option.

flash fill feature

Unfortunately, we are getting an error that says it is not finding any pattern to fill out the rest cells.

flash fill feature

Basically, for the inconsistency of the first two cells, the Excel Fill Series is not working here.

Solution:

➤ Write the combination of the two texts in the right way with a consistent pattern in the second cell.

flash fill feature

➤ Now, use the Flash Fill option for the rest of the cells.
And, so you are getting the combination for all of the cells in the right way this time.

Excel fill series not working


8. Excel Fill Series Not Working Solution with Autofill Options

Context:

Suppose, we want to fill out the Order Date column with weekdays only and we will try to use the Excel Fill Series property here.

Excel fill series not working

To do this, we have put down the first date 1/3/2022 (Monday) in cell C5 and dragged down the Fill Handle tool.

autofill options

Here, we have got the rest of the dates serially but it is including the weekends (1/8/2022 & 1/9/2022) also which we don’t want.

Excel fill series not working

Solution:

➤ Write down the first date 1/3/2022 in cell C5 and drag down the Fill Handle tool.

autofill options

Now, click on the Autofill options icon below the series.

autofill options

➤ Among the options choose the Fill Weekdays option.

autofill options

In this way, you will get only the weekdays and the weekends will be omitted from the series.

Excel fill series not working

Read more: How to Apply AutoFill Shortcut in Excel


Conclusion

In this article, we tried to cover some of the solutions of the Excel Fill Series not working problems. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

1 Comment
  1. Thank you so much, this solved everything for me!

Leave a reply

ExcelDemy
Logo