Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: AutoFill Won't Work as Expected.

AutoFill Won't Work as Expected

by Allen Wyatt
(last updated April 8, 2016)


Deb is trying to AutoFill record numbers in a column of her worksheet. The first record 1, the second record 2, etc. The "Enable Fill Handle" box is checked in Excel options. However, when she uses the AutoFill handle, it just pastes the same number in every cell. Deb is wondering how she can fix this.

Based upon the described behavior, it sounds like you are trying to use AutoFill to create a series (1, 2, 3, etc.) when you aren't giving AutoFill enough information to determine that is what you want to do. For example, enter the value 1 into cell A1 and the value 2 into cell A2. If you select cell A2 and use the AutoFill handle to drag downward, what happens is that Excel fills each of the cells with the value 2. Why? Because you only started by selecting a single cell.

On the other hand, if you select cells A1:A2 and then drag down, AutoFill will increment the value placed into each of the cells. Why? Because it can analyze what you started with to determine you want to fill the cells with a series of values.

If, for some reason, you don't want to start with a selection of two cells, you could simply select one cell (A2) and hold down the Ctrl key as you drag the fill handle downward. AutoFill will override its normal determination (copy the selected value downward) by forcing a series of numbers into the filled cells.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3841) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: AutoFill Won't Work as Expected.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Automatically Printing a Range

If you want to automatically print a particular area of your worksheet at a specific time of day, you'll love this tip. With ...

Discover More

Going to the Corners of a Selected Range

When you select a range of cells (particularly if it is a large range of cells), you may not be quite sure if you've selected ...

Discover More

Sequentially Numbering Elements in Your Document

One of the most powerful and useful fields provided by Word is the SEQ field. This tip describes how you can use the field to ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Turning Off AutoFill

AutoFill can be a great timesaver when adding information to a worksheet. Even so, some users may not want the feature to be ...

Discover More

AutoFilling with Weekdays

Need to fill a range of cells with the days of the week? Excel makes it easy to do so using AutoFill.

Discover More

Skipping Rows when Filling

Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill cells ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 2?

2017-02-04 17:29:40


I am trying to auto fill dates. The dates should increase by the days of the month (8/1/2016, 8/2/2016..) instead its increasing by the year (8/1/2016, 8/1/2017...) How do I change this???

2016-12-20 22:37:02


Thanks Varma and WMGetz! Your tips are really helpful

2016-11-29 11:06:05

Bill G

For some reason autofill sometimes defaults to repeating a sequence instead of continuing it (no filters). A1 is a, a2 is b but the sequence repeats (a, b, a, b, etc.) If I substitute a1 with 1 and a2 with 2 the sequence continues as desired (1, 2, 3, 4). Why can't I get the letters to sequence properly?

2016-11-04 17:35:55


I am having a problem with the fill handle. In creating an amortization table, I have two dates but when I try to fill the rest, the years increase but the months remain the same. The dates are 9/1/2014 and 10/1/2014 so when I try to fill the next date should be 11/1/2014 but it is 9/1/2015 instead. Help, please?

2016-03-18 00:38:49


Additionally I had a problem of not being able to series because the formula had ='[Other book.xlsx]Sheet1'!$A$1. The dollar signs are when you want to anchor the cell and therefore you can only copy when dragging, not series fill it. Converting the formula to ='[Other book.xlsx]Sheet1'!A1 will allow to series fill.

2016-03-14 18:05:08


Autofill won't work correctly with data filtering on.

2015-10-25 17:44:41


For me I needed to make sure "Workbook Calculation" was set to automatic under Options>Formula. I had disabled it for other reasons. Drove me nuts :/

2015-10-10 17:12:23


Update to the previous post I made:
Turns out my Calculate options were set at Manual. I changed that to Automatic and it fixed it. Never seen that before! Did not know fresh installs leave a lot to your customisation -_-

2015-10-10 17:02:00


Hey guys,
I'm facing what seems like a glitch. Can't find a similar occurrence anywhere else.
I'm used to the CTRL D shortcut to copy values or formulae down from any cell while on windows as well as on Office 2011. On 2016 for MAC, the CMND+D or CTRL+D combo does this weird thing where the formula (as per the formula bar) is copied down as required, but the value displayed in the cell is EXACT VALUE as the cell above. i.e.: Cell A2 = "A1 + 100". if I drag this from A2 downwards, all the cells will have the value calculated as "A1+100". Everywhere. But if I look at the formula bar from A3 downwards, the formula reads A2+100, A3+100, A4+100 ... and so on. When I enter the formula bar (click or F2 key) and hit ENTER without changing anything, the correct calculated value is displayed. So I'm forced to manually enter and exit the formula bar for every cell! Interestingly, the click and drag move does the same thing. Is this an issue or just a setting error? I've restarted both Excel and my machine to no difference.


2015-10-08 12:22:15


Great tip Lucy! Thanks. That had me stumped for a while, but clearing the filters fixed it.
It's annoying functionality though - I'd prefer to keep my filters on AND use autofill. It even prevents autofill outside the filtered table - an unfortunate backwards step in functionality for Excel2013.

2015-08-11 10:05:47


I have the same problem and found the same solution. However, autofill used to fill in series even with a filter on. Why can it not now?

2015-06-08 17:16:02


I had issue when autofill stop working for whole sheet.

It looks like, if you put filter on one of the column and then try to create sequence by autofill it won't work just copy first value written.

2015-03-11 12:20:37


Clearing the filters fixed the issue for me. Thanks! Overlooked that little detail.

2015-01-12 19:49:01

Mike Seden

I am using excel 2013. I have one spreadsheet that will not increment even after doing all of the suggestions including unchecking the box in options ,saving it, re-opening and rechecking the box... well you get the idea. It seems to be the only spreadsheet that does this. I have re-formatted to numbers,cleared all conditional formatting rules,etc.
Any ideas?

2015-01-06 02:12:07


Thanks, Lucy!

That's exactly what fixed it for me: clearing all the filters.

2014-07-25 04:56:37


Useful tips but you could be having the same problem as me. Basically it wasn't working as the spreadsheet was filtered.

When I was dragging down it looked like it was counting on correctly then when I released they all reverted back to the same number!

Realised i had the filter on and cleared it and worked fine. It musn't have been able to copy down the series as rows were missing.

So another tip make sure you clear all your filters first!

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing