Tanjima Hossain
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 the problems along with the solutions. So, let’s get started with the main article.
To demonstrate the solutions to the Excel Fill Series problem, we are using the following data table.
We have used the Microsoft Excel 365 version here, you can use any other version 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 to fill 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.
=D5*5%
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.
Solution:
➤ 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: How to Use Autofill Formula in Excel
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.
Here, we have used the following formula in cell E5 and then we will drag down the Fill Handle tool.
=D5*5%
But we can see that here the same value is repeated instead of applying the formula to the rest of the cells automatically.
The cause of this problem is selecting the Manual calculation option.
Solution:
➤ 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
Context:
Here, we have enabled the Filter option for our data table by using the Sort & Filter option of Excel.
Then, we selected the dropdown sign of the Product column to filter the table on the basis of this column and after that, we 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 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,
we can see that 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.
➤ Enter the formula in the E5 cell and drag down the Fill Handle tool.
=D5*5%
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.
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks in Excel
- How to Repeat Formula Pattern in Excel
- How to Apply AutoFill Shortcut in Excel
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.
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 the values in the 5% Discount column.
Similar Readings
- How to AutoFill Sequential Letters in Excel
- How to Perform Predictive AutoFill in Excel
- How to Turn Off AutoFill in Excel
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Fix: Excel Autofill Not Working
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.
So, we have written the first id 11001 in cell B5 and then dragged down the Fill Handle tool.
Unfortunately, it does not give our wanted series pattern rather it is repeating the same values.
Due to insufficient values that can help Excel 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.
Finally, we are getting our desired Product Ids by using the Excel Fill Series property.
Read More: How to Auto Populate from Another Worksheet in Excel
6. Using the 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.
➤ 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 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.
In this way, you will get your correct series pattern.
Similar Readings:
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- How to Auto Number Cells in Excel
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.
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, because of 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.
➤ 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.
Similar Readings:Â
- How to Autofill Dates in Excel Without Dragging
- How to Autofill Dates in ExcelÂ
- How to Autofill Days of Week Based on Date in Excel
- How to Increment Month by 1 in Excel
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
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.
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 includes the weekends (1/8/2022 & 1/9/2022) which we don’t want.
Solution:
➤ 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 Enter Sequential Dates Across Multiple Sheets in Excel
Download Practice Workbook
Conclusion
In this article, we tried to cover some of the solutions to 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
- How to Auto Generate Number Sequence in Excel
- How to AutoFill Numbers in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Auto Number or Renumber after Filter in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- Drag Number Increase Not Working in Excel
Thank you so much, this solved everything for me!