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.

AutoFill Won't Work as Expected

by Allen Wyatt
(last updated October 24, 2015)

31

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.

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

Working with ISO Files

ISO files provide a digital copy of what is stored on an optical disc. Windows allows you to treat these files as if they ...

Discover More

Editing in Print Preview

Long-time users of Word who make the switch to Word 2007 may be wondering how to do some of the tasks they took for granted ...

Discover More

Jumping to a Relative Section

Navigating through a long document can be challenging, at times. Here's a way you can move forward or backwards in your ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Fast AutoFill

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

Turning Off AutoFill

AutoFill is a great editing tool that comes in particularly handy when you are first creating a worksheet. You may, at some ...

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

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 four less than 9?

2017-06-12 12:06:02

Julie

Filters!!! Thank you! This was making me crazy!!


2017-05-18 14:12:51

Steven Laux

I ran into the same issue and found the solution to be to change the value under the "Calculation options" section, then "Workbook Calculation" File>Options>Formulas to be set to Manual when I really desire Automatic. Strange that there is a Manual option; I cannot fathom such a need...and it did screw me up.


2017-04-25 16:42:47

K

Thank you


2017-03-25 00:06:10

Tom Stricker

No. I make sure autocomplete is checked in Files, Advanced, Options. Then I enter 1:7 in cells a1:a7, then select cells a1:a7, use the file handle (+) and drag down, but the cells repeat the same number that is in a1 (1). Very frustrating and the above fix does not work. This is maybe an enhancement (???) to MS Office 2016? r
(see Figure 1 below)

Figure 1. 


2017-02-14 20:31:56

Mert

"If filters are turned on, they may cause the same number to repeat, even when selecting more than one cell."

Thanks 2 !!!

And Allen, sometimes it always repeats the selected cells. For example If I have 1 in A1, 2 in A2 and I select both of them and drang them down, it repeats as 1 to A3, 2 to A4, 1 to A5, 2 to A6. What is the reason for that?


2017-01-25 13:16:51

diane livia

"If filters are turned on, they may cause the same number to repeat, even when selecting more than one cell."

Thanks!!!


2017-01-18 12:12:56

Dale

When dragging the fill handle in Microsoft Excel 2013, the quick analysis tools opens vice the auto fill. When the quick analysis is disabled by going to File/Options/General, it also disables the auto fill from showing despite enabling the fill handle and cell drag-and-drop under File/Options/Advance. This is occurring on a single worksheet of a workbook file. The other worksheets operates correctly. The quick analysis tool should show only when multiple cells are selected and not following cell drag-and-drop using the fill handle. Would you know why this is happening to a single worksheet?


2017-01-10 02:55:11

Simon

But what makes no sense is that autofill defaults to incrementing single dates when dragging down, and holding down CTRL STOPS the date incrementing. The icing on the cake is that the little + appears by the mouse in both cases when CTRL is held down! If there is one thing that sums up all that is wrong with MS and OFFICE, this is it: implementing the same feature in completely opposite ways...


2017-01-09 23:25:50

Mat

Filters, filters, filters - for me it's always filters! Thanks for the reminder!


2017-01-07 03:20:20

Roger

I'm using office for mac 2011. The auto fill function has worked fine but now when I insert day, month, year in a column and pull the cross down the column the only change now occurs in the year so the row increments are shown in years.

I'm stumped and would be very grateful if you could provide any help.

Many thanks

Roger


2016-12-16 13:35:13

vlas

Thanks a lot. Info that filters can change behavior helps me


2016-11-26 19:54:42

Sophia

I entered formulas like this in cell A1, B1, C1, ... : =100*1.01^1, =100*1.01^2, =100*1.01^3.

What I expect the autofill to do is to continue until =100*1.01^20.

However, it repeats 1, 2, 3 for a couple of times.

It doesn't work like this when I take out the = sign.

I'm wondering how to fix this issue.


2016-10-17 11:08:15

tisha

I'm so glad this article mentions the filters!


2016-08-27 10:53:48

Col Gahlot

I have using excel for over 15 years.
I am familiar with autfill and have been using it. For the last few months it has stopped working for me.
I am using a PC with Windows 10 and Office Home and Student 2010


2016-07-27 09:34:45

Chad Bennett

The issue I am having is I am trying to get a series of numbers to repeat for 5000 rows. The series is 1 1 1 2 2 2 3 3 3, and so on, so that each number repeats three times. When I try to AutoFill with 1 1 1 2 2 2 selected Excel adds the following 3 3 3 3 4 4 4 5 5 5 5 6 6 6...why has it made 4 of the next number and then 3 of the next?


2016-07-18 19:13:19

Clark

Based on my research of the subject, there seems to be little rhyme or reason why this simple operation doesn't work consistently w/ filtered data and there doesn't seem to be a straight-forward fix; at least none that I could find.

So, I offer this work-around for assigning a sequential series to filtered data (let's assume we want to start at "1" for our sequential series in Col A beginning in cell A2, where cell A1 is a header field):

1) Ensure all data filters are turned off so that your full dataset is visible

2) In cell A2, insert a value for the start of your range, in this case "1"

3) In cell A3, insert the following formula:

=MAX($A$1:A2)+1

4) Drag/copy this formula down in Col A for all rows of existing data so that all rows of data are sequentially numbered, starting at "1"

5) Set filter(s) to display all the rows of data that you DO NOT want to be assigned a sequential value in Col A

6) Select and delete all values in Col A for the aforementioned filtered data

7) Turn off data filters

You should now have a select set of rows w/ properly assigned sequential values in Col A. To check/confirm, filter Col A for "Blanks" to only display data w/ newly assigned sequential values.

8) Optional: select all of Col A (i.e., click on the letter "A" above cell A1), Copy selected data range, and Paste Special - Values back into Col A.


It shouldn't be nearly this difficult for such a simple operation, and it seems like Excel is going backwards by taking away functionality that used to exist. I consider myself a proficient and savvy Excel user, and I've performed this type of operation on filtered data countless times w/out any problems. It makes no sense as to why this is suddenly occurring; fortunately, I've learned that for every short-coming that exists w/ Excel (and other Microsoft products), there is usually some clever work-around.

Hope this helps those who were, and continue to be, as baffled as I...


2016-07-18 17:12:50

Clark

I was previously working in the same spreadsheet, with my data filtered, and had no problem getting the drag-fill operation to work as expected (e.g., 1, 2, 3, ..., 100, etc). However, now when I try to perform the same steps it won't work as before and instead repeats values in the selected range (e.g., 1, 2, 3, 2, 3, 2, 3, ...). Like I said, it worked fine before w/ filters turned on, but now it doesn't, any explanations/tips/work-arounds?


2016-07-15 18:49:29

Viccy

Thank you so much.

I was really frustrated that a simple task like adding an index column was not even working until I read this article and realized that I had filters on.

This is such a great find.

Thank you!!!


2016-07-10 16:49:39

Robin

Hi!
I am trying to autofill a very simple formula in Excel 2011 with my Mac OS 10.7.5. In column D I have one small number and in Column E I have another small number and I need to add these two together row by row in Column F. Simple formula. =(D2+D2). I have grabbed to the handle to autofill, copied and pasted, added the formula to several cells and grabbed the handle to autofill, copied and pasted several cells at a time, all to no avail. It copies the formula, but the answer is the number that first appeared in the original cell. I can force Excel to calculate the formula, which is there, and come up with the correct answer by clicking on the formula bar and hitting
my Enter key. I have 4600 cells I need to do that on, just for one set of calculations needed and I have 26 sets that I need. How can I resolve this? I have seen that it truly is an Excel bug and has been a problem over the years, but no one has the answer. Please help? Thank you!


2016-07-02 12:25:53

debbie

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. How do I clear filters? I keep getting no 1 repeated even though I a doing it correctly thanks


2016-06-02 03:51:46

Simon Brown

The fact that Auto Fill simply fails to work when a spreadsheet utilises filters on the columns is atrocious. - A really blatant Bug that needs fixing & should never bhave made it through testing & into production releases of Excel.
Please fix this ASAP.


2016-03-11 17:06:20

PB

FILTERS! So obvious and yet couldn't see the forest for the trees. Thank you.


2016-02-17 20:01:30

Jen

For those like me who have been looking for a way to FORCE COPY rather than fill, using only a single cell as a source, here is an easy tip:

Under the source cell, select the destination cells, hold Ctrl + " then hit Enter.

Ctrl + " copies the cell above and will do so into multiple selected cells using the enter. I find this heaps quicker when you know you want to copy not fill


2016-01-19 22:31:32

Chandra

If auto fill does not work in 2010 then follow the steps
1.Click on FILE
2. GOT TO "ADVANCED"
3. UNDER EDITING OPTIONS "CHECK" Enable fill handle and cell drag-and-drop
4.Click OK


2015-12-20 16:37:16

napo

My autofill doeesnt work for months (january and so on ) and days (Monday and so on)
I have tried everything, dont know what to do


2015-12-09 02:08:41

Steve

Hi Allen, great tip.

The filter in my sheet was enabled. With your tip it worked. I use Excel now for several years, but this was too much for me to solve by myself.


2015-10-26 10:19:07

rpurosky

Hey, Dan. As the tip above describes, a number (formatted as a number) will only repeat itself with Autofill. However, a number formatted as text will increment by 1. Your example "55-55" actually treats the "55-" as text and the 55 suffix as a number formatted as text, so increments the second 55 (55-56, 55-57, etc.). I assume your "5" example referred to the number 5 formatted as text and it will increment as expected, but all those cells will also be numbers formmatted as text. Likewise if you entered Room 5, Autofill would increment the next cells to Room 6, Room 7, etc. One of my faves is Quarter 1 since Excel knows the word "Quarter" and will only go to 4, then cycle back to 1 and repeat.

So, for numbers formatted as numbers, if you enter 55 in the first cell, 56 in the second, highlight both, then use the Autofill handle, you will get the progression. Likewise if you entered 55 in the first cell, 60 in the second, highlight both cells and use Autofill, you will get that progression of 55, 60, 65, 70, etc. The progression will also work in reverse (try 55, 54) and decimals (try 55, 55.1). I think because it is so flexible, it requires the second cell because there is an infinite number of progressions. Why would anyone just want a progression of 1? So yo have to tell it.

More fun with Autofill for you virgins, try autofilling Monday. Try January. Excel already knows these words and they will also work with a two-cell progression if you don't want to go to the very next one in line. Try a date. Try 1st. Try all sorts of stuff! You'll learn! Advanced users can create their own custom progressions. Seek Autofill help on that.


2015-10-24 23:29:48

Theivaseelan

very helpful


2015-10-24 17:05:22

Dan Tischler

Why is that that if I put this number in a cell - "55-55" or "'5", AutoFill will work
but if I put the number 55 in a cell it will not work (unless you hit ctrl...)
this is very strange.
I tried to format the number as text but no AutoFill.


2015-10-24 08:22:01

mahmoud

thank you very much good information


2015-10-24 07:48:15

Chris Finn

Another reason I have encountered for cells incorrectly filled is that the cells selected are denoted as Text.


This Site

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.

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.