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.
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.
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
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.
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.
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.
➤ Go to the File Tab.
➤ Select Options.
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.
Then, if you hover your mouse pointer around cell E5, you will now see the Fill Handle tool.
➤ Drag down the Fill Handle tool.
In this way, the Excel Fill Series property will work finely and you will get your desired values with correct formulas.
Read more: Fix: Excel Autofill Not Working
2. Excel Fill Series Not Working Due to Not Selecting Automatic Calculation
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.
Here, we have used the following formula in cell E5 and then we will drag down the Fill Handle tool.
But we can see that here the same value is repeating instead of applying the formula to the rest of the cells automatically.
The cause of this problem is for selecting the Manual calculation option.
➤ Go to Formulas Tab >> Calculation Group >> Calculation Options Dropdown >> Check the Automatic option.
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.
Read more: [Fixed!] AutoFill Formula is Not Working in Excel Table
3. Excel Fill Series Not Working Due to Filtered Data Table
Here, we have enabled the Filter option for our data table by using the Sort & Filter option of Excel.
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.
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.
Seemingly, the fill series has worked here properly.
But, when we clear the filter from the Product column,
then we can see that the fill series is incomplete because the formula did not work for the hidden rows.
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.
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.
Here, you have the filtered data table.
And if you clear the filters from the table, it will not affect the fill series anymore.
Read more: Applications of Excel Fill Series
4. Pressing F4 Three times to Solve Excel Fill Series Not Working Problem
Sometimes, the Excel Fill Series feature doesn’t work because the Fill Handle tool stops working properly.
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.
It will resolve the issue and now you can fill the series without any problem.
Finally, the Excel Fill Series worked properly and you have got the values in the 5% Discount column.
- How to Perform Predictive Autofill in Excel (5 Methods)
- AutoFill Not Incrementing in Excel? (3 Solutions)
- How to Turn Off AutoFill in Excel (3 Quick Ways)
- Autofill a Column in Excel (7 Easy Ways)
5. Putting Enough Values to Get Desired Series
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.
So, we have written the first id 11001 in cell B5 and then dragged down the Fill Handle tool.
Unfortunately, it is not giving our wanted series pattern rather than it is repeating the same values.
Due to insufficient values which can help Excel to determine the pattern and fill out the series, this problem has happened.
➤ 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.
Finally, we are getting our desired Product Ids by using the Excel Fill Series property.
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.
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.
➤ Select that cell and go to Home Tab >> Editing Group >> Fill Dropdown >> 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.
In this way, you will get your correct series pattern.
7. Excel Fill Series Not Working Solution with Flash Fill Feature
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.
At first, we wrote the combination of the Product name and the Salesperson name in cell D5 as Blackberry-John.
But, in the second cell instead of writing the Product – Salesperson combination we have used SalesPerson – Product combination like Peter-Orange.
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.
Unfortunately, we are getting an error that says it is not finding any pattern to fill out the rest cells.
Basically, for the inconsistency of the first two cells, the Excel Fill Series is not working here.
➤ Write the combination of the two texts in the right way with a consistent pattern in the second cell.
➤ 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.
8. Excel Fill Series Not Working Solution with Autofill Options
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.
To do this, we have put down the first date 1/3/2022 (Monday) in cell C5 and dragged down the Fill Handle tool.
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.
➤ Write down the first date 1/3/2022 in cell C5 and drag down the Fill Handle tool.
Now, click on the Autofill options icon below the series.
➤ Among the options choose the Fill Weekdays option.
In this way, you will get only the weekdays and the weekends will be omitted from the series.
Read more: How to Apply AutoFill Shortcut in Excel
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.
Thank you so much, this solved everything for me!