How to copy and paste merged cells to single cells in Excel?
Normally, when you copy merged cells and paste them elsewhere in Excel, those cells remain merged in the new location by default. However, in many cases—such as when you need to analyze or manipulate the data—the merged cell format can cause problems. What’s often needed is to paste the values of those merged cells into single cells instead, as shown in the following example screenshot, so each original value occupies an individual cell. This allows for easier data processing, sorting, and further calculation. In this article, we explain several practical methods to copy and paste merged cells as unmerged, single cells in Excel, along with their applicable scenarios, pros, and cons.
Copy and paste merged cells into single cells with Paste option
Copy and paste multiple merged cells into single cells with Kutools for Excel
VBA Macro: Unmerge cells and fill single cells with merged values
Copy and paste merged cells into single cells with Paste option
If your worksheet includes only a few merged areas that need to be unmerged after copying, the Paste option in Excel can quickly help distribute merged cell values into separate cells. This method is suitable for small worksheets or when handling less frequent cases. Please follow these steps:
1. Select the merged cells you need and press Ctrl + C to copy them.
2. Move to your destination range, then press Ctrl + V to paste the merged cells. At the bottom right of the pasted area, click the small Paste Options icon, and from the list, choose the third icon labeled Formulas & Number Formatting. This special paste option tells Excel to paste just the values and formatting, automatically unmerging the cells in the process. See the illustration below:
3. The result displays the original values distributed to single, unmerged cells, ready for your next steps in data management or calculation.
Note: If the merged range covers multiple columns or rows and only the first (top-left) cell is filled, this method may leave some resulting cells blank. It is mainly recommended when your merged cells each contain only one value.
If you encounter unwanted formatting or data loss, double-check the source and destination ranges’ merge status and adjust as necessary.
This approach is quick and built into Excel but less efficient for processing large ranges or multiple groups of merged cells.
Copy and paste multiple merged cells into single cells with Kutools for Excel
Excel’s native functions do not allow you to copy and paste multiple non-contiguous merged ranges into single cells in one step. However, Kutools for Excel’s Copy Ranges feature makes it much easier to process multiple merged areas. This is ideal for larger data sets or when you must work with several groups of merged cells at once.
After downloading and installing Kutools for Excel, please do as follows:
1. Hold the Ctrl key and select all the merged cells or ranges you want to copy.
2. Go to the Kutools tab in your Ribbon, expand Range, and choose Copy Ranges.
3. In the Copy Multiple Ranges dialog, set the Paste special option to Formulas and number formats. You can also select Including row height and/or Including column width if you wish to maintain the dimensions of the source cells. Refer to the screenshot:
4. Click OK and a prompt appears, allowing you to specify the starting cell for the pasted results. Make sure you select a cell with sufficient free space below or to the right, as the paste will expand accordingly (as shown in the screenshot below):
5. Finally, click OK to confirm, and the originally merged cells will be pasted as separate, individual cells. This way, your data is normalized and ready for further processing without unwanted merge conflicts.
Tip: This approach is highly efficient for batch processing and helps maintain your workflow when handling complex worksheets with numerous or scattered merged cells. If you need to repeat this often, Kutools' additional range and merge tools may further streamline related tasks.
Download and free trial Kutools for Excel Now !
VBA Macro: Unmerge cells and fill single cells with merged values
For users who need to process large numbers of merged areas at once, a VBA macro can save significant manual effort. This VBA code automatically unmerges the selected cells and fills each resulting single cell with the original merged cell’s value. This approach is efficient for complex or repetitive data transformation tasks, and is particularly useful if the built-in options and formulas are not sufficient.
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications (VBA) window. Next, click Insert > Module to add a new code module, and then copy and paste the following code:
Sub UnmergeAndFill()
Dim cell As Range
Dim area As Range
Dim rng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select merged range", xTitleId, rng.Address, Type:=8)
For Each area In rng
If area.MergeCells Then
With area.MergeArea
.UnMerge
.Value = area.Value
End With
End If
Next area
End Sub2. Click the 
Run button (or press F5) to execute the code. A dialog will appear asking you to select the merged cell range you want to process. After confirming your selection, the macro will unmerge all the chosen cells and fill each individual cell with the value from the merged group.
Precautions: Before running the macro, it’s recommended to back up your data. This code overwrites existing values in the destination cells, so avoid selecting areas with important data you want to keep unchanged. This method is highly effective for unmerging and distributing values in large tables or when performing conversions during data import and cleanup.
Troubleshooting: Macros may also be disabled in your Excel settings; adjust Macro Security as needed.
For each of these solutions, choose the method that best fits your worksheet size, data layout, and automation needs:
- Paste Option is best for simple, occasional tasks.
- Kutools for Excel makes batch processing fast and easy, especially suitable for complex worksheets.
- VBA is recommended for repeated or large-scale unmerge and fill scenarios.
If your merged data extends over multiple rows or columns, double-check your results to ensure all intended cells have been filled accurately. Always save your file before making batch changes.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in