Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: AutoFill Won't Work as Expected.
by Allen Wyatt
(last updated October 24, 2015)
Deb is trying to AutoFill record numbers in a column of her worksheet; record 1 in the first cell, record 2 in the second cell, 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 Deb is trying to use AutoFill to create a series (1, 2, 3, etc.) when she isn't giving AutoFill enough information to determine that is what she wants 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 the information you started to enter in the cells and use it to determine how to fill the remaining 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.
If you're still having problems, make sure that your filters are clear. If filters are turned on, they may cause the same number to repeat, even when selecting more than one cell.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11634) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: AutoFill Won't Work as Expected.
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!
Need to fill a range of cells with the days of the week? Excel makes it easy to do so using AutoFill.Discover More
If you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ...Discover More
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
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.