AutoFill Won't Work as Expected
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.
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Leave your own comment:
Comments for this tip:
Jowesome 18 Mar 2016, 00:38
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.
Frank 14 Mar 2016, 18:05
Autofill won't work correctly with data filtering on.
WMGetz 25 Oct 2015, 17:44
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 :/
Varma 10 Oct 2015, 17:12
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 -_-
Varma 10 Oct 2015, 17:02
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.
Will 08 Oct 2015, 12:22
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.
Person101 11 Aug 2015, 10:05
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?
Mateusz 08 Jun 2015, 17:16
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.
Jody 11 Mar 2015, 12:20
Clearing the filters fixed the issue for me. Thanks! Overlooked that little detail.
Mike Seden 12 Jan 2015, 19:49
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.
Justine 06 Jan 2015, 02:12
That's exactly what fixed it for me: clearing all the filters.
Lucy 25 Jul 2014, 04:56
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!