How to unmerge cells and fill with duplicate values in Excel?

If you have a worksheet which contains numerous merged cells, and now you need to unmerge them and auto-fill down the original values from the merged cells as following screenshots shown. How could you quickly deal with this task?

Unmerge cells and fill with duplicate data with Go To Special command

Unmerge cells and fill with duplicate data with VBA code

Unmerge cells and fill with duplicate data with one click


arrow blue right bubble Unmerge cells and fill with duplicate data with Go To Special command

With the Go To Special command, you can unmerge cells and fill down the values. But this method is somewhat troublesome, please do as following steps:

1. Select the columns that have merged cells.

2. Click Home > Merge & Center > Unmerge Cells. See screenshot:

3. And the merged cells have been unmerged and only the first cell will be filled with the original values. And then select the range again.

4. Then click Home > Find & Select > Go To Special. In the Go To Special dialog box, select Blanks option. See screenshot:

5. And then click OK, all of the blank cells in the range have been selected. Then input = and press Up arrow key on the keyboard. See screenshot:

6. Then press Ctrl + Enter keys, all of the blank cells have been filled with the original merged data. See screenshot:


arrow blue right bubble Unmerge cells and fill with duplicate data with VBA code

With the following VBA code, you can quickly unmerge the cells and fill down the values.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Modulewindow.

Sub UnMergeSameCell()
'Upadateby Extendoffice
Dim Rng As Range, xCell As Range
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Rng In WorkRng
    If Rng.MergeCells Then
        With Rng.MergeArea
            .UnMerge
            .Formula = Rng.Formula
        End With
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

3. Then press the F5 key to run this code, a dialog is displayed for selecting a range to work with, see screenshot:

4. Click OK, then the merged cells have been unmerged and auto-filled down the original merged values.


arrow blue right bubble Unmerge cells and fill with duplicate data with one click

With the Unmerge Cell utility of Kutools for Excel, you can unmerge cells and fill with duplicate data with one click, this method is very simple and handy.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days

After installing Kutools for Excel, you can do as follows:

1. Select the columns that you want to unmerge and fill down the data.

2. Click Kutools > Merge & Split > Unmerge Cells & Fill Value, see screenshot:

3. And the merged cells have been unmerged and filled with the duplicate values at once. See screenshots:


arrow blue right bubble Unmerge cells and fill with duplicate data with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related article:

Merge adjacent rows with same data in Excel


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Random · 3 days ago
    Thanks... loved it
  • To post as a guest, your comment is unpublished.
    Mark · 2 months ago
    The VBA code stalls when an entire columns are selected. There should be a mechanism defending against fruitless running through large loops. Apart from that it works fine.

  • To post as a guest, your comment is unpublished.
    Sandesh Dhawale · 3 months ago
    thanks very much Saved me lot of time
  • To post as a guest, your comment is unpublished.
    excel user 22 · 2 years ago
    Thx!!! It saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    Oyunsuren · 2 years ago
    Great. Thank you very very much.
  • To post as a guest, your comment is unpublished.
    Joel · 2 years ago
    that's brilliant, thanks a bunch, massive sheet with loads of inconsistently merged cells fixed in seconds.
  • To post as a guest, your comment is unpublished.
    Barbs · 3 years ago
    Thanks a million, saved me loads of time using that Go to Special function!
  • To post as a guest, your comment is unpublished.
    Nabil · 3 years ago
    Thanks a lot. Used the Go To Special solution and it worked perfectly.
  • To post as a guest, your comment is unpublished.
    Tari-Chan · 3 years ago
    wow thank you. you save my life.
  • To post as a guest, your comment is unpublished.
    Tari-Chan · 3 years ago
    Thank you sir,I used kutools before, but i don't know why it just doesn't work now since i change to win10pro & office2016, but 'go to special' way is so helpful. thank you again.
  • To post as a guest, your comment is unpublished.
    Shimpundu · 3 years ago
    Awsome! This save a lot of time.
  • To post as a guest, your comment is unpublished.
    Pedro · 3 years ago
    Fantastic! VBA code worked! thank you so much! :)
  • To post as a guest, your comment is unpublished.
    Prabakar · 3 years ago
    Thank you very much; Love your solution :-)
  • To post as a guest, your comment is unpublished.
    Giang · 3 years ago
    "Unmerge cells and fill with duplicate data with VBA code" - It's really fast and useful. Thankyou very much! ^_^
  • To post as a guest, your comment is unpublished.
    Thomas · 3 years ago
    I personally use another tool that seems to work in a similar way. It's called Power-user and you can also unmerge and fill down cells. The (little) difference is that you can track each merged cell separately. www.powerusersoftwares.com
  • To post as a guest, your comment is unpublished.
    Korozja · 4 years ago
    Find & Select / ctrl + enter - THANK YOU
  • To post as a guest, your comment is unpublished.
    kobio · 4 years ago
    Thanks - it worked first time!
  • To post as a guest, your comment is unpublished.
    Doaa · 4 years ago
    Awesome. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Agus · 4 years ago
    Thank you for your tips.. It works well !!
  • To post as a guest, your comment is unpublished.
    Hakim · 4 years ago
    Excellent solution ! Thanks !
  • To post as a guest, your comment is unpublished.
    Neel · 4 years ago
    Good One. saved lot of time

    Thanks
  • To post as a guest, your comment is unpublished.
    Rhonda · 4 years ago
    Saved my life! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Anil · 5 years ago
    Very nice explanation with screen shot.... thanks alot...
  • To post as a guest, your comment is unpublished.
    Tom · 5 years ago
    This is really helpful but I have one question. Is there a way to modify the VBA code to skip blank merged cells? I only want the cell to be unmerged and duplicated if there is a value other than blank. I'm sure it wouldn't take much more than an if/else statement or two, I'm just too dumb to do it.
  • To post as a guest, your comment is unpublished.
    Luan · 5 years ago
    Awesome! Keep going with the good work!
  • To post as a guest, your comment is unpublished.
    Amir · 5 years ago
    Thanks a lot for your solution...
  • To post as a guest, your comment is unpublished.
    john · 5 years ago
    Thank you so much this is a very helpful post. It really save me alot of time
  • To post as a guest, your comment is unpublished.
    arlicay · 6 years ago
    You have made my week! 8)
  • To post as a guest, your comment is unpublished.
    Deva · 6 years ago
    Very helpful..!
    Thank you very much.
  • To post as a guest, your comment is unpublished.
    MarkFrank · 6 years ago
    Extremely helpful! Thanks!
  • To post as a guest, your comment is unpublished.
    Lu · 6 years ago
    Sir you saved my life, you are the reason why i bookmarked this page :)!!
  • To post as a guest, your comment is unpublished.
    Alberto · 6 years ago
    Your instructions are as simple as powerful!!!
    I owe you a dinner.
  • To post as a guest, your comment is unpublished.
    tmar · 6 years ago
    Nice job. But Function method worked on first good on first 2288 lines, then without error or notice, just did not do lines past that. I have over 6000 lines (rows). If I manually select any range beyond the 2288 line, the Function method does not work. Unfortunately, data vendor merges lines in the Excel data delivery.

    Any suggestions?
    Thanks for the great info.
  • To post as a guest, your comment is unpublished.
    Andre · 6 years ago
    This saved me countless hours, thank you so much for this. I did notice one thing I came across with the Go To Special Command that confused me for a second. I needed to search within the document, and the ctrl+enter function copies the code, but not the value. I recommend (if you want to search like i did) to copy the row that was merged, paste special, values. This will get rid of the code and make searchable text. Thank you again for this amazing trick.
  • To post as a guest, your comment is unpublished.
    mo · 6 years ago
    this is awesome. life saver
  • To post as a guest, your comment is unpublished.
    Kim · 6 years ago
    OMG, you just saved me SO MUCH TIME.
  • To post as a guest, your comment is unpublished.
    Mak · 6 years ago
    Both the methods are working perfectly fine. Thank you :-)
  • To post as a guest, your comment is unpublished.
    Kyle Baker · 6 years ago
    The VBA worked great except I used it for a 16 digit number and excel turns the last digit to a "0". Any suggestions on a work around for that?
  • To post as a guest, your comment is unpublished.
    Arty · 6 years ago
    Brilliant !! VBA code works perfectly.

    fixed my export to CSV problem :)
  • To post as a guest, your comment is unpublished.
    didier · 6 years ago
    Thanks for the unmerge and duplicate vba code