In Excel, you may find the need to pinpoint duplicate values to remove, replace, highlight, filter, or count them.

Although there isn’t a dedicated feature to identify Duplicates like “Remove Duplicates,” we have several functions and features to do so.

Here, we will go through on how to use Conditional Formatting and functions like FILTER, COUNTIF, IF, etc., to find duplicates in 5 different cases.

## Case 1: Find All Duplicates in a Sheet

While there are many ways to find all duplicates in Excel, I especially recommend you use Conditional Formatting for this.

This is because, with functions like COUNTIF, you will have a hard time tracking the cells that return TRUE for Duplicates – especially for big datasets.

But, with Conditional formatting, you can quickly identify and highlight all duplicate entries.

Simply, select cells of your Sheet. From your Home Tab, click **Conditional Formatting** > **Highlight Cells Rules** > **Duplicate** **Values**.

On Duplicate Values, pick a **color** and hit **OK**.

## Case 2: Find Duplicates in Column

To know the Duplicates in the same column or different column, we will be using functions for this.

**Example 1: Same Column**

Suppose, you want to check and see the duplicate entries within the same column. To perform this, first, add a helper column.

Then, enter the COUNTIF formula as mentioned in the box. Remember, this formula will also take the first occurrences as duplicate values and mark them as TRUE.

**=COUNTIF(C:C, C3) > 1**

The COUNTIF formula above counts and returns the total number of cells with C2 (Clara) value from range C:C.

As we have specified **greater than 1 criteria **in the end, it returns **TRUE **for duplicates and **FALSE **when there is only a unique item.

In case you do not want to include the 1st occurrences of duplicate values, use this formula.

**=IF(COUNTIF($C$2:$C2, C2) > 1, "Duplicate", "")**

**Example 2: Different Column**

Next, to compare values between columns and identify the duplicates, we will use the UNIQUE and FILTER function nested together. It is the same as merging duplicates in Excel.

**=UNIQUE(FILTER(B4:B10, COUNTIF(B4:B10, C4:C10)>0))**

The COUNTIF formula first checks and returns the total number of repeated values in between both columns. Then, the FILTER function filters out and returns an array of duplicate values.

Finally, the UNIQUE function returns only the unique values from that filtered array.

## Case 3: Find Duplicate Rows

You can also find the duplicate values based on rows in Excel. Firstly, combine all values of the column from a Row using the CONCAT function.

For that, enter ** =CONCAT(A4:C4)**. Then, copy the formula for the remaining cells.

Now that we have values to check the duplicates, we will use Conditional Formatting.

On the Home Tab, navigate to **Conditional Formatting **> **Highlight Cells Rules**. Select **Duplicate Values **and hit **OK**.

Once you’re done, you can hide the merged values from the highlighted column. Select Column and enter **Ctrl **+ **1**. On the Number tab, click **Custom**. Enter ** ;;; **in the Type field and hit

**OK**.

The numbers will be hidden now.

**NOTE:**You’ll have a shade in the cell next to the row with duplicates. To make your navigation more easier, you can highlight active rows.

In case you want to delete duplicate rows based on one column, we have an article guide on how to do it.

## Case 4: Find Case-Sensitive Duplicates

While searching for duplicates, Case-Sensitive is another thing you mustn’t miss out on.

In all the above cases, the formulas/features take both the lowercase (nisha) and Uppercase (NISHA) as duplicates in your spreadsheet.

But, let us consider them differently and find only the case-sensitive values as duplicates. Enter this formula in a new cell.

**=IF(SUM((--EXACT($C$3:$C$11,C3)))<=1,"","Duplicate")**

**NOTE:**If your Office version does not support the array formula, enter

**Ctrl + Shift + Enter**after typing the formula.

The formula returns **“Duplicate”** as a result in the cells where there are lowercase matches. Here, we got duplicates for** “Enrika” **only.

As seen in the picture, the formula ignored duplicate values for **“James”** and **“JAMES.”**

## Case 5: Find Duplicates Across Sheets

So far, we learned how to find duplicates in a single spreadsheet. Now, let us check and highlight the duplicate values across sheets in a workbook.

As an example, I will look for duplicate values in Sheet 6 and Sheet 4. Then, highlight duplicate cells.

- Firstly, on Sheet 6, Select ranges with duplicate values to highlight them.
- In the Home Tab, expand the
**Conditional Formatting**menu >**New Rule**. - Head to
**Use a formula to determine within cells to format**menu and type theformula in the field.`=COUNTIF(Sheet4!$C$3:$C$11,Sheet6!C2)`

- Click
**Format**. Then, select a**Colour**and hit**OK**. - Again, click
**OK**. - The formula will highlight the duplicate values of Sheet 6 that match with Sheet 4.

By now, you must’ve learned all the cases and methods to find duplicates. If you want to delete those duplicates, here’s our next article on “How to de-duplicate in Excel.”