How do I conditional format a duplicate in Google Sheet?

To conditionally format duplicate values in Google Sheets, you will use a custom formula with the COUNTIF function within the Conditional Formatting rules. This powerful feature helps you quickly identify and visualize repeated data entries in your spreadsheet.

How to Highlight Duplicates in Google Sheets

Identifying and highlighting duplicate entries is essential for data cleaning and analysis. Follow these steps to set up conditional formatting for duplicates:

Step-by-Step Guide to Conditional Formatting

Select Your Data Range:
- Begin by highlighting the column or range of cells where you want to find duplicates. For example, if you want to check for duplicates in column A, click on the column header 'A'. If it's a specific range like A1:C100, select those cells.
Access Conditional Formatting:
- Navigate to the top menu, click on Format, and then select Conditional formatting. This will open the 'Conditional format rules' sidebar on the right side of your screen.
Choose 'Custom formula is':
- In the 'Format rules' section of the sidebar, locate the 'Format cells if...' dropdown menu.
- Scroll down and select the option 'Custom formula is'. This allows you to input a specific formula to define your formatting condition.
Enter the COUNTIF Formula:
- In the 'Value or formula' field, enter a COUNTIF formula designed to identify duplicates. The formula checks if a value appears more than once within the specified range.
- Example Formula for Column A:
  - If your selected range starts at cell A1 and covers the entire column, use:
```
=COUNTIF(A:A,A1)>1
```
  - If your selected range is specific, like A1:A100, use:
```
=COUNTIF($A$1:$A$100,A1)>1
```
  - Explanation:
    - COUNTIF(range, criterion) counts how many times a value appears in a given range.
    - A:A (or $A$1:$A$100) is the range where you are looking for duplicates.
    - A1 is the first cell of your selected range. The formula evaluates this condition for each cell in your 'Apply to range' relative to its own value.
    - >1 means the cell will be formatted if its value appears more than once in the specified range, thus indicating a duplicate.
Define the 'Apply to range':
- Ensure the 'Apply to range' field correctly reflects the cells you want the formatting to apply to. This should be the same column or range you highlighted in step 1.
- For example, if you want to highlight duplicates in column A from A1 downwards, the range would be A1:A or A1:A1000 (or whatever your data extent is).
Use Absolute Values for Range:
- When defining the range within your COUNTIF formula, it's crucial to use absolute references (e.g., $A$1:$A$100 or A:A). This ensures that the range for counting remains fixed as the conditional formatting rule is applied to each cell in the 'Apply to range'.

Customizing the Format

After entering the formula, you can customize how the duplicate cells will appear:

Formatting Style: In the 'Formatting style' section of the sidebar, choose your desired format. You can select:
- Fill color (e.g., light red for duplicates)
- Text color
- Bold, italic, or strikethrough text
- Borders
Click Done: Once you're satisfied with the formatting, click Done to apply the rule.

Example Formulas for Different Scenarios

Scenario	Apply to Range	Custom Formula	Description
Entire Column A	`A:A`	`=COUNTIF(A:A,A1)>1`	Highlights any cell in column A if its value appears more than once within the entire column A. `A1` is the anchor cell; Google Sheets automatically adjusts this for each cell in the `Apply to range`.
Specific Range (A1:A100)	`A1:A100`	`=COUNTIF($A$1:$A$100,A1)>1`	Highlights any cell in the range A1:A100 if its value appears more than once within that specific range. Using `$` creates an absolute reference, preventing the range from shifting as the formula is evaluated for other cells.
Across Multiple Columns	`A1:C100`	`=COUNTIF($A$1:$C$100,A1)>1`	Highlights duplicates within the combined range of A1 to C100. Be cautious, as this will highlight a duplicate if it appears anywhere in the A1:C100 block, not just within its own column.
Highlight Unique Values	`A:A`	`=COUNTIF(A:A,A1)=1`	If you want to highlight unique values instead of duplicates, change `>1` to `=1`. This will format cells whose values appear only once in the specified range.
Duplicate Rows (Complex)	`A1:C100`	`=COUNTIF(ARRAYFORMULA($A$1:$A$100&$B$1:$B$100&$C$1:$C$100),$A1&$B1&$C1)>1`	This formula checks for entire rows that are duplicates (i.e., all values in columns A, B, and C for a given row match another row). The `ARRAYFORMULA` concatenates the values of columns A, B, and C for each row to create a unique identifier for that row, then `COUNTIF` checks for duplicates of these concatenated strings.

For more details on using COUNTIF and other functions, you can refer to the Google Docs Editors Help documentation.

Best Practices

Specificity: Be clear about your 'Apply to range' and the range within your COUNTIF formula. Mismatched ranges can lead to unexpected results.
Order of Rules: If you have multiple conditional formatting rules that might overlap, remember that the order matters. Rules higher in the 'Conditional format rules' list take precedence. You can reorder rules by dragging them.
Performance: For very large datasets (tens of thousands of rows or more), extensive conditional formatting, especially with complex custom formulas, can sometimes impact spreadsheet performance.

By following these steps, you can effectively conditional format duplicate values in Google Sheets, making your data analysis more efficient and accurate.