VBA & Macro Problems

How to Merge and Center Duplicate Values in Excel

Rana Garcia Sep 30, 2026 869 views

Question details

The user needs a method to dynamically group or physically merge and center repeated sales-person codes across an Excel worksheet containing approximately 10,000 rows, preferably without using VBA.

How to Merge and Center Duplicate Values in a Large Excel Dataset

Product: Excel
Device & OS: not provided
Scenario: Formatting and organizing a large dataset to cleanly present consecutive duplicate values in a single column.
Observed behavior: While PivotTables can group values, physically merging and centering the raw worksheet cells automatically requires a VBA macro.

Before you start

Before attempting to group or merge cells, ensure your dataset is sorted by the 'Sales-person Code' column so that all duplicate values are grouped consecutively.

Solution 1Recommended

Group Data Using a PivotTable (No VBA Required)

The safest way to visually group duplicate sales codes without breaking data integrity is by using a PivotTable with merged labels.

Physical merging in large datasets often breaks sorting and filtering. A PivotTable groups the duplicates dynamically and allows you to center the labels for a clean presentation, bypassing the need for any programming.

Insert a PivotTable

Select your entire 10,000-row dataset, go to the 'Insert' tab on the ribbon, and click 'PivotTable'.

Configure Rows

Drag your 'Sales-person Code' field into the 'Rows' area of the PivotTable Field List to automatically group the duplicates.

Merge and Center Labels

Right-click anywhere inside the generated PivotTable, select 'PivotTable Options', check the box for 'Merge and center cells with labels', and click 'OK'.

Group Data Using a PivotTable (No VBA Required)

Data Integrity Preserved: This method achieves the desired visual layout without altering your raw data or requiring complex macro programming.

Solution 2

Use a VBA Macro to Physically Merge Cells

If your workflow strictly requires the raw cells to be physically merged and centered in the worksheet, a VBA macro is the only practical way to process 10,000 rows.

Efficient Data Management

Easily Merge Duplicate Values with WPS Spreadsheet

WPS Spreadsheet handles massive datasets effortlessly, offering fully featured PivotTables to seamlessly merge and center labels, as well as comprehensive VBA macro support for automating physical cell merges.

1. Open Data in WPS: Launch WPS Spreadsheet and open your large .xlsx dataset.
2. Insert a PivotTable: Navigate to the 'Insert' tab and click 'PivotTable' to group your data.
3. Enable Merged Labels: Right-click the generated PivotTable, go to 'Options', and enable 'Merge and center cells with labels'.
4. Run VBA Macros (Optional): If you require physical merging, switch to the 'Developer' tab to run your VBA automation seamlessly.

Seamlessly open and edit Microsoft Excel (.xlsx) formats.Built-in 'Merge and center cells with labels' PivotTable option.Full support for running advanced Excel VBA macros.Lightweight software optimized for analyzing datasets with 10,000+ rows.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Conditional Formatting to hide duplicate values instead of merging them?

Yes. You can apply a conditional formatting rule using a formula like '=A2=A1' and format the text color to exactly match the background color. This visually hides the duplicate text without structurally merging or altering the cells.

Why is the 'Merge & Center' button greyed out in my Excel worksheet?

If your data is formatted as an official Excel Table (via Insert > Table), the 'Merge & Center' feature is intentionally disabled to preserve data structures. You must convert the table back to a standard range (Right-click > Table > Convert to Range) before you can merge any cells.

Will physically merging cells affect my ability to sort and filter the data?

Yes. Physically merging cells in a raw dataset disrupts the grid structure, which prevents proper sorting, restricts column filtering, and frequently causes calculation errors in formulas. This is why using a PivotTable is the recommended alternative for grouping duplicate values.

Excel & Data Problems