Skip to main content

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 comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>

33 comments
  1. YA
    yaseen

    useless

    • MO
      Mounir

      @yaseen Maybe you didn't know how to adapt the formula to your situation

  2. RU
    Ruby

    Great! Thanks

  3. SB
    SBY

    Formula should be =MAX($A$1:A1)+1

  4. AI
    ahmer iqbal

    I need a formula to merged the below series into 923312. can anybody help me.
    9233120
    9233121
    9233122
    9233123
    9233124
    9233125
    9233126
    9233127
    9233128
    9233129

    regards,
    Ahmer

    • MA
      Matt

      @ahmer iqbal Formula in first cell should be =MAX($A$1:A1)+9233120
      Formula in all subsequent rows should be =MAX($A$1:A1)+1

  5. NA
    naveen

    this formula is only for merged cell what about if merged and single cell is there?

    • HA
      Hamza

      @naveen It Works on both merged and single cell

      • GI
        Girish

        @Hamza Is there any formula to sum and merge cells.
        M F TG Total Merged M Merged F Merged TG Merged Total
        816 759 0 1575
        773 707 0 1480
        798 680 0 1478
        655 616 0 1271
        725 638 0 1363

  6. AS
    ABDUL SALAM

    Sir, BIG RELIEF. I have just found the right solution for my problem at the RIGHT TIME. Thank you so much.

  7. SA
    sanjay

    it works thanks dude for saving my time

  8. MI
    Milu

    It works as described...superb

  9. TA
    TAPAS

    IT IS WORKING, THANKS

  10. AJ
    ANKURKUMAR JASANI

    I want to weekend in this merged cell series... how can i do that ?

  11. SR
    Samir Roy

    Hi,

    It really helped, but I have one concern, how to start from row A1, here the example shows results form row A2. In the formula for cell A2, we include A1 as reference to find the maximum no. but what for cell A1, what should be the reference formula for cell A1??

  12. MV
    Manish Vaishnav

    Wow...Thank you so much, it saved my time , earlier i was manually entering the series nos.

  13. DH
    dhroobh

    Loved it, Thanks, It saves like 2 hours of hard work.
    Though there was error for me, my total value supposed to come 2275, but it came 2285
    There is "10" difference. I fixed the error manually.

  14. MT
    Ms Tam

    Simple, clear... Many thanks!

  15. BH
    BIKRAM HALDAR

    I WANT MERGE CELL CONTINUE ...
    01/01/2019,
    03/01/2019
    05/01/2019
    06/01/2019

  16. AR
    arejune

    it works and thank you very much for sharing.

  17. JW
    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?

  18. LN
    Lakshmi Narayanan C D

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

  19. NA
    Naveed Abbas

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

  20. BO
    Bonnie

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

  21. AM
    Amit

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

  22. MU
    Munir

    Thanx man its working..

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

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

  25. BA
    BAL

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

    Any help would be appreciated.

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

  26. MO
    Mohammed

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

    • MO
      Mohammed

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