How To Autofill Number Series In Merged Cells

As far as we know auto numbering will not work in merged cells. If you need to number a column that it is made up of different sized merged cells, when you try to drag the numbering it tries to resize the merge cells as well. Is there a way to number the merged cells without having to manually type the number?

For example, A1 is header, range A2:A15 is made up of different sized merged cells, see the following screen shot. How to fill number series in A2:A15?

Autofill Number Series In Merged Cells

We can use Excel formula to do it.

Step 1. Select the merged cells (in this example, select A2:A15).
Step 2. Press F2, or click the Formula Bar, enter the formula: =MAX(A$1:A1)+1.
Step 3. Press CTRL+ENTER.


    • Mohammed says

      It’s my fault, I pasted the formula on a column other than “A”.
      It works as described.

  1. Tandra says

    Thnk you so much!

    It works..first of all apply formula in all cell of column A then merge as need….it automatically take next number.

  2. Elamuruguselvan says

    hi bro….
    I tried it, It works,
    But i need addition , my merge cells are not even, May be 2 cells are merged, 3 cells are merged, in that case how can i proceed, Kindly support. Any help would be appreciated

Leave a Reply

Your email address will not be published. Required fields are marked *