Google Sheets Merge Duplicates: What Actually Works

How-to basics · Updated November 21, 2026

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.

  1. Make a copy of the sheet or duplicate the tab if the original records matter.
  2. Select the table, including its headers if it has them.
  3. Open Data > Data cleanup > Remove duplicates.
  4. Set the header-row option correctly and select the columns that define a duplicate.
  5. 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.