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.
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
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 MoreAutoFill can be a great timesaver when adding information to a worksheet. Even so, some users may not want the feature to ...
Discover MoreIf you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-01-30 12:00:54
ee
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
Madhan
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
Abbie58
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
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
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:
Comments