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 March 31, 2018)

5

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

Simultaneous Scrolling

If you have two worksheets displayed at the same time, you might want those worksheets to remain visually "in sync" with ...

Discover More

Displaying Row and Column Labels

When you create a worksheet, it is common to place headings at the top of each column and the left of each row so you can ...

Discover More

Altering the Displayed Format of Numbers to the Nearest 100

Want information in a worksheet to be formatted and displayed as rounded to a power of ten? You may be out of luck, ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 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

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

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
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 two minus 1?

2018-12-14 13:36:25

Ryan

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

okidoki

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