In this article, you will learn how to highlight duplicates with Conditional Formatting in Google Sheets.
Assume you look at some survey data and want to check if there are any duplications in the data.
First, look at the example below where you highlight the duplications in the Name column.
The range should be data in the Name column, B3:B13, as you are looking for duplicates there. You use the COUNTIF formula to test each cell value from B3 to B13, such as “Tiger” and “Lion” and see how many values match the tested value in the selected range. If there is any duplication for a tested word, the COUNTIF formula returns “2” or above. So, the “Format cells if…” section defined that if the number given by the COUNTIF for a tested cell is more than 1, the cell should be highlighted. As such, the range should be the absolute reference, whereas a tested cell should be the partial absolute reference. [Insert a link to an article about reference]
If you want to spotlight duplicates across multiple columns, you need to tweak the range and the COUNTIF function, as shown below.
There are three major changes:
(i) The range for the Conditional Formatting should be the entire list - B3:D13
(ii) The range for the COUNTIF function should be the entire list as well
(iii) The tested value in the COUNTIF function should be a relative reference, not a partial absolute reference because you need to test all values in the list.
If you want to highlight these duplicated values in different colors, you need to set up the Conditional Formatting rules - in this case, three rules - by columns and select different fill colors for each rule. Check this article [A hyperlink to be inserted] if you are keen to learn how to use Conditional Formatting based on another cell value first.
If you don’t get the right answer to your question in this article or you have other questions related to Conditional Formatting, you may find the answers in the following articles.
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇