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.
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).
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!
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:
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.
- You will arrive at the Excel Options window.
- Click on the Advanced tab and select the Enable fill handle and cell drag-and-drop
- 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:
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:
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:
- 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.
- 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.
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:
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.
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:
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.
This will save you the need of adjusting the formatting later, only picking up on the Auto Fill pattern:
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.
But we know well by now that if we only use this entry and the Fill Handle, we will have a column of 1s:
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.
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.
- Then click and drag the Fill Handle down to the target cells.
The numbers will be filled in a sequence. Then use the Auto Fill Options to select Fill Without Formatting to remove the copied format.
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.
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.
When the Automatic option is selected, the formulas in the worksheet will be recalculated and the static results from the Manual option will change:
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!
