Fill Handle Not Working in Excel (4 Easy Fixes)

You might not even know what the Fill Handle is while it gets so much of the work done for you. Today, we’re not talking about how the Fill Handle works but about why it wouldn’t work. Details later.

First, what is the Fill Handle and why would it be hard to handle? That incredibly small green box that sits on the bottom-right of the select cell(s) is the, you guessed it, Fill Handle.

what is the Fill Handle

When you join the cursor to this box, it changes to a black cross and when you click and drag the Fill Handle, it fills the proceeding cells according to the value(s) of the selected cell(s).

what is the Fill Handle

In the case that these filled values aren’t what you’re aiming it or if the Fill Handle is nowhere to be seen, you need to check up on a few things; enabling Fill Handle, using other Auto Fill Options, changing Calculation Options, or selecting enough values for the Fill Handle.

We will guide you on how to do each of these to kick your Fill Handle into gear.

Let’s get handling!

Fill Handle Not Working in Excel

Enable Fill Handle Feature

Let’s straighten the basic matters first; is the Fill Handle even enabled? As shown above, when you hover the mouse pointer over the Fill Handle, it changes from a white bubble cross to a black line cross. This means that when we pointed our mouse to the Fill Handle, this shouldn’t have happened:

Enable Fill Handle Feature

The cursor is completely covering the Fill Handle but the black cross hasn’t appeared and it’s not just an aesthetic problem; if the Fill Handle doesn’t show, the features related to it can’t be used. The reason you’re not being able to see the Fill Handle is that it may have been disabled. See how you can get back the Fill Handle from Excel Options with the steps below:

  • From the tabs above the Ribbon, open the File tab and you will switch out from the active worksheet.
  • Select Options in the leftward green pane.

Enable Fill Handle Feature

  • You will arrive at the Excel Options window.
  • Click on the Advanced tab and select the Enable fill handle and cell drag-and-drop

Enable Fill Handle Feature

  • Then click on the OK button to close the Excel Options window and return to the worksheet.

Now when your mouse pointer goes atop the Fill Handle, the black cross should come back in action and so should the Fill Handle attributes:

Enable Fill Handle Feature

Recommended Reading: Arrow Keys Not Working In Excel – How To Fix

Use Other Auto Fill Options

What does the Fill Handle do when you use it to fill cells? It copies down a value, a formula, or a pattern along with the format of the original selection. Filling out serial numbering is a fairly fundamental concept and you probably expected Excel to pick up on it but it just copies the same value to all the cells:

Use Other Auto Fill Options

You could be thinking that the Fill Handle isn’t working but this auto filling is not inevitable and you can change the results of Auto Fill using Auto Fill Options.

Don’t see that anywhere? The Auto Fill Options appear for the auto filled selection when the Fill Handle is used. 4 basic options are available for auto filling text and formulas with additional options for numbers and dates. Here we can show you the basic and additional Auto Fill options:

  • Fill the target cells using the Fill Handle of the selected cell.
  • We have auto filled dates in our case example:

Use Other Auto Fill Options

  • When the target cells are filled, you will see an icon below the Fill Handle. This is the Auto Fill Options icon.
  • Click on the Auto Fill Options icon to see the options available for the type of auto filled data.

Use Other Auto Fill Options

  • In the menu, note that Fill Series is selected by default. That is how Excel behaves with filling dates; it will fill dates serially.
  • Select Fill Weekdays from the menu to readjust the results of the auto filled dates.

Use Other Auto Fill Options

The days that fall on the weekend as per the calendar will be removed and the target cells will be filled with the weekday dates:

Use Other Auto Fill Options

You just saw the options in Auto Fill with dates. Now let’s see how to change that list of 1s to serial numbers.

After using the Fill Handle to fill the target cells with the starting number, click on the Auto Fill Options icon and select Fill Series.

Use Other Auto Fill Options

Here in the Auto Fill Options, Copy Cell is originally selected which explains why only 1 (or the number in the selected cell) is copied when we drag the Fill Handle.

Fill Series will fill the target cells with serial numbers beginning with the number in the original cell:

Use Other Auto Fill Options

If there’s no other Auto Fill Option you want to make use of, you can select Fill Without Formatting so that the format of the original cell isn’t also copied down.

Use Other Auto Fill Options

This will save you the need of adjusting the formatting later, only picking up on the Auto Fill pattern:

Use Other Auto Fill Options

We filled the cells with a series and managed to evade the formatting of the original selection from being copied down. How did we use two Auto Fill options at a time? Read right ahead.

Select Enough Values Before Dragging

For Excel to fill the cells with a series, you need to provide it a pattern or a sequence that it can pick up on to apply Auto Fill. Let’s attempt to fill a simple series in a column. We start by entering 1 in the first cell.

Select Enough Values Before Dragging

But we know well by now that if we only use this entry and the Fill Handle, we will have a column of 1s:

Select Enough Values Before Dragging

So let’s add another number and see how Excel reacts. We’ll also supply the next number i.e. 2, select it and drag the Fill Handle down.

Select Enough Values Before Dragging

And that also gives us no luck. Why though? We had only selected B4 containing the number 2 so Excel didn’t get a pattern to pick on. The trick is to select B3 and B4 (numbers 1 and 2) so the sequence can be followed for the target cells.

  • Select B3 and B4 to supply the pattern for Auto Fill.

Select Enough Values Before Dragging

  • Then click and drag the Fill Handle down to the target cells.

Select Enough Values Before Dragging

The numbers will be filled in a sequence. Then use the Auto Fill Options to select Fill Without Formatting to remove the copied format.

Select Enough Values Before Dragging

And that my friends, is how you fill a series with the Auto Fill Options.

Change Calculation Options

If you’re getting a similar static result using the Fill Handle for copying down a formula, it might not be the formula’s or the Fill Handle’s fault. Take a look at the Calculation Options in the Formulas tab to check if formulas are set to be calculated automatically or manually.

The Automatic setting is by default but it may have been changed by the user at some point especially if the file has multiple users. The Manual setting fills cells with static formula results which again looks like there’s something wrong with the Fill Handle.

See the example below. We have entered a formula where the IF function would return a downwards arrow if the price compared to the previous day has dropped or an upwards arrow if it has increased. But looking at the results, it’s unlikely that there was always a decrease.

Change Calculation Options

C6 also shows a downwards arrow even though D6 is greater than D5 so there is obviously something going on here. Turns out, the Calculation Options were switched to manual. No problem, let’s fix that!

  • Open the Formulas tab and select the Calculation Options icon in the Calculation
  • Make sure it is set to Automatic.

Change Calculation Options

When the Automatic option is selected, the formulas in the worksheet will be recalculated and the static results from the Manual option will change:

Change Calculation Options

Note: The Automatic option recalculates formulas retrospectively while Manual recalculates prospectively; the formulas calculated with the Automatic option won’t change when you change it to Manual.

Today we discussed some glitches we might come across with a little thing we can take for granted; the Fill Handle. We also went through how to fix those glitches so that all is sailing smoothly. Other than the Fill Handle itself, there may be other factors hindering the propriety of filled data and now you know how to steer them away. Up for more Excel missions? Aye aye captain!

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.