See all How-To Articles

Fix Fill Series When Not Working in Excel & Google Sheets

This tutorial demonstrates how to fix fill series when it is not working in Excel and Google Sheets.

 

fixfill intro

 

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:

  1. In the Ribbon, select File > Options and then select Advanced.
  2. Make sure Enable fill handle and cell drag-and-drop is checked, and then click OK.

 

fixfill-options

 

  1. Now check that the fill handle appears.

 

fixfill show fillhandle

 

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:

 

fixfill show formula

 

The formula in cell E3 calculates total employee pay. Use the fill handle to drag the formula down to the remaining employees.

 

fixfill show fill down

 

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.

 

fixfill show automatic calc

 

Now, when you drag the fill handle down, the results should be correct.

 

fixfill show calculation fixed

 

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.

 

fixfill filter

 

When you copy the data down using the fill handle, it appears correct.

 

fixfill fill filtered

 

However, when you remove the filter, you see some cells are blank.

 

fixfill remove filter

 

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.

 

fixfill fill gs filtered

 

When you clear the filter, you see that there are some cells missing formulas!

 

fixfill gs clear filter

 

As with Excel, make sure you clear any filters before using AutoFill.