Excel autofill not working as expected

autocompletemicrosoft excelmicrosoft-excel-2013

I am using MS Excel 2013. I have two columns – Year and Quarter like this:

2009    1
2009    2
2009    3
2009    4
2010    1
2010    2
2010    3
2010    4
2011    1
2011    2
2011    3
2011    4
2012    1
2012    2
2012    3
2012    4
2013    1

I would expect that if I select the two columns and drag the selection down I would have 2013 2, 2013 3 and so fort. Instead I get:

1) If no formatting is applied to the cells:

2012.852941 2.588235294
2013.098039 2.607843137
2013.343137 2.62745098
2013.588235 2.647058824
2013.833333 2.666666667
2014.078431 2.68627451
2014.323529 2.705882353

2) If both columns are formatted as numbers:

2013    3
2013    3
2013    3
2014    3
2014    3
2014    3

In the second scenario at least the Year column is populated as expected, but not the Quarter column. What should I do to get autofill to work as expected?

Best Answer

The other answer clearly explains why. My solution is a bit simpler though.

  1. Fill the first four cells with 2010 manually
  2. then make the next Cell reference the first 2010 Cell ( A5: =A1+1 )
  3. You can then drag this down as many as you need.

You can also get column two in a similar way. put in your first 1-2-3-and 4 then instead of setting

  • B5: =B1+1 Set B5: =B1

Dragging it down results in

  • B6:=B2
  • B7:=B3
  • and so on.

Regular Screenshot Screenshot with 'Show Formulas' Option Enabled

Related Question