The Excel Fill Series feature allows you to automatically fill in data in the cells, based on the value or patterns in other cells.
This is a useful feature that you can use to complete your datasets and do data entry faster.
However, there are times when things may not go exactly according to plan when using this feature.
In this tutorial, we are going to look at why Excel Fill Series may not be working and what you can do to fix the issue in each instance.
Excel Fill Series Not Working Because Fill Handle is Not Enabled
Situation: You may find that the Fill Handle feature has been disabled in a certain workbook. Some users feel that the Fill Handle is distracting and, as a result, turn it off.
Let’s look at the following example to see what happens if this is the case.
Below we have the numbers 2 and 4, shown in cells A1 and A2, respectively.
We’ll start by selecting range A1:A2.
If you hover over the bottom right-hand corner of the selected range and you see the standard cursor, then this means that the Fill Handle feature has been disabled.
Had the fill handle been enabled, you would have seen the plus icon (as shown below)
But if this is disabled, you won’t see the black plus icon (shown above)
Consequently, you will not be able to drag down and fill the column with even numbers.
Solution: So to fix this issue, do the following.
- Go to the File Tab and select Options.
- Using the Excel Options Panel, select Advanced. Check the Enable fill handle and cell drag-and-drop option. Click Ok.
- Now select range A1:A2.
- Hover over the bottom right-hand corner of the range. You should see that the mouse pointer has changed to a small black cross. This is the Fill Handle.
- Now you can drag down using the Fill Handle to see the even number series.
Excel Fill Series Not Working Because Not Enough Values Are Given
Situation: If you don’t enter enough values for your series, in most cases, you will find that Excel will not be able to detect the pattern.
Let’s look at the following example in order to see what happens if this is the case.
We want to fill the series down the column based on multiples of 3.
So we enter the number 3 in cell A1.
If you select cell A1 and then drag down the column using the Fill Handle, you will see the following.
The number 3 has been copied down the column instead of multiples of 3 which is what we actually want.
Solution: So to fix this issue, do the following.
- Delete all the values except for the number 3 in cell A1.
- In cell A2, enter 6. This is the next multiple of 3.
- Select range A1:A2 and drag down the column using the Fill Handle to see the column populated with multiples of 3.
Note: For Auto Fill to detect a pattern, you will usually need to enter at least two values of your desired series.
You can do the following instead to fill the series without dragging it down.
- Select cell A1, which contains the number that our series will start at. If you want the series to start at another number, you will have to enter it instead.
- With cell A1 selected, go to the Home Tab. In the Editing Group, select the Fill option.
- Select the Series… option.
- Using the Series Dialog Box:
- For Series in check Columns. This means that Excel will fill the series down the column.
- For Type choose Linear.
- Enter a Step value: of 3. This means that the interval between each number will be 3. This is how we create multiples of 3 for our series.
- Enter a Stop value: of 21 This means our series will stop at the number 21.
- Click Ok. You should see the following.
Excel Fill Series Not Working Because Data is Filtered
Situation: If you use the Fill Handle while working within a filtered data range, you will find that some values are missing if you clear the filter.
Let’s look at the following example to see how Fill Handle works with filtered data.
We have a range containing Employee IDs, Employee First Names, Employee Last Names, and whether or not the employee at hand is a remote worker.
In column E, we’d like to have the full name of the employee. This will be created by combining the values in column B and column C in a formula and then dragging the formula down the column.
To filter the range, select any cell in the range. In this case we will select cell A2.
Go to the Data Tab, and in the Sort & Filter Group, select Filter.
You should see the following.
Select the arrow next to Remote Worker and choose Yes only. This will filter the range and only show those employees who work remotely.
Click Ok to see the following filtered range.
In cell E2, enter the following formula.
=TEXTJOIN(" ",TRUE,B2,C2)
Drag the formula down the column using the Fill Handle, to see the following.
If we clear the Filter from the Remote Worker Column, we are left with the following. We can see Fill Series only worked for the visible cells and we have missing values in column E.
Solution: So to fix this issue, do the following.
- Ensure that you clear the filter from the Remote Worker Column so that all the rows are showing. Delete all the values in column E.
- Type the formula =TEXTJOIN(” “,TRUE,B2,C2) in cell E2. Drag the formula down the column using the Fill Handle.
Now if you filter the range and then clear the filter, all the values should be shown.
So always make sure to check if your data is filtered before using the Fill Series option, or else, you will be left with blank cells.
Excel Fill Series Not Working Because Manual Calculation is Turned On
Situation: There could be times when a user has changed the Calculation setting to Manual. By default, the Calculation setting is Automatic, which means that Excel automatically computes and updates any formulas in the workbook. If a workbook contains many formulas then this can result in slow performance, hence the reason a user may opt to change the default setting to Manual.
This, in turn, will affect the Fill Series option.
Let’s take a look at a situation where this is the case.
Below we have a dataset showing a list of students and their respective test scores.
We want to calculate the average of the three test scores, for each student in Column E.
So in cell E2 enter the following formula.
=AVERAGE(B2:D2)
We will now drag the formula down the column using the Fill handle.
However, instead of dragging the formula down and computing the average in each case, we’ve simply copied the value down because the Calculation setting is Manual.
Solution: So to fix this issue, do the following.
- Delete all the values from column E other than cell E2, which contains our formula.
- Go to the Formulas Tab and in the Calculation Group, select Calculation Options.
- You should see that the Manual option is currently selected.
- Change the Calculation option from Manual to Automatic.
- Now drag the formula down the column using the Fill Handle to see the following.
The average of the three test scores for each student is now calculated correctly.
Excel Fill Series Not Working Because the Wrong Auto Fill Option is Selected
Situation: You can use Auto Fill when working with dates. If you drag down however, you may find that you don’t have the right Auto Fill option selected. Let’s look at an example to see what happens if this is the case.
We have a list of volunteers and we intend to populate column B using the Fill Series option. Since each volunteer comes in on a consecutive day of the week.
When we select cell B2, and drag down the column using the Fill Handle, we get the following.
The series has been filled with the days of the week including weekends. There is one problem since in this case the organization is only open on weekdays.
Solution: So to fix this issue, do the following.
- Delete all the values, except for the value in cell B2.
- Select the cell and drag down the column.
- Click on the drop-down arrow for Auto Fill options.
- Select the Fill Weekdays option.
5) You should now see that the series contains only Weekdays.
In this tutorial, I went over some instances where Excel Fill Series may not be working as expected and how to remedy this.
I hope you found this Excel tutorial useful. Let me know your thoughts in the comments section.
Other Excel articles you may also like:
