How to Merge Adjacent Excel Cells with Identical Values
Question details
The user wants to merge adjacent cells in Excel that contain the exact same values to clean up schedules, timetables, or reports.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting schedules, timetables, or reports with repeated adjacent entries.
- Observed behavior
- Excel formulas cannot physically merge cells based on matching values, requiring alternative formatting or macro solutions.
Before running any VBA macros to merge cells, ensure you create a backup copy of your workbook. Merging cells physically can permanently remove or alter existing cell content, and macro actions cannot always be undone.
Use a VBA Macro to Physically Merge Cells
Since Excel formulas cannot merge cells, a VBA macro is the most effective way to automatically detect and merge adjacent cells with identical values.
Using a VBA script allows you to automate the process of checking adjacent rows or columns for duplicate values and merging them instantly. This is highly efficient for large datasets like timetables or rosters.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Click 'Insert' in the top menu, then select 'Module' to create a new blank module window.
Paste a VBA script designed to loop through your selected range and merge cells with matching values. Ensure the script includes 'Application.DisplayAlerts = False' to prevent Excel from showing a warning prompt for every single merge.
Close the VBA editor and return to your worksheet. Select the range of cells you want to process, press Alt + F8, select your newly created macro, and click 'Run'.
Use Conditional Formatting to Visually Group Identical Values
If you want to avoid data loss from physical merging, use conditional formatting to make adjacent identical cells look like a single block.
Manage and Format Data Effortlessly with WPS Office
WPS Spreadsheet offers powerful formatting tools, seamless VBA macro support, and intuitive cell merging features to help you organize schedules and reports without the hassle.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the data you need to merge.
- 2. Run a VBA Macro: Navigate to the 'Developer' tab, click 'Macros' or 'Visual Basic', and run your script to merge identical adjacent values.
- 3. Apply Conditional Formatting: Alternatively, select your cells, go to the 'Home' tab, and use 'Conditional Formatting' to hide duplicate values visually.
- 4. Use Manual Merge Tools: For smaller datasets, simply select the identical cells and click the 'Merge and Center' dropdown on the Home tab.
Frequently Asked Questions
Can I use an Excel formula to merge cells with the same value?
No, Excel formulas can only calculate and return values; they cannot change the physical structure or formatting of the worksheet, such as merging or unmerging cells. You must use VBA or manual merging tools.
Will I lose data when merging adjacent cells in Excel?
Yes. When you physically merge multiple cells in Excel, the system only retains the data from the upper-leftmost cell in the selection and deletes the data in the other merged cells.
How do I unmerge cells that were merged by a macro?
You can unmerge cells by selecting the merged block, going to the Home tab, and clicking the 'Merge & Center' button again to toggle it off. However, the data discarded during the original merge will not be restored.
Why is my VBA macro not running to merge the cells?
Your macro might be blocked by security settings. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings, and ensure macros are enabled. Also, ensure your file is saved as a Macro-Enabled Workbook (.xlsm).
