Set autofill as default when dragging a handle

simonfinnerty333

New Member
Joined
Oct 13, 2017
Messages
5
Hi there,

So whenever I've used Excel (2016) in the past, when dragging a handle or doubleclicking the autofill box, the autofill series has been the default option. i.e. dragging the value '1' in cell a1 down would autofill to 1,2,3,4,5 etc.

Now when I do it, it automatically copies, down as 1,1,1,1,1,.

Does anybody know how to change this?

Thanks in advance!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,094
Office Version
365
Platform
Windows
Welcome to the Board!

When you click and drag and pull/fill down, click on the little box that appears under the lower left corner, and change the option from "Copy Cells" to "Fill Series".
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
That should still work that way - are you sure you are dealing with real numbers, and not text numbers?
 

simonfinnerty333

New Member
Joined
Oct 13, 2017
Messages
5
Welcome to the Board!

When you click and drag and pull/fill down, click on the little box that appears under the lower left corner, and change the option from "Copy Cells" to "Fill Series".
Thanks, yes that option is available, but because I've gotten so used to the default of '1,2,3,4,5', the idea that it isn't the default is a quirk that annoys me. I wondered why it was default to copy,and if there was an option hidden away somewhere that I could change?

Thanks for your help!
 

simonfinnerty333

New Member
Joined
Oct 13, 2017
Messages
5
Yup, dealing with real numbers (I double checked the formatting). And the feature is certainly enabled within the options panel. :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,094
Office Version
365
Platform
Windows
Thanks, yes that option is available, but because I've gotten so used to the default of '1,2,3,4,5', the idea that it isn't the default is a quirk that annoys me. I wondered why it was default to copy,and if there was an option hidden away somewhere that I could change?
What I do is enter the first two values, then select both of them and double-click or drag-down. If Excel can see a pattern (increment or staying the same), it will continue that pattern.

So you could even in 2, 4 and then it will fill in 6, 8, 10, etc.
 
Last edited:

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
I've trained myself to drag and fill with the right mouse button. When the drag is finished, a context menu pops up. Usually I just select "Fill series".

The context menu gives good choices when you're dragging dates, too. For example, place 01/01/2017 (mm/dd/yyyy) in cell A1 and 01/15/2017 in A2. Select both cells and then do a drag and fill. A3 becomes 01/29/2017 and A4, 02/12/2017. Probably not what you want. Do the right-button drag and fill, select "Fill Months" and now A3 = 02/01/2017 and A4 = 02/15/2017. Aha!

Here's an odd variation. With A1 containing the integer 1 and B1 empty, select A1:B1 and and drag-fill downward. Column A will always fill as a series, column B will remain empty.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Yup, dealing with real numbers (I double checked the formatting). And the feature is certainly enabled within the options panel. :)
That still does not mean that the cells do contain real numbers - a text number formatted as general/percent/number/currency etc, is still a text number, not a real value, formatting wont change that. Do a quick test with =ISNUMBER(cell-ref) to be sure. FALSE indicates tewxt
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,039
Messages
5,508,932
Members
408,702
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top