How-To

How to Highlight Duplicates in Google Sheets

If you need to highlight duplicates in Google Sheets, you can use conditional formatting. Here’s how.

While Google Sheets doesn’t have quite the same depth of features as Microsoft Excel, it still has a number of useful tricks under the hood. One of its most useful features is conditional formatting.

This allows you to change the formatting of any cells that obey a specific set of conditions that you create. You could, for instance, highlight all of the cells that are above a certain value, or contain a specific word. Conditional formatting has a number of uses, such as highlighting errors or comparing data.

Another useful application of conditional formatting is highlighting duplicates. This allows you to quickly spot any duplicate text or values within a large spreadsheet. Here’s how it’s done.

Highlighting Duplicates From a Single Column

If you want to highlight duplicates from a single column in Google Sheets, you’ll need to first select the column where you want to find duplicates.

Selecting a column in Google Sheets

With your column selected, click Format > Conditional Formatting from the menu bar.

The Conditional Formatting menu option

If you already have conditional formatting active in that column, click Add another rule. Otherwise, you can edit the rule that is currently showing.

Adding a Google Sheets Conditional Formatting rule

Press the Format Cells If box and choose Custom formula is from the dropdown options.

Selecting Custom Formula conditional formatting rules

Type =countif(A:A,A1)>1 into the Value or formula box or replace all instances of the letter in the formula with your chosen column.

For example, to apply the formatting to column M, your formula would be =countif(M:M,M1)>1

Adding a custom formula rules

You can set the formatting style yourself, by selecting the color and font options. You can also use one of the various preset formatting styles by clicking on the Default text under the Formatting style options, then selecting one of the presets.

Changing conditional formatting styles

Click Done to add your conditional formatting rule. Any duplicate cells should now appear with your chosen highlighting.

Highlighting Duplicates From Multiple Columns

It’s possible to apply this formatting to more than one column, highlighting any duplicates that appear across your selected cell range.

To begin, select the columns that you want to include, hold down the Ctrl key, then click on the letter at the top of each column to add individual columns.

You can also hold down the Shift key and click on the first and last cells in your range to select multiple columns at once.

Selecting multiple cells in Google Sheets

With your cells selected, press Format > Conditional Formatting.

The Conditional Formatting option in Google Sheets

Edit the current rule or press Add another rule to add a new rule.

Adding a Google Sheets Conditional Formatting rule

Press the Format Cells If… option and choose Custom formula is.

Selecting Custom Formula conditional formatting rules

Type =COUNTIFS($A$1:Z,A1)>1 into the Value or formula box. Replace each A with the letter of the first column in your selection and Z with the last column in your selection.

For example, if your data was in columns M to Q, then =COUNTIFS($M$1:Q,M1)>1 would be the correct formula.

A COUNTIFS formula in Google Sheets

You can select your own formatting style in the Formatting style section or click on the Default text to apply one of several preset formatting options.

Changing conditional formatting styles

Click Done to confirm and save the rule—your duplicates will now be highlighted across all the columns that you selected.

Any columns that you missed out will be not be highlighted, but will still count towards the number of duplicates.

Selected duplicates in Google Sheets

Using Powerful Google Sheets Features

Conditional formatting is a useful feature that allows you to easily spot duplicated information across a large spreadsheet, but there are plenty of other features that can be incredibly useful for Google Sheets beginners and regular users.

You may wish, for instance, to add in-cell validation to your spreadsheets to improve the quality of your data. If you’re looking to create automated tasks, you may want to look at using Google Sheets to send emails based on cell values.

Click to comment
To Top