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)

19

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

MORE FROM ALLEN

Footnotes within Footnotes

Need to add footnotes to your footnotes? It's actually allowed by some style guides, but Word doesn't make it so easy.

Discover More

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More

Using Call to Run VBA Macros

An elegant way to run macros from within macros is to use the Call statement. In order to use it, you need to provide a ...

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!

More ExcelTips (menu)

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

Quick AutoFill Variations

The AutoFill feature can be used for more than just incrementing information into cells. This tip explains how to access the ...

Discover More

Fast AutoFill

Want to fill a long column with predictive data? It's easy to do by using AutoFill and a double-click of the mouse.

Discover More
Subscribe

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

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. Maximum image size is 6Mpixels. 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 4 + 1?

2017-05-08 06:20:57

Cameron

Franks answer below sorted my problem. Autofill had been working fine and suddenly stopped and would copy cells instead (even if I tried to drag a 1,2,3 sequence it would just copy them all to 1). Turns out it was because I had Data Filtering on in that column.


2017-05-06 12:31:14

Kerry

Giving enough info, its still not auto-filling correctly. Yes, I see the + sign in the corner of the 2nd cell I am trying to pull down


2017-04-26 12:03:26

Rusesll Smith

I am using a Mac. I don't know if this applies but I have some VBA that inserts a VLOOKUP formula in a column. I know if I autofill in VBA I have to include the origin cell in the destination. My code works in Excel 2011 but in 2016 it enters the same value in each cell that receives the formula-just like Deb's. This is not just a problem with the interactive task as it happens with VBA also.


2017-02-04 17:29:40

dee

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

Howard

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

Tina

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

Jowesome

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

Frank

Autofill won't work correctly with data filtering on.


2015-10-25 17:44:41

WMGetz

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

Varma

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

Varma

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.

Thanks,


2015-10-08 12:22:15

Will

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

Person101

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

Mateusz

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

Jody

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

Justine

Thanks, Lucy!

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


2014-07-25 04:56:37

Lucy

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!


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

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.