In my example below I have 27 rows of data, with names in the range A2 to A28.
two, three or four occurrences of the same piece of data, we need another approach: Conditional Formatting with a formula.įirst, let’s find out how to count the number of occurences in a list. But sometimes we want to make it more dynamic: If we want to be able to choose between highlightning duplicates, triplicates or quadruplicates, i.e. If you only want to locate duplicates, the super-easy way above is the right way to do it. That’s why using Excel templates and following these simple instructions on formulas can help you level up your game.Select the cells you want to check, go to the Home Ribbon, choose Conditional Formatting and select Highlight Cell Rules > Duplicate Values. Working on marketing reports or any marketing Excel sheet can leave you frustrated and banging your head against the wall (is it just me?). You can highlight the whole workbook or you can just highlight the rows that have data in it.Įxcel will automatically keep the first occurrence of the value. When you remove duplicates from Excel, it’s important to note which column you want to remove duplicates from and remember that Excel is removing duplicates within a selected table range.
This is what the sheet used to look like: Comparing my first dataset to this dataset, you can see that all the rows containing the same blog title were deleted. Remember that Excel will remove the entire row where the duplicate value is.Įxcel will now show you have many duplicate values were found, removed, and how many unique values remain. I kept “My list has headers” checked because there are two rows of headers before the data starts on this sheet. In this case, I only want to remove duplicate blog titles.
Then, create another column for “occurrences.” This is where we’ll put the formula. In the example below, I copied and pasted the blog titles from the editorial calendar to see if there were any duplicate titles. Then, copy and paste the column that you want to count duplicates in. I found that the easiest way to count duplicates in Excel is to create a new sheet in your Excel workbook. Then, A2 references the value you want to count the frequency of. This will probably be a different value on your Excel sheet. The column A:A stands for the data table you’re looking at. The formula means that Excel is going to count how many times a certain value is used in a certain place. To do this, you can use this formula =COUNTIF(A:A, A2). Now that you’ve found your duplicates, you might want to count them and see how many there are, especially if you have a large dataset. You can choose highlighting, bolding text, changing the color of text, etc.Īnd voila. In the area titled “Format With”, change how you want duplicates to be highlighted.Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.Select the entire table by hitting the button on the top left.You can do that by following these steps: An easy way to do this is through conditional formatting. The first step of removing duplicates will be to find them. Find and highlight duplicates in Excel through conditional formatting. Remove duplicates with the remove duplicates feature.ġ.Find and highlight duplicates in Excel through conditional formatting.