# How to Highlight Duplicates in Google Sheets | Capterra > Duplicated data can be a headache for business owners, especially when it comes to data analysis. Here's how to highlight duplicates in Google Sheets. Source: https://www.capterra.com/resources/how-to-highlight-duplicates-in-google-sheets --- Analytics, Data, & Business Intelligence # How to Highlight Duplicates in Google Sheets Written by: Krista Hillis Krista HillisAuthor Krista Hillis is a writer specializing in business, health, and psychology. She is a frequent contributor to trusted business resources including Capterra. [See bio & all articles](https://www.capterra.com/resources/author/krista-hillis-guest-contributor/) Published November 9, 2023 4 min read Table of Contents - [Why would you want to highlight duplicates in Google Sheets?](#why-would-you-want-to-highlight-duplicates-in-google-sheets) - [How to highlight duplicates in Google Sheets](#how-to-highlight-duplicates-in-google-sheets) - [Additional tips for highlighting duplicates in Google Sheets](#additional-tips-for-highlighting-duplicates-in-google-sheets) - [Get the most out of Google Sheets](#get-the-most-out-of-google-sheets) ## Learn how to highlight duplicate data in Google Sheets so you can avoid faulty data analysis. When producing spreadsheets at scale, duplicate data can find its way into tools like [Google Sheets](https://www.capterra.com/p/169718/Google-Sheets/). Duplicate information is sometimes necessary and relevant. However, when it's not, it can be a frustrating distraction—or worse, duplicate data can contribute to false information. Knowing how to highlight duplicates in Google Sheets—and adjust duplicate records or figures—is crucial for eliminating data redundancies and any associated confusion. ## Why would you want to highlight duplicates in Google Sheets? Duplicate data can hide within larger data sets, especially if you merge tables or share spreadsheets with several users. These redundancies could affect your ability to [analyze data](https://www.capterra.com/data-analysis-software/) effectively and may produce inaccurate results.  When you know how to highlight duplicate data, you are better equipped to spot and manage this information. This step helps ensure you don't make critical business decisions based on faulty data. In some cases, duplicate information could steer your company in an unintended direction, especially when looking at trends. When you learn more about highlighting duplicates in Google Sheets, you can increase the automation of this process, saving one of your most precious resources—time. ## How to highlight duplicates in Google Sheets There are several methods for highlighting duplicates in Google Sheets, including built-in functions and third-party add-ons. There is no singular tool that exists for this purpose. ### Using conditional formatting to check a single column This method is the easiest way to identify data redundancies. Consider the following steps if you have a single column of data you'd like to check for duplicate entries.  **Step one**: Open your spreadsheet. Left-click and drag your cursor to highlight the column you want to search for duplicate data. _Source: All screenshots are from the author_ **Step two**: Click "Format" in the top menu bar. Select "Conditional formatting." **Step three**: In the pop-up menu on the right side of your screen, find the "Format cells if..." dropdown menu—click it to reveal a list of options. **Step four**: Navigate to the bottom of the list. Click "Custom formula is." **Step five**: Create a rule to highlight duplicates in Google Sheets. The most common is the COUNTIF formula, which is \[**\=COUNTIF (A:A, A1)>1**\].  Input "**\=COUNTIF(A:A,A1)>1**" into the editor box that says "Value or Formula." You need to alter this formula depending on which column(s) you aim to analyze–the numbers inside the parentheses refer to the column you want it to look at, and the cell you want it to start on.  Once you have entered the formula and adjusted it to the correct column, you will instantly see any duplicates highlighted within that intended data set. ### Using conditional formatting to check multiple columns When using the COUNTIF function, tweak the range to check for duplicates across several columns. In this case, column A and column B. .  **Step one**: Follow the above steps, one to five, highlighting multiple columns this time. After you click "Custom formula is," you'll access the "Conditional format rules" window, where you can lean on the COUNTIF function.  **Step two**: Use the following formula to check multiple columns → \[**\=COUNTIF($A:$Z,Indirect(Address(Row(),Column(),)))>1**\]. **Please note**: When scanning for duplicates, you can use "Apply to range" to change which specific columns or rows you're targeting. ### Using a third-party add-on You can source Google Sheets add-ons that offer additional features and greater automation. Use these add-ons and formula-free features to assist with duplicates when using Google Sheets.  You can do this within Google Sheets:  **Step one**: Click on the "Extensions" dropdown menu and choose "Get add-ons." **Step two:** Search for "remove duplicates" and follow the instructions to install and use your desired add-on.  ## Additional tips for highlighting duplicates in Google Sheets When highlighting duplicates in Google Sheets, consider the following: - Conditional formatting is dynamic. The formatting will automatically update when you change the data in any cell. - False results can happen if you don't remove other conditional formatting rules from your target cells.  - Google Sheets can miss duplicates if there are extra space characters or an extra space in one cell but not another. Use the TRIM or CLEAN function in Google Sheets to remove additional spaces before searching for duplicates. To find resources on these functions, search "CLEAN Google Sheets" or "TRIM Google Sheets" in the Google Search Help Center. ## Get the most out of Google Sheets  Google Sheets is a powerful [data management](https://www.capterra.com/product-data-management-software/) tool that allows you to quickly and easily organize, analyze, and collaborate. When you use a proactive approach to reduce errors, you'll ensure data quality while benefiting from greater automation. Check out more how-to guides for using Google Sheets: - [How To Search in Google Sheets: A Comprehensive Guide](https://www.capterra.com/resources/how-to-search-in-google-sheets/) - [How to Sort Data in Google Sheets: Simple Methods and Steps](https://www.capterra.com/resources/how-to-sort-in-google-sheets/) - [How to Make a Graph on Google Sheets](https://www.capterra.com/resources/how-to-make-a-graph-on-google-sheets/) ## Capterra's 2026 Software Buying Trends Report ### Download our 2026 Software Buying Trends Report to see how successful software adopters avoid disappointment and how your business can, too. * * * ### Was this article helpful? * * * ## About the Author [### Krista Hillis](https://www.capterra.com/resources/author/krista-hillis-guest-contributor/) Krista Hillis is a writer specializing in business, health, and psychology. She is a frequent contributor to trusted business resources including Capterra. ### RELATED READING - [Free CRM Software vs. Paid: Features, Costs, and Comparison](https://www.capterra.com/resources/free-vs-paid-software-for-crm-guide/) - [Accounting Trends in 2026: How AI Is Changing Work, Skills, and Strategy](https://www.capterra.com/resources/accounting-trends-ai-software-changing-work/) - [Bookkeeper vs. Accounts Payable Software: Which One Should You Choose?](https://www.capterra.com/resources/category-compare-bookkeeper-vs-accounts-payable-software/) - [How to Unlock Real AI Benefits in Sales and Marketing](https://www.capterra.com/resources/ai-benefits-sales-marketing-software/) - [How To Build the Right Project Management Tech Stack for Your Business](https://www.capterra.com/resources/project-management-tech-stack/) - [5 Key Help Desk Software Features With Top Products That Offer Them](https://www.capterra.com/resources/help-desk-software-key-features/) - [Why You Need Project Management Software With ADP Integration](https://www.capterra.com/resources/project-management-software-with-adp-integration/) - [Help Desk Software for IT Support: Features and Benefits 1,300+ Users Value](https://www.capterra.com/resources/it-help-desk-software-features-benefits/) - [AI in Talent Management Software: The Bridge Between Recruiting and Retention](https://www.capterra.com/resources/ai-talent-management-software/)