To highlight duplicates in Google Sheets, select your data cells, open Format and Conditional formatting, and use a COUNTIF custom formula that checks how often each value appears. To remove duplicates, select the full table, open Data, Data cleanup and Remove duplicates, then choose the columns that define a duplicate record.
Review the repeated rows before deleting anything. Two students can have the same name, and one person can have several valid records. If you want a separate list that updates while the source stays intact, use UNIQUE. Choose the comparison columns or range based on what makes a record the same in your task.
- Use conditional formatting to see repeated values without deleting rows.
- Use an exact record key such as an approved ID when names can repeat.
- Keep all columns of related records together when removing duplicates.
- Use UNIQUE when you need a separate distinct list rather than a permanent cleanup.
- Verify the row count and a few important records after the change.
Decide what a duplicate means in your sheet
Start with the job the table is meant to do. A contact list might have one row per contact, a borrowing log might have one row per loan, and an attendance register might have one row per person per date. A repeated name can mean different things in each sheet. Write down the column or set of columns that identifies one record before you highlight or remove anything.
Repeated values and duplicate records are different checks
Suppose a school activity list has columns for Student ID, Name, Session and Attendance. Two rows can have the same name and different IDs. They should remain distinct. One student may also attend two sessions. If the table is one row per student per session, compare the ID and session together. Removing rows based on the name alone can discard valid data.
A full-row duplicate can be safe to inspect first
If two rows contain the same values in all the fields that matter, they are candidates for cleanup. Check whether they came from a repeated import or whether the table intentionally records repeated events. A transaction log can have identical-looking amounts and dates while still referring to separate events. Use the appropriate reference or source information to distinguish them.
Decide what to keep when two rows disagree
A repeated ID can come with different contact details or statuses. A duplicate-removal command cannot decide which value is correct. Compare the records and use the approved rule, such as the verified latest update. If the answer is unclear, flag the rows for review and preserve both until the responsible person resolves the difference.
Back to contents · Next section
Highlight repeated values using conditional formatting
For a list of IDs in A2:A100, select A2:A100. Open Format and Conditional formatting. Choose the custom formula option and enter a rule based on the first cell of the applied range. This example ignores empty cells and highlights every nonblank ID that appears more than once.
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)Choose a format and save the rule. Open two rows with the same ID and confirm that both are highlighted. Check a unique ID and an empty row. The comparison range stays fixed because of the dollar signs, while A2 moves with each row to test the current value.
Highlight the full row instead of only one cell
Apply the rule to A2:D100 and use a fixed column reference for the current ID. The dollar sign before A keeps each column of the highlighted row checking the ID in column A.
=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1)This makes it easier to review the related name, date and status without moving data. Check a row near the end of the range. If the highlighting appears one row off, compare the formula’s row number with the first row of the applied range.
Flag only later occurrences when that is what you need
You can count occurrences from the first data row through the current row. This marks the second and later appearances and leaves the first unmarked. Use it for review when the order matters, and verify whether the first occurrence is the one you want to keep.
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)If you later sort the table, the first occurrence can change. Preserve the source order or use an approved timestamp or reference if retention depends on a specific record. A highlight is a review cue rather than a decision about which row is valid.
Back to contents · Next section
Check duplicate records using more than one column
When the key uses two columns, compare both. Suppose column A is an ID and column C is a session. For a table where each student should appear once per session, apply a conditional-formatting rule to the full row range and use COUNTIFS.
=AND($A2<>"",$C2<>"",COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,$C2)>1)The formula highlights a row only when the ID and session combination occurs more than once. It leaves out rows with an incomplete key. Compare a repeated ID in two different sessions with a repeated ID in the same session. Only the second situation should be flagged under this rule.
Check dates and other fields that can look alike
A date can be stored as a date value or as text. A cell might display only the date while its value includes a time. If the record key is one ID per day, decide whether date alone or date and time matters. Review and normalise the relevant values in a working column or copy where appropriate. Verify the results against the source data.
Avoid losing the rest of a record
Keep the name, session, status and other related fields with their row during the review. Sorting one column by itself can detach an ID from the corresponding data. When you need to sort for inspection, select or use the full table with the correct header setting. Check a known row after the sort.
Treat blank keys as their own problem
A missing ID can make two records look alike in a key-based cleanup. Check incomplete rows separately. If a row needs an ID from an approved source, fill it in through the agreed process. If the key is genuinely unavailable, keep it out of an automatic removal until you know how to handle it.
Back to contents · Next section
Remove duplicates from a working table
Select the complete rectangular table so the columns of each row stay together. On a computer, use Data, Data cleanup and Remove duplicates. Confirm whether the data has a header row, then choose the columns to compare. Use only the columns that define the duplicate record. Review the result and compare the record count and important values with your expected outcome.
Make a copy when you need to preserve the source
A cleanup can permanently change the working data. Save an authorised copy or work on a separate sheet when you need the original import. Name the cleanup copy clearly and inspect the relevant rows there. If the output will feed another report, verify its formulas and row counts before replacing any approved data.
Select the full table and choose comparison columns in the dialog
A common mistake is selecting only the ID column when the ID has related fields to its right. That can affect only the selected data and leave other columns out of alignment. Select the whole table, then use the dialog to specify which columns decide duplication. Check a known row afterward to confirm its values still belong together.
Check which copy remains
If duplicate rows have conflicting data, settle what to keep before removal. Do not depend on a cleanup command to choose the most accurate or newest record. If the table has already been sorted, verify the retention rule in a working copy. Check that the remaining row contains the approved values, not just the expected ID.
Read the result in context
Google notes that the Remove duplicates command can treat identical values as duplicates despite differences in letter case, formatting or formulas. If distinctions like case or formula provenance matter to your data, inspect the records using an appropriate comparison workflow before running the removal. Verify the remaining rows against the actual requirements.
Back to contents · Next section
Use UNIQUE when you want a separate list
Put a UNIQUE formula in an empty output area. For a single list in A2:A100, it returns distinct values from that range. For a multi-column range, the usual row-based use returns distinct combinations across the included columns. It creates a result without removing the source records.
=UNIQUE(A2:A100)If blank source rows should be excluded, filter them out before applying UNIQUE. For a list of IDs, this example uses a nonblank condition. If the source has no nonblank values, FILTER returns a no-match result that you can handle separately.
=UNIQUE(FILTER(A2:A100,A2:A100<>""))Decide whether you need distinct rows or a distinct key list
A source range with ID, name and status can have two rows with the same ID but different statuses. UNIQUE across all three columns can return both. If you only need a list of IDs, apply it to the ID column. If you need one approved full row for each ID, decide which row to retain before creating the report. A distinct list and a reconciled register are different outputs.
Keep the result area clear
UNIQUE can return several cells. Leave room below the formula and across the required columns. If an existing value blocks the output, move the formula or clear only the intended destination after checking the data. Edit the source when you need to change a row. The generated result is not a separate editable copy of every value.
Inspect entries that look the same
Extra spaces or other hidden differences can produce distinct-looking records that appear identical on screen. Google’s UNIQUE guidance notes hidden text such as trailing spaces as a cause to check. Compare the values in the source and clean an authorised working copy where appropriate. Verify the distinct result afterward.
Back to contents · Next section
Check spaces, case and data types before cleanup
Imported records can contain leading or trailing spaces, inconsistent labels or values with different types. Highlight repeats and inspect samples before applying a cleanup. Google documents a Trim whitespace command for supported data, while some characters such as non-breaking spaces may need a different approach. Check how the specific imported values behave.
Preserve reference numbers that have leading zeros
An ID such as 0042 can be different from 42 under the issuer’s rules. Use the required data type and check the display before comparing. If Sheets already converted an ID to a number, adding a formatting style may change what you see without restoring the original data meaning. Compare against the approved source before making changes.
Check entries that differ only in case
Some workflows treat upper- and lower-case text as equivalent. Others use case-sensitive identifiers. Google documents case treatment for its duplicate-removal tool. If case matters, use a comparison appropriate to the identifier and review the records before removal. Check a pair that differs only in case as part of your test.
Keep normalised helper data separate during review
In an approved working copy, a helper column can show the cleaned comparison key while the original value remains visible. Review a few examples and resolve ambiguous values with the responsible person. Use the helper only for the comparison it was created for. Check whether the final output should retain the original formatting or use the corrected value.
Back to contents · Next section
Work through a duplicate import with conflicting rows
A class event register is imported twice. Most repeated rows have identical IDs, sessions and statuses. A few have the same ID and session but a different attendance status. Highlight the repeated key with COUNTIFS. Verify that each highlighted record refers to the same person and session. Keep the exact repeated copies separate from rows that need a decision.
For the identical entries, use the approved cleanup workflow on the full table. For conflicting statuses, check the source or ask the organiser which one is correct. Once resolved, apply the correct values and verify the final count. Check one person with two sessions to make sure both valid rows survived. Open any summary that depends on the register and compare its figures.
Practise on a small table first
Create a harmless test table with one unique row, one exact duplicate and one repeated ID in a different session. Highlight repeated IDs, then highlight repeated ID-and-session pairs. Compare the two outputs. Run Remove duplicates on a test copy using the correct key. Verify the rows that remain and inspect the unique list on a separate sheet.
Back to contents · Next section
Check how the comparison treats symbols and formats
A spreadsheet comparison can interpret some characters in a criterion rather than taking them as plain text. For straightforward labels and IDs, a COUNTIF rule is easy to review. If your identifiers can contain symbols such as an asterisk or question mark, check the function’s criteria rules and use a suitable literal or exact comparison. Verify the actual behaviour with two known values before relying on the highlight.
Check a code that looks like a number
An equipment identifier such as 0007 can be intended as text. If it becomes the number 7 during import, a formatted display might put zeros back on screen without restoring the original distinction. Inspect the source and use the data type the issuer requires. If 7 and 0007 are different identifiers, keep them distinct in the working key and verify both after cleanup.
Check a name with a small visible difference
Two names may differ in accent, spacing or punctuation. Decide whether they should be considered the same based on the record policy and a reliable identifier. Do not assume that removing punctuation or changing case is always appropriate. A helper value can make a comparison more consistent, while the original field remains available for review. Use an approved copy and check the effect on a few examples.
Check values produced by formulas
A formula result and a typed value can display the same thing. Google’s cleanup guidance explains that identical values can be treated as duplicates even when formulas or formatting differ. If the formula itself matters, inspect it before removing any row. A reporting view may need the displayed result only, while a master table may need to preserve how it was produced.
Back to contents · Next section
Reconcile repeated IDs before choosing which row to keep
Open the repeated rows together. Compare the fields that can change, such as status, location or date. Identify the source that is authoritative for each field. Do not assume the last row is newest unless the input process establishes that. A table can have been sorted or imported out of order. Use an approved timestamp or source record and check any conflicting data directly.
Same ID, same values
If two rows are exact repeats of one record, removing an extra copy may be appropriate. Check how the repeated row arrived and whether any unique field is hidden outside the comparison range. In a copy, remove the duplicate and verify that one full record remains. Open any dependent summary and confirm that the count changed by the expected amount.
Same ID, different statuses
Suppose two records for the same participant and session say Pending and Confirmed. Confirm the correct status with the approved source. If the register is supposed to show one current row, keep the verified one after resolving the conflict. If it is a history log that records status changes, both may belong there. The duplicate key depends on what the table represents.
Same name, different IDs
Keep both records when their IDs establish that they refer to different people. The same principle applies to books with the same title but different accession numbers or products with similar names and different codes. Review the record identifier, not just the most familiar label. Check this case specifically after any cleanup by name.
Same event repeated in a valid log
A student can borrow the same item more than once. A measurement can be repeated intentionally. If repeated events are valid, use a record key that can distinguish them, such as an event ID or the appropriate combination of fields. A distinct list may still help with reporting. Keep the full event records in the source.
Back to contents · Next section
Use a separate review table when the source must stay intact
If the original import needs to remain available, keep it in the approved location and do cleanup in a working copy or separate output. Include a source reference or row identifier where the workflow calls for it. That lets the reviewer find the original record when a value is uncertain. Do not rely on a formula output alone for a record of what was received.
Create a unique list for a dropdown or summary
If you need a list of sessions rather than the full register, apply UNIQUE to the session column. Check empty entries and labels that differ only because of spaces. If the list feeds a dropdown, verify that the source range contains the intended values and that the output has room to grow. Keep old session values if required for historical records.
Compare a live list with a fixed report
A live formula result can change when the source changes. That is useful for a dashboard. A report submitted at a deadline may need a fixed authorised copy. Verify the values and take a separate approved snapshot where required. Keep the source and any accepted output distinct so later changes do not obscure what was sent.
Keep a helper calculation visible during review
For a composite key, a helper column can display a repeat count or an explicit review flag. Check its range and criteria on a known case. If the count is two, inspect both records before deciding what to keep. A helper can support review without requiring permanent changes to the source. Remove or preserve it according to the workflow for the final sheet.
Back to contents · Next section
Check the first and last rows before using Data cleanup
Confirm that the selection includes every column that belongs to each record. Check whether row one is a header and tell the tool accordingly. Verify that the selection stops before notes or another table. If the table uses an extra totals row, decide whether it belongs outside the cleanup. A duplicate rule should not treat the header or a summary as ordinary records.
Make a small test with a known expected count
Create a copy with six rows. Let two be exact repeats, two share a name but have different IDs and the last two have one ID in different sessions. If the key is ID plus session, only the exact repeated record should reduce the count. Run the cleanup on the test copy, then verify the five remaining records and all related columns.
Use the result count as a checkpoint
After cleanup, compare the number removed with your review. If the tool removed more than expected, inspect the selection and comparison columns. Return to the preserved source or undo where appropriate. A low count does not establish correctness either. Check that a known duplicate was resolved and that a legitimate repeated event remains.
Inspect an important record after sorting or cleanup
Find one record by its ID and compare every relevant field with the source. If a name now appears beside a different session or status, check whether only part of the table was moved. Repair from the preserved source. A coherent row is more important than an apparently tidy list of IDs.
Back to contents · Next section
Troubleshoot duplicate results that disagree with each other
A conditional-formatting rule, a UNIQUE result and Remove duplicates can answer different questions. A highlight may check one column. A UNIQUE formula may compare all included columns. A removal action may compare a selected subset. If their totals differ, inspect the question each one asks before treating the difference as an error.
The row is highlighted but still appears in UNIQUE
Check the ranges. A repeated ID can be highlighted while both rows remain distinct across name, date or status. If the report needs distinct IDs, apply UNIQUE to the ID column. If it needs a reconciled full row, resolve conflicting fields before making that list. The correct output depends on the record key and the report’s purpose.
Two values appear identical but are not highlighted
Open the actual cells and inspect leading or trailing spaces, type and any hidden characters. Check the first and last rows of the comparison range. If the rule omits a later record, extend the range. If the values differ in a meaningful way, keep them separate. If the difference is an import artefact, correct an approved copy and retest.
The wrong cells are coloured
Compare the first row of the applied range with the formula reference. A rule applied to A2:D100 usually needs its first current-row reference to use row two. Lock the key column where each cell in the row should inspect the same ID. Test a row with a repeated value and the row immediately above or below it. Correct the applied range or formula before using the colours for review.
Back to contents · Next section
Prepare a short review record for a shared register
If the cleanup affects a team report, note the comparison key, any decisions about conflicting rows and the expected result count where the process permits. Keep the original and reviewed copy in their appropriate locations. Give the responsible person a way to find a sample record and verify it. Avoid adding unnecessary personal data to the review notes.
Confirm the comparison rule with a teammate
Ask the reviewer to name one record that should be removed and one repeated value that should remain. Compare their reasoning with the selected key. If they disagree about the task, settle the rule before changing the master table. The review should make clear whether the register tracks people, events or one current status per record.
Check related reports before replacing a source
Open any formula, dashboard or exported summary that depends on the table. Verify its counts and selected rows. A cleanup can change a report even if the main sheet looks correct. If the result is accepted, preserve the approved version and follow the handoff process. If a later correction is needed, return to the preserved source or approved working copy.
Back to contents · Next section
Use an exact-text check when case or symbols are part of an identifier
For approved text IDs where letter case and punctuation must match literally, test an exact comparison in a helper column. The following example counts exact matches to A2 in a small fixed range and leaves a blank key empty. Copy it down and inspect a known pair before applying any cleanup.
=IF(A2="","",SUMPRODUCT(--EXACT($A$2:$A$100,A2)))A count of two means the exact-text comparison found two occurrences within that range. This is useful when codes such as Ab7 and AB7 must remain distinct. It still cannot recover an original code if a prior import already removed leading zeros. Check the data type and the approved source first.
Use the helper as evidence, then preserve the intended records
Do not run the ordinary Remove duplicates command on a case-sensitive key and assume it will follow the exact-text rule. Review the highlighted or counted rows and use an approved case-sensitive cleanup workflow. Inspect a pair with different case and a pair with identical spelling after the change. The result should retain every distinct identifier required by the task.
Compare visible repetition with a verified key
A participant name can repeat while the ID does not. A code can look similar to another code in a narrow column. Widen the view or open the actual values and compare the relevant fields. The helper is useful when the underlying values need a clear test. The decision about which rows represent one record still comes from the table’s rules.
Back to contents · Frequently asked questions
Frequently asked questions
How do I highlight duplicate cells without deleting them?
Use a custom COUNTIF rule in conditional formatting and set the comparison range and first cell correctly.
Can I highlight the whole duplicate row?
Apply the rule to the full row range and lock the column of the comparison key, such as $A2.
Which columns should I select in Remove duplicates?
Select the full table, then choose the columns that define a duplicate in the dialog. Keep related fields together.
Will Remove duplicates choose the latest record?
Decide which record to keep before removing duplicates. Review timestamps and conflicting values against the approved source.
What is the difference between UNIQUE and Remove duplicates?
UNIQUE produces a separate result. Remove duplicates changes the selected data.
Why do values that look alike remain separate in UNIQUE?
Check for spaces, hidden differences and the selected columns. Two rows with the same ID and different statuses can be distinct combinations.
How can I find a duplicate using two columns?
Use COUNTIFS with both comparison ranges, or choose both columns in the cleanup dialog when they define the key.
Can I keep one copy only after checking several key columns?
Yes, when those columns identify a duplicate record under the task’s rules. Review conflicting values first, then select the full table and choose the comparison columns.
Why do highlighting and UNIQUE return different results?
Check which columns each one compares. A repeated key can be highlighted while two full rows are distinct because another field differs.
Helpful reading
- Google Docs Editors Help on removing duplicates and trimming whitespace
- Google Docs Editors Help on conditional formatting with a custom formula
- Google Docs Editors Help on the UNIQUE function
- Google Docs Editors Help on COUNTIF criteria and wildcards
- How to Name and Organise Files for School and Online Applications
- How to Make a Dropdown List in Google Sheets
- How to Split Text Into Columns in Google Sheets
- How to Use FILTER in Google Sheets With One or Multiple Conditions
Review the record before removing its row
Use a comparison key that matches the task. Highlight or preview first, then verify the actual records and totals after cleanup.