We may not have the course you’re looking for. If you enquire or give us a call on +44 1344 203 999 and speak to our training experts, we may still be able to help with your training requirements.
Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
Most spreadsheet applications, including Excel, offer many options for data analysis, filtering, and transformation. When you work on massive data every day, there may be unidentified duplicates in your data that will make your analysis redundant. During such instances, it is important to know How to Find Duplicates in Excel.
So, wait no more. If you want to sort your data and avoid making mistakes in data entry, you should read this blog. We will give you detailed insights about How to Find Duplicates in Excel.
Table of Contents
1) What are Duplicates in Excel?
2) Formula to Find Duplicates in Excel
3) Five methods to identify and remove Duplicates in Excel
a) Method 1: Conditional formatting
b) Method 2: Conditional formatting (Specific occurrence)
c) Method 3: Change rules (formulas)
d) Method 4: COUNTIF formula
e) Method 5: Remove Duplicates
4) Conclusion
What are Duplicates in Excel?
MS Excel makes it easier to locate and highlight duplicates within a worksheet. A column can be filtered to remove duplicate values. Using the COUNTIF formula is a simple approach to looking for duplicates.
Using this formula – COUNTIF, you can track the total number of duplicates. Additionally, it can count each unique instance of a given duplicate entry. It will take the range and the criteria as two arguments.
Formula to Find Duplicates in Excel
The following is a general formula to determine the accurate, case-sensitive duplicate values:
“IF(SUM((-EXACT(range,uppermost_cell)))<=1,””,”Duplicate”)”
The target cell is compared to the cell range using the EXACT function. The total number of cells is added by the SUM function. The IF function returns "duplicate" if the occurrence is more than 1.
Note 1: The formula should be typed using "Ctrl+Shift+Enter" because it is an array formula.
Note 2: The algorithm will not treat the uppercase word as duplicate if the identical word appears twice in lowercase and once in uppercase.
Five Methods to Identify Duplicates in Excel
Now that you know what Duplicates are, let’s have a look at How to Find Duplicates in Excel. Here's a closer look:
Method 1: Conditional formatting
You can use conditional formatting in Excel 2007 and the later versions. To understand conditional formatting, we are considering the data on the costs involved in using specific office facilities. Additionally noted are the matching dates for purchasing such a facility. Using Conditional Formatting, we will help you track Duplicates in Excel. Let's take the following example:
The following are the steps to use conditional formatting to Find duplicates in Excel:
1) Choose the A1–C13 data range where duplicates should be looked for.
2) Choose "conditional formatting" under "styles" on the “Home” tab. Select "highlight cell rules" from the drop-down box, then click "duplicate values."
3) The window of “duplicate values” appears on the screen. Click on the left side, and select “Duplicate”. Select the required colour in the “values with” drop-down to highlight the duplicate cells. After that, select “Ok.”
4) The duplicate cells can be spot in the following image:
5) To find values that are duplicates, the columns can be filtered. For this, choose "filter by selected cell's colour" from the menu when you right-click the necessary column. Duplicate data are removed from the data.
6) The following image displays the output after applying the filter to the first column (office supplies):
Build your career as a Data Analyst, and learn to prepare tables. Charts, sheets and reports with our Data Analysis Training using MS Excel
Method 2: Conditional formatting (Specific occurrence)
Let’s take an example to track the particular number of occurrences. Let’s find and show the duplicate values using three occurrences in the following table:
Follow the steps below to find the identical values for specific occurrences:
Step 1: Select the cells from A2 to C8 range from the provided data table.
Step 2: Choose "Conditional formatting" under "Styles" on the “home” tab. Tap "new rule."
Step 3: Fill in the details in the new window titled "new formatting rule”, as shown in the image below:
1) Pick the "use a formula to determine which cells to format" option under "select a rule type."
2) Put the COUNTIF formula where it says, "Edit the rule description."
The COUNTIF formula “=COUNTIF (cell range of data table)” finds and highlights the cells for the required number of occurrences. The COUNTIF formula helps in tracking the same cells with a triplicate count. Depending on the user's requirement, the count can be changed to a different number.
Step 4: After entering the COUNTIF formula, select "format." The "format cells" pop-up window appears. Select the "regular" font style.
Step 5: Select a colour from the “fill” tab. The “Fill” tab helps spot the same cells.
Step 6: After you have selected the “format cells”, click “Ok” twice in the “new formatting rule” window.
Step 7: Check the result displayed in the following image. The Duplicate cells with three occurrences are shown.
Get expertise in Data Visualisation with our Microsoft SQL Server Training.
Method 3: Change rules (formulas)
Now, let’s talk about the process of changing the formula. First, the existing formulas in the data set must be removed to test new formulas. The steps to remove the current data are mentioned below:
Step 1: Select “conditional formatting” from the styles section”.
Step 2: Choose one of the options under "clear rules":
1) Clear rules from selected cells—By clicking this button, the rules for the table's selected range are reset. Therefore, the data must be chosen before the rules may be cleared.
2) Remove all rules from the sheet—This removes all rules from the sheet.
Method 4: COUNTIF formula
The names of a few countries are shown in the following table. Using the COUNTIF function, you can find the Duplicate values.
The COUNTIF function can be used to locate duplicate data in Excel by following the procedures mentioned below:
Step 1: The following image will take you to the first step of entering the formula. Press the “Enter” key.
Step 2: Using the fill handle, drag the formula all the way to the end of the table. The fill handle can also be selected by double-clicking with the cursor in cell B2. Cell B2's lower right corner features the fill handle.
Step 3: You will be directed towards the final count of duplicates for the entire set.
Method 5: Remove Duplicates
Let's analyse and remove duplicate values from the range we've chosen. The duplicates will be permanently deleted; hence it is advised to keep a copy of the table before deletion.
A list of items and their related IDs are shown in the following table:
The following is a list of the steps to identify and remove duplicate values:
Step 1: Choose the area of the table from which duplicates must be removed.
Step 2: Click "remove duplicates" in the "data tools" section of the Data tab.
Step 3: The “remove duplicates” window appears. The options mentioned below are already been selected:
1) The options for the "Order ID" and "items" headings
2) Use the "select all" box
Step 4: The results are shown in the image included below with the following details:
1) how many duplicate values were deleted from the table
2) how many different values are still present in the table after the removal
Conclusion
We hope this blog on How to Find Duplicates in Excel assisted you in preventing data duplication in one or more columns of your Excel worksheet. So, begin your Excel journey today and master the art of Data Analysis.
Learn how to create advanced formulas, macros and much more in our MS Excel training – sign up for Microsoft Excel Expert Training now!
Frequently Asked Questions
Mastering duplicate handling in Excel improves your ability to clean and organise data efficiently, saving time and reducing errors in Data Analysis. By understanding how to identify and remove Duplicates, you demonstrate proficiency in Data Management. This makes you an indispensable asset in any workplace.
Excel's deduplication features allow you to streamline data sets, ensuring that only unique information is included in your presentations. This enhances the clarity and professionalism of your presentations, impressing colleagues and stakeholders with your attention to detail and polished deliverables.
The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide.
Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.
The Knowledge Academy offers Microsoft Excel Courses including Microsoft Excell, PowerPoint, Project Training. These courses cater to different skill levels, providing comprehensive insights into Minitab vs Excel.
Our Office Application Blogs covers a range of topics related to Project Management Businesses, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Microsoft Excel skills, The Knowledge Academy's diverse courses and informative blogs have you covered.
Upcoming Office Applications Resources Batches & Dates
Date
Fri 26th Jul 2024
Fri 20th Sep 2024
Fri 15th Nov 2024
Fri 20th Dec 2024