Note: The other languages of the website are Google-translated. Back to English

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
Comments (45)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks for the unmerge and duplicate vba code
This comment was minimized by the moderator on the site
Brilliant !! VBA code works perfectly. fixed my export to CSV problem :)
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Both the methods are working perfectly fine. Thank you :-)
This comment was minimized by the moderator on the site
OMG, you just saved me SO MUCH TIME.
This comment was minimized by the moderator on the site
this is awesome. life saver
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Your instructions are as simple as powerful!!! I owe you a dinner.
This comment was minimized by the moderator on the site
Sir you saved my life, you are the reason why i bookmarked this page :)!!
This comment was minimized by the moderator on the site
Extremely helpful! Thanks!
This comment was minimized by the moderator on the site
Very helpful..! Thank you very much.
This comment was minimized by the moderator on the site
You have made my week! 8)
This comment was minimized by the moderator on the site
Thank you so much this is a very helpful post. It really save me alot of time
This comment was minimized by the moderator on the site
Thanks a lot for your solution...
This comment was minimized by the moderator on the site
Awesome! Keep going with the good work!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Very nice explanation with screen shot.... thanks alot...
This comment was minimized by the moderator on the site
Saved my life! Thank you so much!
This comment was minimized by the moderator on the site
Good One. saved lot of time Thanks
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations