“I was never born, all I do is try, try, try.”
These Taylor Swift lyrics are in the song “Mirror ball“, perfectly explained my relationship with numbers, mathematics, and anything related to data analysis.
However, as a marketer, data analysis is one of the most important aspects of my work. But like most marketers who like strategy and creativity, numbers and Excel reports are not natural to me.
Therefore, it is important to understand how to work in Excel and find shortcuts that help simplify the process.
Today, we will delve into one of these processes-how to find and delete duplicates in Excel.
How to delete duplicates in Excel
- Find and highlight duplicates in Excel with conditional formatting.
- Count duplicates in Excel.
- Use the delete duplicates function to delete duplicates.
1. Find and highlight duplicates in Excel through conditional formatting.
The first step in removing duplicates is to find them. An easy way is through conditional formatting.
You can follow the steps below:
- Make sure you are on the “Home” tab.
- Click the button in the upper left corner to select the entire table.
- Click Conditional Formatting→Highlight Cell Rule→Repeat Value.
- In the area titled “format”To change how you want duplicate items to be highlighted. You can choose to highlight, bold text, change text color, etc.
Look. Your duplicates are now highlighted. It should look like this:
2. Calculate duplicates in Excel.
Now that you have found duplicates, you may want to count them and see how many there are, especially if you have a large data set.
To do this, you can use this formula =COUNTIF(A:A, A2)This formula means that Excel will calculate how many times a value has been used in a certain place.
Column Answer: A Represents the data sheet you are viewing. This may be a different value on the Excel worksheet. then, A2 Refer to the value of the frequency to be calculated.
To do this, follow these steps:
- Create a new worksheet in the Excel document.
I found that the easiest way to count duplicates in Excel is to create a new worksheet in the Excel workbook.
Then, copy and paste the column where you want to count duplicates. In the example below, I copied and pasted the blog title from the editorial calendar to see if there are any duplicate titles.
Then, create another column for “Occurrences.” This is where we will put the formula. Your new worksheet should look like this:
- Insert the formula.
Now you can insert the formula in the first cell under the number of occurrences. You will enter or copy and paste the formula. Then highlight A:A (we will replace it with your data set), and click on the worksheet that contains the data in the Excel document. Now you can click the upper left corner to select the entire worksheet, or you can highlight only the column or row that contains the data.
For the second value, you need to go back to the second worksheet, highlight A2, and select the value next to the left. In most cases, this will keep A2, A3, A4, etc.
Check the actual effect here:
3. Use the delete duplicates function to delete duplicates.
Now it’s time to remove duplicates from the data set.
Before you do this, I suggest you copy/copy the data set to another worksheet or the entire other workbook. Even if you can use Excel to delete and filter the data you need, you always want to keep the original data unchanged. You don’t want to lose data due to wrong clicks.
After copying the data, you can delete the duplicates.
To delete duplicates, follow these steps:
- Select the worksheet with the duplicate values you want to delete. Click Data → Table Tools → Delete Duplicates.
- Select the column you want to delete duplicates.
In this case, I just want to delete the duplicate blog title. So I will choose column D. I always check “My list has titles” because there are two rows of titles before the data on this worksheet starts.
Remember that Excel will delete the entire row where the duplicate value is located.
- View the data.
Excel will now show you how many duplicate values have been found, deleted, and how many unique values have been kept.
Now you can view your data. Comparing my first dataset with this dataset, you can see that all rows containing the same blog title have been deleted.
This is what the previous table looked like:
This is how it looks now:
When deleting duplicates from Excel, be sure to pay attention to the columns from which you want to delete duplicates, and remember that Excel is deleting duplicates in the selected table range. You can highlight the entire workbook, or you can highlight the rows that contain data.
Excel will automatically retain the first occurrence of the value.
Dealing with marketing reports or any marketing Excel worksheets will make you feel frustrated and bump your head against the wall (is it just me?). This is why using an Excel template and following these simple formulas can help you improve your game.