How to find duplicate values in excel using formula

This article is about how to find duplicate values in excel using formula. Through duplicate function you can easily identify duplicate values whether text or numbers and you can put color style of your choice, so that identification get easily.

If you are wondering how to find duplicate values in excel using formula, you have come to the right place. In this article we will find two types of duplicate values, one is text and the other is numbers.

Find duplicate text values in excel using formula

1. Open the excel document containing text. It has random text in cells

2. Put your cursor on D6.

3. Click and hold left mouse button and drag diagonally till cell F15 to select the entire table.

4. Now click the “Conditional Formatting” option in the Home tab.

5. A drop down menu appears, click “Highlight Cell Rules”. Again drop down list appears having multiple options.

6. Click “Duplicate Values” option.

7. After clicking dialogue box appears. In the left field let it remain as Duplicate because this is what we require. Choose the color Light Red Fill with Dark Red Text or any other color your wish to choose in the right box.

8. Click Ok. The duplicate cells are highlighted as red box and text as dark red color.

Find duplicate number values in excel using formula

1. Open the excel document containing some numbers. This example consists of random number values in cells

2. Bring your cursor to the D6

3. To select entire table click and hold left mouse button and drag diagonally up to cell F11.

4. In the Home tab click the “Conditional Formatting” option.

5. A drop down menu appears having multiple options, go to Highlight Cell Rules then again a drop down list appears.

6. Click Duplicate Values option

7. A dialogue box appears. In the left field let it remain as Duplicate because this is what we need to find. In the right box, select the color Light Red Fill with Dark Red Text or any other color your wish to choose.

8. Click Ok. The duplicate cells containing numbers are highlighted as red box with dark red color

Find duplicate number values in excel using help option

1. Open the excel document which have some random numbers.

2. Go to the D6 cell.

3. Click and hold left mouse button and drag diagonally up to cell F15. This will select the entire table.

4. Now go to the Search bar present at the top of menu bar and type duplicate in it. A drop-down list appear having multiple options.

5. Click on the Duplicate Value option

6. Repeat the same procedure completely in detail and in the end click Ok. All the duplicate numbers are highlighted

So this is how to find duplicate values in excel using formula. Need to edit Word/Excel/PPT file free of charge? download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.