Excel does not have a command that safely merges duplicate cell contents into one cell: Home > Merge & Center merges cell areas and keeps only the top-left cell’s content. To handle repeated values, use Remove Duplicates to delete repeated records, Conditional Formatting to find them, or a formula or Power Query workflow to combine information while preserving what you need.
What people mean by merging duplicate cells
The phrase can describe several different tasks, and choosing the wrong one can erase information. A sheet might contain repeated names or IDs that you want to identify, repeated rows you want to remove, or multiple notes associated with the same ID that you want to bring together. Those are data-cleanup tasks, not cell-merging tasks.
Excel’s merge command changes the shape of the worksheet. It joins neighboring cells into one larger cell, usually for layout purposes, such as centering a heading over several columns. It does not search for matching values, combine text from separate records, or decide which duplicate record is safe to discard.
- Repeated values: Find or highlight cells that contain the same value.
- Repeated records: Remove rows that match in selected columns.
- Related information: Combine text or summarize figures for records sharing a key.
- Visual layout: Merge adjacent cells only when a larger display area is genuinely needed.
What Merge & Center actually does
In Excel for Microsoft 365 on Windows, select adjacent cells and choose Home > Alignment > Merge & Center. The drop-down offers related options, including Merge Cells, Merge Across, and Unmerge Cells. Merge & Center joins the selected area and centers its surviving content; Merge Cells joins the area without centering it. Merge Across makes separate merged areas for each row in a multirow selection. None of these options checks whether the cells contain duplicates.
If more than one selected cell contains data, Excel keeps the content in the upper-left cell of the selected area and discards the contents of the other cells in that area. For example, merging A1:C1 when A1 contains Region, B1 contains East, and C1 contains West leaves Region in the merged cell; East and West are lost. Excel warns that merging cells keeps only the upper-left value, but it is still easy to overlook the warning.
Before merging, copy any content you need elsewhere. If you have already merged cells and need to recover discarded values, undo immediately with Ctrl+Z on Windows (Command+Z on Mac), if the undo history is still available. Unmerging restores the original cell layout, but it cannot bring back content that was discarded during the merge.
How to identify duplicates without changing data
For a quick visual check, select the range to inspect and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a formatting style in the dialog and confirm it. Excel highlights repeated values in the selected range; this is a display rule, so it does not delete or combine cells. Review the highlighted entries before deciding what action to take.
Another option is a helper column. With the values to check in A2:A7, enter this in B2 and fill it down through B7: =COUNTIF($A$2:$A$7,A2)>1. If the range contains Apple, pear, Apple, plum, pear, and apple, the formula returns TRUE for each Apple or pear entry and FALSE for plum. COUNTIF is case-insensitive, so Apple and apple count as the same value. The result flags every occurrence of a repeated value, including the first one; it does not tell you which row to keep.
Make the range match your data and keep the references appropriate when filling the formula down. For a changing list, you can turn the data into an Excel table using Insert > Table, then use the table column in a formula. A helper-column check is useful when you want to filter, review, or document repeated entries before making an irreversible change.
How to remove duplicate rows safely
Use Remove Duplicates when you have decided that matching records should be deleted, not merely marked. First save a copy of the workbook or duplicate the source sheet. Then click inside the data and choose Data > Data Tools > Remove Duplicates. In the dialog, confirm whether the data has headers and select the columns Excel should compare. Excel removes later rows that match an earlier row according to those selected columns.
The comparison columns define what counts as a duplicate. If a customer list has the same customer ID twice but different phone numbers, selecting only the ID treats one row as a duplicate and removes one record, including the phone number in that row. Selecting both the ID and phone-number columns removes a row only when both values match. Select all columns when you mean to remove only fully repeated records.
- Keep an unchanged backup or source sheet.
- Decide whether the whole row must match or only particular key columns.
- Check the headers and selected columns in the Remove Duplicates dialog.
- Review Excel’s removal summary and inspect the remaining records.
Remove Duplicates edits the selected data; it does not create a separate clean copy. Ctrl+Z on Windows or Command+Z on Mac can undo the action while it remains in the undo history, but a backup is safer if you need a reliable original. If the data is formatted as an Excel table, the command can be used on its data; merging cells inside a table is not supported. A table can be converted to a range through Table Design > Convert to Range, but that changes the table structure and is not a way to merge duplicate values.
How to combine information from repeated records
If repeated IDs have different notes or categories, deleting duplicate rows may throw away useful details. Instead, create a summary that groups records by the shared key and combines or summarizes the related information. For a small, fixed set of cells, TEXTJOIN can put their contents into one result. With the text North, East, North, and South in A2:A5, enter =TEXTJOIN(", ",TRUE,A2:A5) in another cell. It returns North, East, North, South; it joins the values but does not remove repeated words.
In Microsoft 365, a dynamic-array formula can filter records for one key and return distinct related labels. With IDs in A2:A7, labels in B2:B7, and the key to summarize in D2, enter =TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$7,$A$2:$A$7=D2,""))) in another cell. For rows with ID 10 and labels North, East, North, this returns North, East. FILTER selects matching labels and UNIQUE keeps one of each; TEXTJOIN puts them in one cell. FILTER and UNIQUE require Microsoft 365 or Excel 2021 or later. This formula creates a summary result and leaves the source rows intact.
For larger or regularly refreshed lists, Power Query is often easier to maintain than hand-built formulas. Select the data and use Data > From Table/Range to open Power Query Editor. Group records by the key column with Home > Group By, then choose a suitable aggregation, such as count or sum for numeric fields. Text aggregation may require additional transformation steps, so check the resulting preview before loading the output back to Excel. Power Query produces a transformed result; it does not silently repair the original source data.
Choose the right method for the job
The safest method depends on whether you want to inspect, delete, combine, summarize, or format. A useful rule is to keep the original data unchanged until you have confirmed what each repeated value represents.
- To spot repeats: Use Conditional Formatting or a COUNTIF helper column.
- To delete identical records: Use Remove Duplicates with all relevant columns selected.
- To keep one row per ID: Use Remove Duplicates on the ID only after deciding which other row details can be discarded.
- To combine repeated notes: Build a formula or Power Query summary rather than merging worksheet cells.
- To total repeated sales or quantities: Use a PivotTable or Power Query aggregation, keeping the source records available for checking.
- To make a heading span columns: Use Merge & Center only when the area is for layout and other cell contents are not needed.
A PivotTable is especially helpful when the goal is to summarize amounts by customer, product, or category. It groups matching labels in its report and can sum or count numeric fields without deleting the underlying rows. It does not combine text notes into a single descriptive sentence by default, so use a text-oriented approach for that task.
Common problems with merged cells
Merged cells can make ordinary worksheet tasks awkward. Sorting and filtering may behave poorly when merged areas do not line up with the data, and copying or pasting ranges with different merge patterns can produce errors or unexpected layouts. A merged area also has only one addressable value—the upper-left cell—so formulas and navigation may not behave as expected if you treat every visible part as a separate cell.
If the aim is simply to center a heading across columns without joining cells, use Center Across Selection instead. Select the cells, open the Format Cells dialog from the Home tab’s Alignment dialog launcher, choose the Alignment tab, and set Horizontal to Center Across Selection. This keeps the cells separate and avoids the data-loss behavior of merging, while centering the text across the selected range.
Another common misunderstanding is that duplicate highlighting identifies a row that is safe to delete. A repeated name might belong to two different people, or a repeated product ID might describe separate transactions. Check a stable key and the business meaning of the data before removing anything. In Excel for Mac, the core duplicate and merge features are similar, though ribbon placement and dialog appearance can vary. Excel for the web also supports common duplicate-removal and formatting tasks, but advanced Power Query workflows are best handled in the desktop app.
Frequently Asked Questions
Can Excel merge cells that contain the same value?
It can merge the selected cell area, but it does not first verify that the values match. The merge keeps the upper-left cell’s content and discards other selected contents, even if they are duplicates.
How do I merge duplicate rows but keep all the information?
Do not use Merge & Center. Group the rows by a key and create a formula or Power Query summary for the fields you want to retain. Keep a copy of the original rows so you can check the combined result.
Does Remove Duplicates keep the first row?
It keeps the first occurrence it encounters in the selected range and removes later rows matching the chosen columns. Sort or arrange the data first if a particular record should be kept, and verify the selected columns.
Can I undo a cell merge?
Choose Home > Merge & Center > Unmerge Cells, or use the related unmerge option in the ribbon. Unmerging restores the cells’ layout, but it cannot restore contents lost when the cells were originally merged; use Undo if that action is still available.
Why can’t I merge cells in an Excel table?
Excel tables do not support merged cells within the table. If the goal is to make a heading span columns, place it outside the table or use Center Across Selection. Converting a table to a range is possible, but it removes table features and does not combine duplicate records.
Related
- Excel Import CSV: Does the First Row Become Headers?
- Excel Import CSV from Clipboard? Methods That Work
- Excel Import CSV: Open Comma-Separated Files Correctly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- DDE Error in Excel: What It Means and How to Fix ItA DDE error in Excel usually means Windows or another program cannot pass a request to Excel through…
- How to Fix Mail Merge in Word: Fields, Data, and PrintingTo fix mail merge in Word, check that your recipient list has clear column headers and valid records,…
- Fix Conditional Formatting in Excel: A Practical GuideTo fix conditional formatting in Excel, open Home > Conditional Formatting > Manage Rules, show the rules for…
- TRIM and CLEAN Not Working in Excel? Fix the Hidden SpacesTRIM and CLEAN can seem broken because they do not remove every character that looks like a space.…
- VLOOKUP Circular Reference Error: Causes and FixesA VLOOKUP circular reference error means the formula depends, directly or indirectly, on its own result. VLOOKUP does…
- Excel Zero Error: Why #DIV/0! Appears and How to Fix ItIn Excel, a “zero error” usually means the #DIV/0! error: a formula is dividing a number by zero…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.