How To Highlight Duplicates In Google Sheets

Learning how to highlight duplicates in Google sheets is crucial if you store most of your data there. You may find yourself in a situation where you need to find repeated values on a data range. For example, when you make a mistake by repeating a value in a sheet when recording your accounts.

It’s easy to identify duplicates in google sheets using conditional formatting. You can highlight these entries by using a color such as red or yellow or a formatting style such as bold. It’s also possible to use the same process to highlight multiple repeat values in a google sheet. You can also use it to identify and correct mistakes in a document.

Advantages of Learning How to Highlight Duplicates in Google Sheets

Knowing how to highlight duplicates in Google sheets will save you time using Google sheets. For example, if you have a data set in a long column, you don’t have to move through every cell to pick similar data. You can highlight them at a go in minutes.

In a data set, you can also identify several duplicate values, such as A, B, and C. Once you find each value, you can highlight it using a different color. Such an approach will make your work more manageable when looking at a google sheet with different highlighted values.

The easiest way to identify duplicates in a Google sheet is by using conditional formatting. Here’s how to do it.

How to Highlight One Duplicate Value in Google Sheets with Conditional Formatting

  • Sign in to your Google sheet, and open the preferred spreadsheet.
  • Pick the cells you want to check for duplicates. It could be a row of cells, a column, or a cell range.
  • Click Format on the toolbar and select conditional formatting from the resulting drop-down menu.
  • A sidebar will pop up where you can input the formula for identifying duplicate data, also known as a rule.
  • You will find text on the Format rules box saying “Format cells if”
  • Pick what you prefer from the format rules box. Some examples of terms you will see are ‘Is not empty”, “Text contains,” and “Text does not contain.”
  • Choose “Text contains,” and a box will pop up where you can type in the repeated value you are looking for. For example, “F” if your data is made up of alphabets, or “1” if they are numbers.
  • Go to the box below “formatting style” and pick the style or color to highlight your duplicate value. You may also keep it in default mode.
  • Your repeat data will be highlighted with the formatting style you choose or the default color specified on Google sheets.

How to Highlight Several Duplicate Values in a Google Sheet with Conditional Formatting

You may have several repeated values on your Google sheet that you want to highlight. Here’s how to do it.

  • Open the Google sheet containing your data.
  • Highlight the set of values you want to work on. It could be several rows or columns.
  • Click on “format” on the toolbar and select “conditional formatting” from the drop-down menu.
  • A “conditional format rules” box will pop up.
  • The location of the data you want to work on will be specified in the “Apply to range” box. An example is A1:C11, representing three columns, A, B, and C, with data in rows 1 -11.
  • Go to the “format cells if box” beneath and choose the rule you want to apply. e.g., “Text contains.”
  • Specify the data you are looking for. For example, “abc” or “hkj.”
  • Choose how to highlight the values you are looking for under the formatting style box.
  • You may pick a color or writing style such as “bold” or “italic.”
  • Click done, and the data you are looking for will be highlighted in the style you prefer.
  • Next, click “add another rule” in the “conditional format rules” box to find another value.
  • Repeat the process until you’ve identified all the data you are looking for.


Now you know how to highlight duplicates in Google sheets. Next time you have many duplicates to find, you don’t have to spend too much time doing it. Use conditional formatting to find the data you are looking for fast.

Leave a comment

Your email address will not be published.