How to Highlight Duplicates in Google Sheets

How to Highlight Duplicates in Google Sheets

Duplicate data isn\’t a good thing for spreadsheet users, as it leads to real problems. What do you think would happen if duplicate data was accidentally inserted? Properly managing this problem is crucial for maintaining the integrity of your spreadsheet.

In this article, we\’ll go through a quick way to highlight duplicates in Google Sheets. We will also cover some side benefits of highlighting duplicates in certain sheets. So, if you are also among the ones searching for it, then keep reading this blog post till the very end.

Google Sheets is a great tool for managing data and presenting it in a spreadsheet format. It\’s by no means a difficult software to learn, but now and again you might find that some of your data have duplications that you don\’t want. Luckily, it\’s pretty easy to highlight cells with duplicates in Google Sheets. We will be mentioning the process shortly!

Guide to Highlight Duplicates in Google Sheets


Follow the next-mentioned steps carefully, in order to successfully highlight duplicates inside your Google Sheets. This will ultimately assist you in getting rid of these results or customizing them according to your specific needs and requirements. 

Logging Into Google Sheets Account

  • First, open a web browser of your choice on your PC/Laptop. For this guide, we are using the well-renowned Google Chrome web browser.
  • Next, navigate to this link. This is the official web link to the Google Sheets homepage.
  • After that, you will see a three horizontal lines icon i.e., (≡) appearing on the top left-hand corner of your screen. Click on it. 
  • Now, from the opened sidebar, click on the option labeled Sign In.
  • Next, sign in to your account by entering the correct credentials i.e., Username/Email Address and Password.
  • After that, once logged in successfully you will land on your Google Sheets account main page.

Highlighting Duplicates in Google Sheets

  • Now, from here select the Google Spreadsheet which you want to open. Then double-click on it.
  • Next, proceed with picking the columns, rows, or a cell range for which you desire to find the duplicate values.
  • After that, go to the \”Menu\” and from here click on \”Format\”, followed by clicking on \”Conditional Formatting\” from the dropdown list of options. 
  • Now, you will see a sidebar opened on the right-hand side of your screen with the label \”Conditional Format Rules\”. Here you will be making a rule to highlight the duplicate data values.
  • Next, make sure that the \”Single Color\” tab is selected. Then insert the cell range under the \”Apply to Range\” option. For example, you can type like this, B1:B10.
  • After that, under the \”Format Rules\” option, click on the dropdown arrow to open the dropdown list of options. From the available ones, click on the one labeled \”Custom Formula Is\”.
  • Now, type the following formula into the Formula box. (Note: Replace the letters and cell references in the formula with those for your selected cell range.)

In the above formula:

  • COUNTIF is the function
  • B:B is the column range
  • B1 is the criteria
  • and, >1 is greater than one
  • Next, navigate to the \”Formatting Style\” option. From here, you can pick a highlight of your choice. You can also give a specific color for the duplicates, or make them bold, italic, or underlined in order to easily differentiate them from other cell entries.
  • Finally, click on the button labeled \”Done\”, present at the bottom right-hand corner of your screen. With this, you will see the duplicated entries highlighted inside your Google Spreadsheet, in the format which you selected priorly. That\’s it! Now, you can do whatever you want with these cells freely.

See also: How to convert Google Sheets into PDFs


Google Sheets is a powerful tool for managing data, and one of its most useful features is the ability to highlight duplicates. By using the built-in conditional formatting tool, you can quickly identify any cells that contain duplicate data. Once you have found the duplicates, you can delete them manually or use one of the many available formulas to automatically remove them from your sheet.

By following the mentioned steps in this blog post, you can quickly and easily identify duplicate values in your data set, which can save you time and effort in the long run. Give it a try next time you\’re working with data in Google Sheets!

Have you ever tried to highlight duplicates in Google Sheets? Whether you are successful in doing it or not? Which method did you use to perform this task? Has our explained guide worked for you? If you have done this before, share your experience with us in the comments section below.

You are also free to ask any questions or queries you have in your mind that are bothering you. We will appreciate it and try to answer them in the best possible manner. Overall, if you liked this article, give it a thumbs up. Also, share it with your friends, colleagues, and loved ones. Thank you all in advance guys and stay blessed forever.

Leave a Comment

Your email address will not be published. Required fields are marked *