How to Highlight Duplicate Values in Google Sheets

Google Sheets is a powerful tool for managing and analyzing data. However, when working with large datasets, duplicate values can sometimes cause errors or confusion. Fortunately, Google Sheets provides an easy way to highlight duplicates using Conditional Formatting. In this guide, we’ll show you step-by-step how to automatically highlight duplicate values in your spreadsheet.

---

Why Highlight Duplicates?

Highlighting duplicates can help you:

• Identify errors in data entry

• Remove unwanted repeated values

• Ensure data accuracy in reports

• Organize and clean up your spreadsheet efficiently

---

Step-by-Step Guide to Highlighting Duplicates in Google Sheets

Follow these simple steps to automatically color duplicate values in your Google Sheets:

Step 1: Select the Data Range

1. Open your Google Sheet.

2. Click and drag to select the range of cells where you want to check for duplicates. If you want to check an entire column, click on the column letter (e.g., column A).

Step 2: Open Conditional Formatting

1. Go to the Format menu at the top.

2. Click Conditional formatting.

3. A new menu will appear on the right side of your screen.

Step 3: Apply a Custom Formula

1. Under the Format rules section, click on the dropdown and select Custom formula is.

2. Enter the following formula:

   =COUNTIF(A:A, A1) > 1

   • Replace A:A with your actual column range (e.g., B:B for column B).

   • Replace A1 with the first cell in your selected range.

3. Choose a color under the Formatting style section.

4. Click Done.

Now, any duplicate values in your selected range will be automatically highlighted with the chosen color.

---

How Does This Formula Work?

The formula =COUNTIF(A:A, A1) > 1 works as follows:

• COUNTIF(A**:A****, A1)** counts how many times the value in A1 appears in column A.

• If the count is greater than 1, it means the value is a duplicate.

• Conditional formatting then applies the selected color to all duplicate values.

---

Example Usage

A	COUNTIF(A:A, A1)	Highlighted?
5	2	Yes
3	1	No
7	1	No
5	2	Yes

In this example, the number 5 appears twice in column A, so both occurrences are highlighted.

---

Bonus Tip: Remove Duplicates

If you want to remove duplicate values instead of just highlighting them, follow these steps:

1. Select the data range.

2. Click Data > Data cleanup > Remove duplicates.

3. Choose whether to include the header row.

4. Click Remove duplicates.

---

Final Thoughts

Using Conditional Formatting to highlight duplicates in Google Sheets is a quick and efficient way to clean up your data. Whether you're managing inventory, financial records, or survey responses, this method will help you spot errors and improve data accuracy.

Try it out today and make your spreadsheets smarter!

---

Let me know if you need any modifications or additional tips!