Google Sheets can remove duplicate rows, but it does not have a single command that intelligently merges duplicate records and combines their different cell values. Use Data > Data cleanup > Remove duplicates to delete repeated rows; to consolidate records, summarize or combine them with formulas after deciding which information to keep.
What “merge duplicates” can mean
The phrase can describe two different jobs. One is deleting rows that repeat another row. The other is consolidating records that share a key—such as a customer name or order number—while adding numbers or retaining details from multiple rows. Those jobs have different results, so decide which one you need before changing the sheet.
For example, suppose a table has two rows for Jordan Lee, one with a purchase of $12 and another with a purchase of $5. Removing duplicate rows will not add the purchases together: because the amounts differ, the rows are not identical across every column. A summary can instead produce one Jordan Lee row with a total of $17. If the two rows contain different notes, you may want to preserve both notes rather than discard either record.
There is also a separate Google Sheets feature called merging cells. It changes the layout by joining adjacent cells, often for a heading. It does not find repeated records or combine their data. For duplicate cleanup, use the data-cleanup tools or a deliberate consolidation method instead.
Choose the right method before editing
Start by identifying what makes two records duplicates. It might be an exact match across every column, or a match on a specific identifier such as an email address. A person’s name alone is often not a safe identifier: two different people can share a name, and one person’s name can be entered in several ways.
- Delete exact repeated rows: use Remove duplicates, checking all relevant columns.
- Keep one row per key: deduplicate based on the chosen identifier, but first decide which version of the other fields is trustworthy.
- Add numeric values: summarize by key with QUERY or SUMIF rather than deleting rows.
- Combine text details: collect matching notes, tags, or categories with TEXTJOIN and FILTER.
- Review possible matches: flag them first, especially when names or addresses may be inconsistent.
Keep an untouched copy of the source data while deciding. A formula-generated summary is usually safer to review than an immediate deletion because it leaves the original rows available for comparison.
Remove identical rows with the built-in tool
For a straightforward cleanup in Google Sheets in a web browser, select the data range and choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the range has a header row, then choose the columns Sheets should use to identify duplicates. Click Remove duplicates after checking that the selected range and columns match your intention.
- Make a copy of the sheet or duplicate the tab if the original records matter.
- Select the table, including its headers if it has them.
- Open Data > Data cleanup > Remove duplicates.
- Set the header-row option correctly and select the columns that define a duplicate.
- Review the removal message and inspect the remaining rows, especially near the top and bottom of the range.
If every column is selected, Sheets removes rows that repeat across the selected data. If only an ID column is selected, rows with the same ID are treated as duplicates even if their other fields differ. That may leave only one of those records, so this option is not a way to merge differing values. If the choice of which row survives matters, sort or review the data first and preserve a backup.
Consolidate repeated values with a summary
When the goal is to add numbers for each repeated key, a summary formula can produce one output row per key without deleting the source. Suppose column A contains customer names, column B contains purchase amounts, and row 1 contains headers. In an empty cell, with that layout, enter =QUERY(A1:B, "select A, sum(B) where A is not null group by A label sum(B) 'Total'", 1). For Jordan Lee with amounts 12 and 5, the result is Jordan Lee and 17; a Lee row with 8 remains 8.
QUERY groups matching values in column A and sums the numbers in column B. The final 1 tells Sheets that the input has one header row. Put the result somewhere that will not overlap the source table, and make sure the amount column contains numbers rather than text that only looks numeric. This method is useful for totals, but it does not preserve every original detail column automatically.
For a more transparent two-step setup, create a unique key list and calculate each total beside it. With names in A2:A and amounts in B2:B, enter =UNIQUE(FILTER(A2:A,A2:A<>"")) in D2 to list each nonblank name once. Then, with those names in D2 downward and the original names and amounts still in A2:A and B2:B, enter =SUMIF($A$2:$A,D2,$B$2:$B) in E2 and fill it down. Jordan’s two amounts, 12 and 5, return 17. The original rows remain unchanged.
Combine notes or categories instead of adding them
Some duplicates contain useful text that should be kept together. For instance, a customer may have the tags red and blue on two separate rows. TEXTJOIN can place matching details in one cell, while FILTER selects the rows belonging to the chosen key.
With names in A2:A, tags in B2:B, and a unique name in D2, enter =TEXTJOIN(", ",TRUE,FILTER($B$2:$B,$A$2:$A=D2,$B$2:$B<>"")) in E2. If Jordan appears with red and blue tags, the result is red, blue. Fill the formula down for other names. This creates a readable combined list; it does not decide whether repeated tags should be removed, so inspect the result if duplicates within the text matter.
For fields such as address, phone number, or status, avoid joining values automatically without a rule. You might want the newest address, the most complete address, or a human review when two values disagree. A formula cannot know which conflicting value is correct unless you encode a reliable rule, such as using a date column to identify the latest record.
Standardize data before looking for matches
Rows that refer to the same real-world item may not have identical text. Extra spaces, inconsistent capitalization, abbreviations, and spelling variations can prevent a simple duplicate check from treating entries as the same key. For example, “Jordan Lee” and “Jordan Lee ” may look alike while containing different characters.
Google Sheets includes a whitespace cleanup option at Data > Data cleanup > Trim whitespace. Apply it to a copy or to the relevant text columns, then check a sample of the results. It helps with stray spaces but cannot determine that “St.” and “Street” mean the same thing or that a misspelled name is intended to match another entry.
For a review list, you can create a normalized helper column. With names in A2:A, enter =LOWER(TRIM(A2)) in B2 and fill down. The result for “ Jordan Lee ” is “jordan lee.” Use that helper value to identify likely matches, but retain the original name for display and review. Normalizing capitalization and spaces can make comparison easier; it does not prove that two records belong to the same person.
Find duplicates without deleting them
If you want to inspect repeats before taking action, conditional formatting can highlight them. Select the cells in A2:A, open Format > Conditional formatting, choose a custom formula rule, and use =COUNTIF($A$2:$A,A2)>1. This rule highlights a name when it appears more than once in the checked range. Adjust the range and formula reference if your key is in another column or the data begins on a different row.
Highlighting is a review aid, not a merge operation. It may mark every occurrence, including the first one, and it cannot tell whether matching names refer to the same person. For stronger checks, compare a reliable identifier such as an account number, and examine records with conflicting dates, addresses, or other important fields before consolidating them.
Common mistakes and a safer workflow
The most damaging mistake is using Remove duplicates on a partial set of columns without understanding what will be discarded. If two rows share an email address but have different phone numbers, choosing only the email column may remove one version rather than combine the phone numbers. Conversely, selecting every column will not catch records that differ only in a spelling or a space.
Use a deliberate sequence:
- Preserve the original data in a copy or separate tab.
- Choose a stable key and decide how to handle blank keys.
- Standardize obvious formatting inconsistencies, keeping the original fields where needed.
- Generate a summary or highlight possible matches before deleting records.
- Check totals and sample records against the source, then make any final edits to a reviewed copy.
Pay special attention to totals: if you aggregate amounts, verify that the grand total in the summary matches the source total, unless you intentionally excluded blank or invalid keys. Also check how dates and numbers are stored; imported values can sometimes be text, which changes how a calculation behaves. A plausible-looking summary is not a substitute for checking it.
Google Sheets compared with Excel
Google Sheets offers a built-in Remove duplicates command and formula-based summaries. Microsoft Excel also has a Remove Duplicates feature, but its menus and dialog are different; instructions for Excel’s ribbon should not be assumed to match Google Sheets. Both products can remove rows based on selected columns, and in either one, removing a row is different from combining the contents of records that disagree.
In Sheets, formulas such as QUERY, UNIQUE, FILTER, SUMIF, and TEXTJOIN can create dynamic or reviewable outputs while leaving the source data intact. A formula result is not the same as a cleaned source table: if you need to replace the original data, review the output first and use a copy. The right method depends on whether you need deletion, a live summary, or a carefully reviewed master record.
Frequently Asked Questions
Does Google Sheets have a merge duplicates button?
It has a Remove duplicates tool, but that removes repeated rows; it does not combine different cell values into a single record. Use formulas or a manual review to consolidate information that differs between rows.
How do I remove duplicates in Google Sheets but keep one copy?
Select the data and choose Data > Data cleanup > Remove duplicates. Set the header option and columns carefully. Sheets keeps one occurrence based on the selected columns, so make a copy first if you may need the removed rows.
Can Google Sheets combine duplicate rows and add their amounts?
Yes. Group the records by a key and sum the amount column with QUERY or SUMIF. For example, a QUERY summary can turn two rows for one customer with amounts 12 and 5 into one total of 17, while leaving the source table intact.
Why does Remove duplicates miss rows that look the same?
The rows may differ in a selected cell because of spaces, spelling, capitalization, or a different value elsewhere in the selected columns. Trim whitespace, compare a helper key, and inspect likely matches rather than assuming that similar-looking text is identical.
Does merging cells remove duplicate data?
No. Format > Merge cells combines cells for layout purposes; it does not find repeated records. Use Data cleanup or a formula-based summary for duplicate data.
Related
- XFD Column Number in Excel: What 16,384 Means
- Windows 11 Excel Keyboard Shortcuts for Faster Work
- Windows 10 Excel Keyboard Shortcuts to Work Faster
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Combine Like Items in Excel: A Practical GuideTo combine like items in Excel, group the repeated item names and total their quantities with a PivotTable…
- How to Lock Cells in Google Sheets and Protect RangesTo lock cells in Google Sheets, select the cells, choose Data > Protect sheets and ranges, then set…
- Excel Data Validation Simple List: Create and Manage ItTo make a simple drop-down list in Excel for Microsoft 365 on Windows, select the cells, choose Data…
- Excel Rows Keep Hiding? How to Find and Unhide ThemExcel rows that seem to keep hiding are usually filtered, grouped, set to zero height, or hidden manually.…
- How to Make a Budget Tracker in Google SheetsMake a monthly budget tracker in Google Sheets by listing expense categories, entering planned and actual amounts, and…
- Add a Cover Page to a Word Document: Steps and TipsTo add a cover page in Microsoft Word for Microsoft 365 on Windows, open the document and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.