Excel fill series not working Office 365

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:

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.

1. 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.

Are you the one facing Excel Fill handle not working issue while using Autofill Excel feature?

Well, no one wants to deal with the problems raises due to Excel fill series not working issue. As we all know how useful this Excel file handle tool is to auto-fill the list in worksheet columns or rows just by dragging through the mouse.

But sometimes the situation may arise where fill series in Excel not working properly. So to tackle such a situation it’s important to know how to fix Excel fill won’t work issue.

That’s why I have outlined some best fixes to resolve Excel fill series not working issue in this blog post.

To extract data from corrupt Excel file, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
3. Preview the repaired files and click Save File to save the files at desired location.

User’s Query:

Let’s know about abut this Excel drag not working issue with one such user experience:

All I want to do is continue a series of numbers vertically. I’ve done this many times before but now the fill handle is simply repeating the same number in each cell. I have checked that the column is formatted for numbers, the fill handle is enabled and ‘show paste options’ is checked, but I’m not getting any options

Original Post from Excel Forums.

Well, this is really very irritating but not a problem that can’t be fixed. Today here in this article I am putting together some of the working solutions that help you to resolve the fill handle in Excel not working issue and bonus tips to use fill handle effectively.

How To Fix Excel Fill Handle Not Working Problem?

Well, there is no particular reason behind Excel Fill handles not working issue. So, here we have tried some of the possible working solutions.

Try this…

Method 1: Enable File Handle Option

If Excel fills series not working then the reason can be also that the fill handle option is not enabled. Although this feature is by default comes enabled but may be due to your mistake somehow it got disabled.

So here is how to enable Excel fill handle feature:

• Go to the File menu and then hit the Options tab. This will open the Excel Options window on your screen.
• Choose the Advanced option from the left pane of the Excel Options window.

• Now go to the section of Editing Options and put a checkmark across “Enable fill handle and cell drag-and-drop” option.
• Hit the OK button.

Method 2: Remove Filters

Well, if you have added filters then this makes the fill handle cause an issue. So, many users state that removing filters from the columns in your Excel workbook solves the issue.

After removing the filters the fill handle began working again.

Therefore it is worth checking if you had added column filters than removing and check whether the fill handle not working issue is solved or not.

However, if you are still facing the error then follow the second solution to solve the issue

Method 3: Choose Automatic Calculation

Checking the Automatic in Formulas tab resolves Excel Fill Handle not working properly issue for many users.

So here know how to do so:

• Go to the ‘Formulas’ tab
• And then go to the ‘Calculation Options’ from the drop-down menu
• Make sure ‘Automatic’ is checked.

 Hope this works in your case but if not then here is another trick.

Method 4: Press F4 key

This solution is the easiest trick to solve Excel fill handle stopped working issue.

Select the cell value and press F4 key 3 times and doing this trick will solve Excel fill handle issue.

Actually the fact behind this is when you choose a cell and then press F4 key the last action that you have performed will get repeated. This last action can anything like deleting rows, filling up data in cells, formatting, inserting, or doing changes in the shapes.

Well, these are some of the working solutions to repair Excel fill handle issue. But if after following the given manual solutions you are still facing the issue then make use of the automatic MS Excel Repair Tool.

Automatic Solution: MS Excel Repair Tool

If you are unable to perform any action in your Excel worksheet then chances are high that your Excel file is got corrupted somehow.

So make use of the professional recommended MS Excel Repair Tool to repair corrupt, damaged as well as errors in Excel files. This tool allows you to easily restore all objects from corrupt Excel files including the charts, worksheet properties cell comments, and other important data.

• With the help of this, you can fix all sorts of issues, corruption, errors in Excel workbooks.
• This is a unique tool to repair multiple Excel files at one repair cycle and recovers the entire data in a preferred location.
• It is easy to use and compatible with both Windows as well as Mac operating systems.
• This supports the entire Excel version and the demo version is free.

* Free version of the product only previews recoverable data.

Steps To Utilize MS Excel Repair Tool:

Best Tips To Work With Excel Fill Handle Effectively

Despite all this here follow tips that will help you to work efficiently with Excel Fill Handle. Right knowledge of how to use the fill handle is also equally important to avoid Excel drag not working while using the autofill feature.

1: Copy values

As I said above it is equally important to know how to use the fill handle to copy values early on.

To do so, select the value you want to copy and hold the fill handle by clicking on it. After that holding down the left mouse key, drag it in the appropriate direction.

In this way Excel copies values to the selected cells.  Now make use of the fill handle to instantly copy values.

To increase values, enter enough values to differentiate a pattern.

For example – You may enter 1 in A1 and A2 to fix a pattern, that increases by 1 – 1, 2, 3, and so on.

But if you type 1 & 3, then the fill handle will increase by 2 – 1, 3, 5, and so on.

Now, hold the Ctrl key during dragging the fill handle will also force an incremental series.

2: Take drop-down help

Well, the fill handles alone not always do what you want. So when this is the case, there are two options:

• Right-click fill handle > drag to display the context menu.
• Or left-click and drag to display the context menu.

However, not all options are valid for the source value.

So, determine the copy action after dragging.

If you select Series, Excel displays the dialog shown in the image below.

You can create any series you require with the help of this one dialog. Make use of the Series dialog to handle series that are difficult.

3: Double-click

In some cases, the target range is large enough that the fill handle becomes a bit discomfited.

Well, it is quite difficult to imagine dragging fill handle for thousands of rows. So in this case, double-click the fill handle instead of dragging it.

Here follow some simple rules:

• When the fill handle is double-clicked, Excel uses the values in the left column to determine how far to copy and the Excel stops as a blank cell is found.
• If no values are there to the left, then Excel will use the values in the column to the right.
• If to the left or right there are no values, then this trick doesn’t work

4: Include text

If you type text with a number, then Excel’s fill handle will accommodate word, in a prospective manner.

For Example, Excel knows there are just four quarters. And if you copy Quarter 1 to five cells, this will repeat the series.

But if you don’t want a series but need to copy the actual text, then hold down Ctrl while dragging. The Ctrl key force Excel to do the opposite to its default behavior

The fill handle creates all these series.

5: Insert cells

Press the shift key during dragging the fill handle to insert cells.

For instance: select A3:F3, and right-click the selection, select Insert > choose an insert option, and click Ok.

One can also hold the Shift key and drag fill handles the number of rows you drag through verifies the number of rows Excel inserts.

By dragging, select the number of rows that you want to insert.

FAQ:

What’s This Fill Handle In Excel?

Fill handle in Excel is mainly used with an objective to saves time and to make more productive. Using this Excel tool one can autocomplete the lists in their Excel worksheet within a few seconds.

With the help of this user can extend a series of numbers, dates, or text to the desired number of cells.

Excel’s fill handle is a very flexible tool and can instantly fill thousands of rows all you need to know when to drag, when to click and when to hold down the alternate key to fulfill its potential.

Suppose, you want to assign numbers from 1 to 20 in your worksheet cells A1:A20. So instead of manually entering each number, just enter 1st two numbers and then make use of Excel fill handle feature to perform the rest of your task.

Why Is My Fill Handle Not Working In Excel?

Following reasons are responsible for fill series not working in Excel issue

• The very first reason behind fill handle not working in Excel can be disabled Fill Handle option.
• Excessive application of filters in Excel worksheet.
• Excel file got corrupted somehow.

How Do You AutoFill In Excel Without Dragging?

To quickly autofill in excel without dragging you need to perform the following steps:

• Enter the number 1 in your worksheet cell A1.
• Follow this path: Home –> Editing –> Fill –> Series.

• Now in the opened dialog box of series you need to make following selections:

Type: Linear

Stop Value: 1000

Series in: Columns

Step Value: 1

Now you will see that the cell from A1 to A1000 is filled with number 1 to 1000.

Conclusion:

Hope this blog will help you to fix Excel fill handle not working properly issue.

Make use of the given solutions to resolve the issue. But if the solution won’t help you then make use of the Automatic solution, this is best and efficient to fix any Excel issues as well as errors.

Also, I tried my best to put together some effective tips that will help to work flawlessly with fill handle in Excel.

If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can also ask it on our Facebook and Twitter page.

Good Luck….