Find data quicker using highlight duplicates in Google Sheets

November 24, 2021 Dominic Lill Data, Google, sheets, spreadsheet

Is there a way to find data quicker in Google Sheets?

Using Google Sheets is all about seeing and finding things faster, but it also helps you get your work done more efficiently and in less time. Still, many users of Google Sheets today, even advanced business owners and corporate administrators, aren’t using Google Sheets the way it should be used. For example, you can highlight duplicates in Google Sheets and save yourself and your business hours of spreadsheet sweat. This feature is one that you will soon become obsessed with at work.

Learn how to find your data quicker and highlight duplicates in Google Sheets right here.

Identifying the duplicates problem

Duplicates in a spreadsheet on Google Sheets are a common error, among the most common. Sometimes it is not even an error, but you simply want to find where those duplicates are. One way that people using Google Sheets deal with this is by manually removing duplicates, or manually formatting duplicates with highlighting formatting.

You don’t have to do this. Google Sheets will help you identify what you need and either highlight or remove your duplicate content. The highlight duplicates feature helps you to locate where those duplicates are, and highlight them so you don’t waste time searching for them again.

To identify duplicates in Google Sheets, be sure that you have editing permissions for the Sheets in question. There are a number of ways you can find your duplicates, and use that data accordingly. You can use conditional formatting in the Format tab and perform simple highlights, or if you have more data to work with, use the CountIF formula within conditional formatting to highlight duplicates in Google Sheets.

Highlight duplicates in Google Sheets

To highlight duplicates in Google Sheets, start practising with a single column or row that you want to highlight duplicates with. Start by finding the cell that has a duplicate in its column. Highlight the column that you are examining. Let’s say you are looking for duplicates of the word “red.”

Go to the Format tab, and choose the “Conditional formatting” function. Now you are going to use your Countif formula. Click on “Format rules” in the drop-down menu, and scroll to “Custom formula is.”

Here is where you will enter your formula. Enter (without the quotation marks): “=countif(range, value)” where the range is the range of cells you are looking at, and “value” is the value of the duplicate you are searching for, in this case, “red.”

So if you are looking at four cells in column A for duplicates of “red”, your formula looks like this: =CountIf($A1:$A4, red)>1. This is telling Google Sheets you are looking for more than one entry of the word red in this specified range.

Your values will then be highlighted. You can then change the colours of these cells by using the paint bucket icon in the Conditional format rules window to highlight the cells with the colour of your choice.

Let’s try a business example.

Highlight duplicates in business records

A business example can help you to understand how to do this for your own work. Let’s say you want to highlight the members of your Marketing Department in your main sheet that contains all employees. The duplicate record you are looking for will be “Marketing,” and you are looking for that in 200 cells of Column B.

Go to the column where this identifier (Marketing) is found. Go to the Format tab and click on “Conditional formatting.” Go to the “Custom formula” option and enter (without quotation marks): =Countif($B1:$B200, Marketing)>1.

Now you will tell Google Sheets what you want that to look like on your sheets. In the same menu, you have “Formatting style” options. Specify how you want that to look. You might choose the colour green, for example, or may want that in bold. Format as you desire, and click Done. You should have an entire column that shows where your Marketing employees are highlighted in the colour of your choice.