Fix Fill Series When Not Working in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to fix fill series when it is not working in Excel and Google Sheets.
The fill handle is a useful tool for automatically filling data in Excel, but it can be annoying if it does not work as it should. Read on for ways to fix this problem should it arise.
Enable Fill Handle
The first thing is to ensure that the fill handle is enabled. If the fill handle is enabled, when you rest your mouse on the bottom right-hand corner of a cell, the mouse pointer changes to a small black cross. If this does not happen, your fill handle is not enabled.
To enable the fill handle:
- In the Ribbon, select File > Options and then select Advanced.
- Make sure Enable fill handle and cell drag-and-drop is checked, and then click OK.
- Now check that the fill handle appears.
Select Automatic Calculation
If your fill handle does appear, but when you use it, the data is not what you expected, it may be that your worksheet is set to manual calculation.
Consider the following worksheet:
The formula in cell E3 calculates total employee pay. Use the fill handle to drag the formula down to the remaining employees.
When you do this, the pay is the same of each of the employees even though the hours worked and hourly rate differ. The fill handle is clearly not working correctly!
To fix this issue, in the Ribbon, select Formulas > Calculation > Calculation Options > Automatic.
Now, when you drag the fill handle down, the results should be correct.
Filtered Data
If you have filtered data and are trying to use the fill handle to fill the series, this too could cause problems.
Consider the following worksheet:
The (1) formula is created to be copied down (2) using the fill handle. However, the (3) data is filtered.
When you copy the data down using the fill handle, it appears correct.
However, when you remove the filter, you see some cells are blank.
The secret is to make sure your data is not filtered before using the fill handle!
Fix Fill Series in Google Sheets
Google Sheets neither has the option to switch AutoFill off nor manual calculation, so those reasons wouldn’t account for fill series not working.
However, Google Sheets won’t fill a series correctly if you have a filter set on your worksheet.
In the example below, you created a (1) formula in F3 and used AutoFill to copy the formula down to F20. However, the data is (2) filtered by Department.
When you clear the filter, you see that there are some cells missing formulas!
As with Excel, make sure you clear any filters before using AutoFill.
