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

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.

• 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.

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.

• Next, use the Fill Handle to AutoFill the lower cells.

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.

• 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.

• 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.

Read More: How to Compare Rows for Duplicates in Excel

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.

Read More: Excel Find Duplicate Rows Based on Multiple Columns

Practice Section

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

Download Practice Workbook

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.

Related Articles

• How to Compare Two Excel Sheets for Duplicates
• How to Find Matching Values in Two Worksheets in Excel
• How to Find Duplicates in Excel and Copy to Another Sheet
• Excel VBA to Find Duplicate Values in Range
• How to Find Duplicates in a Column Using Excel VBA
• How to Use VBA Code to Find Duplicate Rows in Excel

<< Go Back to Find Duplicates in Excel | Duplicates in Excel | Learn Excel