AutoFill Won't Work as Expected

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.

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

jmccall93
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

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.

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

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. :)

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!

Thanks!

Lee

Slick tip!

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!

I need a VBA formula or short cut to solve the below -
NAME LINE ID
APPLE 1
APPLE 2
ORANGE 1
GRAPES 1
GRAPES 2
GRAPES 3
GRAPES 4

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

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

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

Sorry, but Excel is NOT that smart.

@adva
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.

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

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

@adva
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.

Hi,
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?

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.

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

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 ! :-)

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

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.

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.

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

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

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

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.

Thank you

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)

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

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

Thanks!!!

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?

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

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

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

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

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.

I'm so glad this article mentions the filters!

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

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?

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

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?

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!!!

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!

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

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.

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

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

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

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

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.

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.

very helpful

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.

thank you very much good information

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