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.

Leave a Reply

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

20 comments
  1. Mohammed
    Mohammed

    =MAX(A$1:A1)+1 equals =ROW()-1, and not working as described in the screenshot!

    • Mohammed
      Mohammed

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

  2. BAL
    BAL

    I tried this formula and I got the columns numbered
    1
    1
    2
    2

    Any help would be appreciated.

    • Rahul
      Rahul

      @BAL You have to type the formula =MAX(A$1:A1)+1 if you are applying it from row2. If you are starting from row3 then formula will change to =MAX(A$1:A2)+1 and onwards accordingly.

  3. Tandra
    Tandra

    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.

  4. Elamuruguselvan
    Elamuruguselvan

    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

  5. Munir
    Munir

    Thanx man its working..

  6. Amit
    Amit

    Wow.. so easy.. Thank you so much..

  7. Bonnie
    Bonnie

    This is not working for me. It numbers as
    1
    1
    2
    2
    2
    3
    3
    Can you Help?

  8. Naveed Abbas
    Naveed Abbas

    I have to manage a result sheet then result card,,, so how can I get proper way kindly help any one

  9. Lakshmi Narayanan C D
    Lakshmi Narayanan C D

    How can i concatenate string along with the number sequence in same above format.
    Example:
    TC01
    TC02
    TC03

  10. juanita walker
    juanita walker

    every thing you did I was able to do on my own excel sheet that I opened to follow along on my second monitor I found it easier to follow when you do hands on verses just sitting and watching, thanks for showing me how to put the days /months in now I need to be able to do the 9*9= in excel I did not understand how you got the answer when you clicked I blank and miss it . this was my first class and I want to learn more on how to use excel?