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)

17

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

Searching for Character Formatting

Need to look for a piece of text possessing a particular formatting attribute? Here's the skinny on how this is accomplished.

Discover More

Using a Single Instance of Excel with Two Monitors

Working on a computer system that has multiple monitors can help increase your productivity. If you want to work with ...

Discover More

Converting to Word 6.0 Format

Need to convert your document to a really old format previously used in Word? The solution may already be built into the ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (MENU)

AutoFilling with the Alphabet

If you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ways ...

Discover More

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
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 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 two more than 9?

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.

Links and Sharing