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.


Download Practice Workbook


4 Easy Ways to Find Repeated Cells in Excel

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.

how to find repeated cells in excel


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.

how to find repeated cells in excel

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

how to find repeated cells in excel

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 Repeat Cell Value X Times in Excel (6 Easy Methods)


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")

how to find repeated cells in excel

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.

how to find repeated cells in excel

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

Read More: How to Repeat Cell Values in Excel (6 Quick Methods)


Similar Readings


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.

how to find repeated cells in excel

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 Repeat Rows in Excel Based on Cell Value (4 Easy Ways)


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…

how to find repeated cells in excel

  • A dialog box will appear. Just click OK.

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

how to find repeated cells in excel

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

Read More: How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)


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. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

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

Leave a reply

ExcelDemy
Logo