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

Written by Allen Wyatt (last updated March 31, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003


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


Preventing the Loss of Personal Information

For security purposes, you can configure a document so that no personal information is stored with the document. But what ...

Discover More

Starting a New Section on an Odd Page Number

A common pagination convention used in publishing is for new chapters (and sometimes new sections) to start on an ...

Discover More

Jumping Back to the TOC

Word allows you to create a table of contents that provides hyperlinks to headings within your document. It doesn't make ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

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

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

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

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.


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}] (all 7 characters, in the sequence shown) 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 seven minus 1?

2020-01-30 12:00:54


i am currently using ms excel from office 365, and as per mentioned above:
"...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....."

the autofill will SOMEtimes work as expected, while other times it will simply override ALL numbers in the column by copying the 1st # into all highlighted rows down the column.

i have also tried your suggestion by holding down the CTRL key (made 2 attempts, 1st by pressing/holding the CTRL before clicking on the autofill handle and dragging down the column and the 2nd by clicking and holding the autofill handle 1st then click/hold the CTRL key before dragging down the column). this did not work either.

i have DBL-checked the autofill setting in the options area and it is checked.

2019-08-10 06:58:56


Not Working. Even when I select 1,2,3.... the autofill is not incrementing the sequence but fill in the series 1,2,3,1,2,3,1,2,3.....
what to do now.

2019-07-31 05:23:41


You are brilliant. Tried I don't know how many other "tips". Yours is the only one that actually works & makes sense. Thankyou.

2018-12-14 13:36:25


Does not work. My file will not increment whether I have 1, 2, or 20 cells copied with 1, 2, 3... filled in.
Be more thorough with your advice.

2018-12-13 07:40:18


I somehow managed to disable this function too - wtf? New documents work though. I checked the ribbon and it's greyed out?

2018-10-30 17:39:35

doesnt matter

Nope, try again... Have tried selecting 2, 5, 10 cells before dragging, and still only picks up the first number in the series...

2018-07-12 12:39:06

Lauren Taylor

I'm having a similar issue in Excel all of the sudden when trying to Autofill with formulas. Instead of dragging down the formula, the cross cursor just copies the value from the first cell. I've used this functionality in Excel for years and it has all of the sudden quit working and it's driving me CRAZY! I've checked my settings under Options > Advanced and all seems to look correct. Any help?

2018-04-30 18:47:09

auto-fill not working

I discovered that auto-fill stopped working at line 315 in my spreadsheet. To fix, I opened a new sheet, copied a couple of cells into a1 and a2, and auto-fill worked. I then copied over to my other sheet. Its a work-around rather than a fix but I got what I wanted.

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

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.