Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated March 10, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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, 2013, 2016, 2019, and Excel in Microsoft 365. 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. ...


Adding Information after the Endnotes

Endnotes appear at the end of the document, right? Not always, as Word provides a way that you can actually add as much ...

Discover More

Turning Off Sharing

All good things must come to an end at some point. When you are done sharing your workbook with others, this is how you ...

Discover More

Automatically Sorting as You Enter Information

When entering information into a worksheet, you may want it to always be in a correctly sorted order. Excel allows you to ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Turning Off AutoFill for a Workbook

Don't want people using your workbook to be able to use AutoFill? You can add two quick macros that disable and enable ...

Discover More

AutoFilling with the Alphabet

If you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ...

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

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 3 + 4?

2020-12-08 17:00:27

Paul H.

Thanks, Allen, it had been so long since I had used AutoFill that I had forgotten the necessity of holding the Ctrl key for override! Your post here has set me straight again.

2020-11-02 09:15:32


How about just =35+((C2-1)*15)
If C2=1 that would be =35+((1-1)*15)=35+0=35
If C2=2 that would be =35+((2-1)*15)=35+15=50

2020-10-31 11:58:44

Peter Atherton

You included a comma after the last statement. try:= =IF(C2=1,35,IF(C2=2,50,IF(C2=3,65,IF(C2=34,80))))

or shorter =20+15*C2

2020-10-30 13:31:33


I m trying to auto fill column 1 with a number 1-7 in each cell. Each number has an assigned value that doesn't change - IE: 1=35, 2=50, 3=65, 4-80, and so on (technically if it makes the formal writing easier each number past the initial 35 will increase by +15).

Currently I am trying to use the formula =IF(C2="1",35,IF(C2="2",50,IF(C2="3",65,))) but it says this formula is not valid.

2020-09-19 10:20:17

J. Woolley

Are the "dates" actually text instead of date values (numbers)?

2020-09-18 14:59:34


I am trying to use auto-fill using dates as the data. I've used the auto-fill function by "dragging down the handle" many many times without issues in other spreadsheets, but for some reason this particular spreadsheet always duplicates the dates, regardless of whether I select two cells with sequential dates (ex: Aug 7, Aug 8) or not.

Have you ever heard of this happening? I'm not finding anything else related to that... :(

Any help would be much appreciated. :)

2020-04-03 11:15:56


Is it Autofill when Excel "predicts" the pattern you are creating from data in other columns? If not, what is it called (and can I presumptuously request a tip entry on it?)

I'm referring to the following "feature":
(see Figure 1 below)

I have never had a good experience with this function - it either "predicts" unrelated to what I am actually doing or (arguably worse) it looks right but then as I go into individual entries to tweak them it starts messing with entirely unrelated entries elsewhere in the column!



Figure 1. Screenshot of Activation

2020-03-10 07:56:17

Billy Thomas

Slick tip!

2020-02-04 02:58:31


Om my word... thank you so much, I have been sitting with this problem for WEEKS now and this is the first time I was able to fix it! I will be forever greatful for your 'Ctrl drag-down trick'. Have a great day!

2020-01-25 10:31:03


I need a VBA formula or short cut to solve the below -

In Name, When Apple comes twice it has to count like 1 & 2 if it repeats 4 times it has to come like 1, 2, 3, & 4

2019-12-10 19:46:25


Ah! The simplicity of turning the filter off! Thank you.

2019-02-01 04:49:25

Peeyush K

Again found an answer I couldn't find elsewhere clearly. I was trying to auto-fill, with filters on. Thanks! :-)

2018-08-10 11:18:05


Sorry, but Excel is NOT that smart.

2018-06-28 09:12:24


Someone asked below about apostrophes. First double-checking that isn't your issue.
Apostrophes can only be seen in the edit bar, not in the cell unless you double-click it. I reproduced your problem by entering '5/1/18 in a cell. Autofill acts as you said. Not only that, but when I formatted the cell to date, it did not fix the issue. I had to remove the apostrophe for it to work.
Further attempts to reproduce your issue. Not involving apostrophes. I formatted a cell as Text first. Then typed 5/1/18 in it. Also reproduced your issue. THEN formatted that cell as Date and still not fixed. Therefore, if you had empty cells that were FORMATTED as TEXT. You cannot fix your issue simply by reformatting them to Date. I had a heckuva time converting that cell to a real date or even copying that date to another cell, using Paste Special, to get the true date. What I finally did was copy the cell, when I went to paste in an empty cell, I had to use Paste Special (Ctrl+Alt+V) and choose Add from the dialog to get it to paste as a true date.
My guess is that your cells are starting out formatted as Text. Not sure why, but that makes life very difficult for dates and numbers afterwards.

2018-06-28 08:16:49

Willy Vanhaelen

Then I have no idea. Perhaps some setting in Windows???

2018-06-27 12:21:44


hi @Willy Vanhaelen,
The dates are formatted as dates and not text. Also, the filters are not on.
Still not working.... :-(

2018-06-26 05:55:18

Willy Vanhaelen

You probably entered your dates as text, either because the cells where formatted as text or by starting your entry with an apostrophe.

Make sure your cells are formatted for date, then it will work correctly.

2018-06-25 03:36:22


I have Excel 2007 and am trying to autofill dates but for some reason, instead of adding days, it add years, so the cells exceed by years only and not by days.
Anyone know why?
It seems I am not the only one with this problem since I have asked a 2 friends about it and they both have the same issue.
Can anyone help?

2018-05-17 11:02:07

Peter Atherton

Tom Stricker

To fill a sequence of number enter the first, say 1, and hold the CTRL key while dragging down.

To get the nth item in a series use the ROWS function.
e.g. =CHOOSE(ROWS($1:1),"Three","Blind","Mice") and drag down.

2018-05-16 03:26:20


Thank you, my filter was on and hence facing the problem.

2018-04-27 14:58:51

Rick Fehlberg

I found this thread with because of the same problem.
The solution to my problem was:

Change the value under the "Calculation options" section, then "Workbook Calculation" File>Options>Formulas to be set to Manual when I really desire Automatic.

No idea how this option got changed. First time in 20yrs this has happened to me.

Thanks for your help ! :-)

2018-04-11 09:28:24


I did the same manipulation as Steve Laux, worked perfectly now
" 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."

2018-03-21 11:05:50


Hi- I know this post is older but it took me hours to try and figure out what the problem was for me. Tried all the tips posted but for me I had to unfreeze my cells. Then it worked... Go figure..Hope this helps save someone else hours of frustration.

2017-12-07 08:49:30


If all else fails, start your sequence and hold down Ctrl as you drag down from the cell in the usual way. This worked for me.

2017-12-02 23:02:03


A1=2017, A2=2018, highlight both cells and autofill to A10.

2017-12-01 16:56:19


Is there a way to simply autofill a column with only the year value? Example: A1 = 2017 A10 = 2026 ? Thanks, D

2017-06-12 12:06:02


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


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


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


2017-01-18 12:12:56


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


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


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

2017-01-07 03:20:20


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


2016-12-16 13:35:13


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

2016-11-26 19:54:42


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


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


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:


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


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


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


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


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


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

2016-02-17 20:01:30


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


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

2015-12-20 16:37:16


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


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


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


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


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

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.