# How to Find Repeated Cells in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to find repeated cells in Excel. Sometimes, there can be a lot of repeated cells in your dataset, and finding them one by one would be an itchy task to do. Fortunately, we can use some pretty easy and effective formulas to find them and delete them if needed. The article will pave the way for you to show how to use those formulas to find repeated cells in an Excel worksheet.

## How to Find Repeated Cells in Excel: 4 Easy Ways

In the dataset, we have information about some peopleâ€™s purchased items from a shop and their prices. Some cells contain repeated data which we will find out in the later section of this article in various methods.

### 1. Applying Excel Conditional Formatting to Find Repeated Cells

You can see there are some repeated names of the customers in the dataset. We can use Conditional Formatting to find out those repeated cells. Letâ€™s go through the process below.

Steps:

• First, select the range B5:D14 and then go to Home >> Conditional Formatting >> New Rule.

• The New Formatting Rule window will show up. After that, select â€˜Use a formula to determine which cells to formatâ€™.
• Next, type the following formula in the â€˜Format values where this formula is trueâ€™ section.

`=COUNTIF(\$B\$5:\$D\$14, B5)>1`

• Thereafter, click on Format.

• After that, the Format Cells window will show up. Select Fill and choose a color.
• Next, click OK.

• The New Formatting Rule window will appear again. Just click OK.

After executing this command, you will see all the repeated cells are filled with the selected color.

Thus, you can find repeated cells in Excel using the Conditional Formatting application.

Read More: How to Find Repeated Numbers in Excel

### 2. Using IF and COUNTIF Functions to Find Repeated Cells

You can also find repeated cells by combining the IF and COUNTIF functions. We are going to find whether the name of a customer appears more than once in the Name column. Letâ€™s go through the procedure below.

Steps:

• First, make a new column to identify whether a repeat occurs and type the following formula in cell E5.

`=IF(COUNTIF(\$B\$5:\$B\$14,B5)>1,"Repeated","No Repeat")`

The formula here returns Repeated if the name in B5 is found more than once in the B5:B14 range and No Repeat if the name is found only once. The COUNTIF function counts the number of times these names appear in the range and the IF function tests whether that number is greater than 1. If the logical_test of the IF function is TRUE, then it will return Repeated, otherwise it will return No Repeat.

• After that, press the ENTER button and you will see if the name in B5 occurs more than once or not.

Finally, you will be able to find the repeated cells by using IF and COUNTIF functions.

Read More: How to Filter Duplicates in Excel

### 3. Implementing Excel TEXTJOIN Function for Repeated Cells of Rows

Another function you can apply to find repeated cells is the TEXTJOIN function. Suppose your data contains repeated cells in a row. Letâ€™s go through the following section of this article to find out those cells.

Steps:

• First, make a column to store the data of the cells in a row as a complete package and type the following formula in E5, press ENTER and drag the Fill icon downwards to AutoFill the lower cells.

`=TEXTJOIN(", ",TRUE,B5:D5)`

• After that, select the range B5:E14, open the New Formatting Rule window as we did in Method 1, and type the following formula in the â€˜Format values where this formula is trueâ€™ section. Also, select a Format Cell color of your choice.

`=COUNTIF(\$E\$5:\$E\$14,\$E5)>1`

• Next, click OK.

Finally, the repeated rows will be highlighted after the execution of this command.

Thus you can find repeated cells by using the TEXTJOIN function.

### 4. Finding Repeated Cells Without Formulas

If you are tired of using formulas, you can simply use the following technique.

Steps:

• First, select the range B5:B14 and then go to Home >> Conditional Formatting >> Highlight Cell Rules >> Duplicate Valuesâ€¦

• A dialog box will appear. Just click OK.

• Next, you will see that this operation highlights the repeated cells in the range B5:B14.

Thus, you can find repeated cells in Excel without using any formula.

## Practice Section

Here, Iâ€™m giving you the dataset of this article so that you can practice these methods on your own.

## Conclusion

Suffice to say, you can learn some basic ideas about how to find repeated cells in Excel after reading this article. If you have any suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF