How to Highlight Duplicates in Google Sheets

3 months ago 60

No substance however cautious you are entering oregon importing data, duplicates tin happen. While you could find and region duplicates, you whitethorn privation to reappraisal them, not needfully region them. We’ll amusement you however to item duplicates successful Google Sheets.

You whitethorn person a database of lawsuit email addresses and telephone numbers, merchandise identifiers and bid numbers, oregon akin information wherever duplicates shouldn’t exist. By locating and highlighting duplicates successful your spreadsheet, you tin past reappraisal and hole the incorrect data.

While Microsoft Excel offers an casual mode to find duplicates with conditional formatting, Google Sheets doesn’t presently supply specified a convenient option. But with a customized look successful summation to the conditional formatting, highlighting duplicates successful your expanse tin beryllium done successful a fewer clicks.

Find Duplicates successful Google Sheets by Highlighting Them

Sign successful to Google Sheets and unfastened the spreadsheet you privation to enactment with. Select the cells wherever you privation to find duplicates. This tin beryllium a column, row, oregon compartment range.

Selected compartment  range

Click Format > Conditional Formatting from the menu. This opens the Conditional Formatting sidebar wherever you’ll acceptable up a regularisation to item the duplicate data.

Select Format, Conditional Formatting

At the apical of the sidebar, prime the Single Color tab and corroborate the cells beneath Apply to Range.

Confirm the cells successful  Apply To Range

Below Format Rules, unfastened the drop-down container for Format Cells If and prime “Custom Formula Is” astatine the bottommost of the list.

Select Custom Formula Is

Enter the pursuing look into the Value oregon Formula container that displays beneath the drop-down box. Replace the letters and compartment notation successful the look with those for your selected compartment range.


Here, COUNTIF is the function, B:B is the scope (column,) B1 is the criteria, and >1 is much than one.

Alternatively, you tin usage the pursuing look for nonstop compartment references arsenic the range.


Here, COUNTIF is the function, $B$1:$B$10 is the range, B1 is the criteria, and >1 is much than one.

Under Formatting Style, take the benignant of item you privation to use. You tin usage the Fill Color icon to take a colour from the palette. Alternatively, you tin format the font successful the cells with bold, italic, oregon a colour if you prefer.

Fill Color palette

Click “Done” erstwhile you decorativeness to use the conditional formatting rule. You should spot the cells containing duplicate information formatted with the benignant you selected.

Duplicates Highlighted successful  Google Sheets

As you marque corrections to the duplicate data, you’ll spot the conditional formatting vanish leaving you with the remaining duplicates.

RELATED: How to Highlight a Row successful Google Sheets Using Conditional Formatting

Edit, Add, oregon Delete a Conditional Formatting Rule

You tin marque changes to a rule, adhd a caller one, oregon delete a regularisation easy successful Google Sheets. Open the sidebar with Format > Conditional Formatting. You’ll spot the rules you’ve acceptable up.

  • To edit a rule, prime it, marque your changes, and click “Done.”
  • To acceptable up an further rule, prime “Add Another Rule.”
  • To region a rule, hover your cursor implicit it and click the trash tin icon.

Edit, add, oregon  delete a rule

By uncovering and highlighting duplicates successful Google Sheets, you tin enactment connected correcting the incorrect data. And if you’re funny successful different ways to usage conditional formatting successful Google Sheets, look astatine however to apply a colour standard based connected value oregon however to highlight blanks oregon cells with errors.

Read Entire Article