{"id":6497,"date":"2023-05-19T19:47:39","date_gmt":"2023-05-19T19:47:39","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=6497"},"modified":"2024-05-10T09:45:19","modified_gmt":"2024-05-10T09:45:19","slug":"how-to-find-duplicates-value-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-find-duplicates-value-in-excel\/","title":{"rendered":"How to find Duplicates Value in Excel?"},"content":{"rendered":"<p>Duplicates Value in Excel refers to the presence of identical data entries within a dataset, which can often lead to inaccuracies or inefficiencies in data analysis. By identifying and removing duplicate values, users can ensure data integrity and accuracy in their spreadsheets. Excel offers various tools and functions to detect and manage duplicate values, such as Conditional Formatting, Remove Duplicates, and COUNTIF function. Removing duplicate values streamlines data analysis processes, enhances decision-making accuracy, and improves overall data quality. Utilizing these features enables users to efficiently manage and manipulate data, resulting in more reliable and meaningful insights.<\/p>\n<p><strong>This Tutorial Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#how-to-find-duplicate-values-in-excel\"><strong>How to find duplicate values in Excel<\/strong><\/a>\n<ul>\n<li><strong>How to identify duplicate records with 1st occurrences<\/strong><\/li>\n<li><strong>How to identify duplicate records without 1st occurrences<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-make-excel-highlight-duplicates\"><strong>How to make Excel highlight duplicates<\/strong><\/a>\n<ul>\n<li><strong>How to highlight duplicate records without the first occurrence<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-search-for-duplicate-rows-in-excel\"><strong>How to search for duplicate rows in Excel<\/strong><\/a><\/li>\n<li><a href=\"#how-to-calculate-duplicates-in-excel\"><strong>How to calculate duplicates in Excel<\/strong><\/a>\n<ul>\n<li><strong>Count each occurrence of a duplicate record separately<\/strong><\/li>\n<li><strong>Determine the overall number of duplicates in a column.(s)<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-use-excel-to-filter-duplicates\"><strong>How to use Excel to filter duplicates<\/strong><\/a>\n<ul>\n<li><strong>How to display and hide duplicates in Excel<\/strong><\/li>\n<li><strong>How to filter duplicates based on how often they appear<\/strong><\/li>\n<li><strong>How to delete duplicates from Excel<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2><strong id=\"how-to-find-duplicate-values-in-excel\">1. How to find duplicate values in Excel?<\/strong><\/h2>\n<p>The COUNTIF function in Excel is the simplest tool for finding duplicates. The formula will differ slightly depending on whether you wish to discover duplicate values with or without initial occurrences, as demonstrated in the following instances.<\/p>\n<ul>\n<li>\n<h3><strong>How to identify duplicate records with 1st occurrences?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>One scenario is given where you have a dataset, and you need to identify if there are any duplicate values present in column B.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-6498 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-1.png\" alt=\"Duplicates Value in Excel\" width=\"370\" height=\"191\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-1.png 370w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-1-300x155.png 300w\" sizes=\"(max-width: 370px) 100vw, 370px\" \/><\/p>\n<p><strong>The steps to identify duplicate records with 1st occurrences are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> To duplicate the formula down to other cells, enter the following formula in E2, then choose E2, then drag the fill handle:<\/p>\n<p><strong>=COUNTIF(B:B, B2)&gt;1<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-6499 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-2.png\" alt=\"Duplicates Value in Excel\" width=\"397\" height=\"221\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-2.png 397w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-2-300x167.png 300w\" sizes=\"(max-width: 397px) 100vw, 397px\" \/><\/p>\n<p>The formula returns TRUE for duplicate values and FALSE for unique values, as shown in the screenshot above.<\/p>\n<p><strong>Note:<\/strong> Remember to lock that range with the $ sign if you need to search for duplicates within a set of cells rather than throughout a whole column. Use this formula, for instance, to look for duplicates in cells B2 through B8:<\/p>\n<p><strong>=COUNTIF($B$2:$B$8, B2)&gt;1<\/strong><\/p>\n<p>Put a duplicate formula inside the IF function and type any labels you like for duplicate and unique values to get it to produce something other than the Boolean values TRUE and FALSE:<\/p>\n<p><strong>=IF(COUNTIF($B$2:$B$8, $B2)&gt;1, &#8220;Duplicate&#8221;, &#8220;Unique&#8221;)<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-6500 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-3.png\" alt=\"Duplicates Value in Excel\" width=\"499\" height=\"197\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-3.png 499w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-3-300x118.png 300w\" sizes=\"(max-width: 499px) 100vw, 499px\" \/><\/p>\n<p>Replace &#8220;Unique&#8221; with an empty string (&#8220;&#8221;) if you want an Excel formula to just discover duplicates:<\/p>\n<p><strong>=IF(COUNTIF($B$2:$B$8, B2)&gt;1, &#8220;Duplicate&#8221;, &#8220;&#8221;)<\/strong><\/p>\n<p>The formula will return &#8220;Duplicates&#8221; for records that have duplicates, and a blank cell for records that have unique records:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6501 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-4.png\" alt=\"Duplicates Value in Excel\" width=\"544\" height=\"209\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-4.png 544w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-4-300x115.png 300w\" sizes=\"(max-width: 544px) 100vw, 544px\" \/><\/p>\n<ul>\n<li>\n<h3><strong>How to identify duplicate records without 1st occurrences?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Using the formula\u00a0above is risky because it flags all identical entries as duplicates, which is undesirable if you intend to filter or delete duplicates after detecting them. Additionally, you can only get rid of the second and any succeeding instances of a duplicate record if you want to maintain the list&#8217;s unique values.<\/p>\n<p><strong>The steps to identify duplicate records without 1st occurrences are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> To duplicate the formula down to other cells, enter the following formula in E2, then choose E2, then drag the fill handle:<\/p>\n<p><strong>=IF(COUNTIF($B$2:$B2, $B2)&gt;1, &#8220;Duplicate&#8221;, &#8220;&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6502 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-5.png\" alt=\"Duplicates Value in Excel\" width=\"516\" height=\"219\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-5.png 516w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-5-300x127.png 300w\" sizes=\"(max-width: 516px) 100vw, 516px\" \/><\/p>\n<p>This formula does not identify the first instance of &#8220;John Smith&#8221; as a duplicate, as shown in the screenshot above.<\/p>\n<h2><strong id=\"how-to-make-excel-highlight-duplicates\">2. How to make Excel highlight duplicates?<\/strong><\/h2>\n<p>There is a predefined rule for highlighting duplicate cells in every version of Excel.<\/p>\n<p><strong>Follow these steps to highlight duplicate in your worksheets:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the data you prefer to examine for duplications.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6503 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-6.png\" alt=\"Duplicates Value in Excel\" width=\"439\" height=\"202\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-6.png 439w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-6-300x138.png 300w\" sizes=\"(max-width: 439px) 100vw, 439px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Click &#8220;Conditional Formatting&#8221; under the &#8220;Styles&#8221; category on the &#8220;Home&#8221; tab, then choose &#8220;Highlight Cells Rules,&#8221; and finally click &#8220;Duplicate Values&#8230;&#8221;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6504 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-7.png\" alt=\"Duplicates Value in Excel\" width=\"639\" height=\"275\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-7.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-7-300x129.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 3:<\/strong> The default settings for the &#8220;Duplicate Values&#8221; dialog window&#8217;s fill and text colors are Light Red Fill and Dark Red, respectively. Just click OK to use the default formatting.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6505 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-8.png\" alt=\"Duplicates Value in Excel\" width=\"378\" height=\"142\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-8.png 378w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-8-300x113.png 300w\" sizes=\"(max-width: 378px) 100vw, 378px\" \/><\/p>\n<p>After clicking OK, the result looks like\u00a0 below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6506 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-9.png\" alt=\"Duplicates Value in Excel\" width=\"347\" height=\"222\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-9.png 347w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-9-300x192.png 300w\" sizes=\"(max-width: 347px) 100vw, 347px\" \/><\/p>\n<p>There are a few other predetermined formats in the dropdown list in addition to the red fill and text formatting. Click Custom Format&#8230; (the last option in the drop-down) and choose your preferred fill and\/or font color to shade duplicates with a different color.<\/p>\n<ul>\n<li>\n<h3><strong>How to highlight duplicate records without the first occurrence?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p><strong>Following are the procedures to emphasize the second and all subsequent duplicate occurrences:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the data you prefer to examine for duplications.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6507 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-10.png\" alt=\"Duplicates Value in Excel\" width=\"439\" height=\"201\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-10.png 439w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-10-300x137.png 300w\" sizes=\"(max-width: 439px) 100vw, 439px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Click &#8220;New rule&#8221; after selecting &#8220;Conditional Formatting&#8221; from the &#8220;Styles&#8221; group under the &#8220;Home&#8221; menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6508 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-11.png\" alt=\"Duplicates Value in Excel\" width=\"638\" height=\"301\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-11.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-11-300x142.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Choose the &#8220;Use a formula to determine which cells to format&#8221; option from the &#8220;New Formatting Rule&#8221; window that has just shown.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6509 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-12.png\" alt=\"Duplicates Value in Excel\" width=\"408\" height=\"291\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-12.png 408w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-12-300x214.png 300w\" sizes=\"(max-width: 408px) 100vw, 408px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Enter the following formula in the area marked Format values where this formula is true:<\/p>\n<p><strong>=COUNTIF($B$2:$B2,$B2)&gt;1<\/strong><\/p>\n<p>Select the fill and\/or font color you desire by clicking the &#8220;Format&#8230;&#8221; button.<\/p>\n<p>To save and apply the rule, click OK at the very end.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6510 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-13.png\" alt=\"Duplicates Value in Excel\" width=\"429\" height=\"310\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-13.png 429w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-13-300x217.png 300w\" sizes=\"(max-width: 429px) 100vw, 429px\" \/><\/p>\n<p>The duplicate cells, with the exception of first instances, will thereafter be highlighted in the color of your choice:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6511 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-14.png\" alt=\"Duplicates Value in Excel\" width=\"358\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-14.png 358w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-14-300x178.png 300w\" sizes=\"(max-width: 358px) 100vw, 358px\" \/><\/p>\n<h2><strong id=\"how-to-search-for-duplicate-rows-in-excel\">3. How to search for duplicate rows in Excel?<\/strong><\/h2>\n<p>If your goal is to dedupe a table with multiple columns, you&#8217;ll need a formula that can examine each column and only discover absolute duplicate rows, or rows with exactly the same values in every column.<\/p>\n<p>Let&#8217;s think about the following dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6512 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-15.png\" alt=\"Duplicates Value in Excel\" width=\"327\" height=\"213\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-15.png 327w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-15-300x195.png 300w\" sizes=\"(max-width: 327px) 100vw, 327px\" \/><\/p>\n<p><strong>The steps to search for duplicate rows with 1st occurrences in Excel are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> To duplicate the formula down to other cells, enter the following formula in E2, then choose E2, then drag the fill handle:<\/p>\n<p><strong>=IF(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)&gt;1, &#8220;Duplicate row&#8221;, &#8220;&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6513 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-16.png\" alt=\"Duplicates Value in Excel\" width=\"639\" height=\"199\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-16.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-16-300x93.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p>The screenshot that follows shows that the formula\u00a0actually only finds rows with identical values across all three columns. For instance, row 6 is not considered a duplicate row because it includes a distinct ID\u00a0in column C.<\/p>\n<p>If you want to display duplicate rows without first occurrences, make the following small change to the formula:<\/p>\n<p><strong>=IF(COUNTIFS($A$2:$A2,A2, $B$2:$B2,B2,$C$2:$C2,C2) &gt;1, &#8220;Duplicate row&#8221;, &#8220;&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6514 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-17.png\" alt=\"Duplicates Value in Excel\" width=\"638\" height=\"198\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-17.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-17-300x93.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<h2><strong id=\"how-to-calculate-duplicates-in-excel\">4. How to calculate duplicates in Excel?<\/strong><\/h2>\n<p>To determine the precise number of identical records present in your Excel spreadsheet, you can utilize one of the formulas provided below to count the occurrences of duplicates.<\/p>\n<ul>\n<li>\n<h3><strong>Count each occurrence of a duplicate record separately:<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>You may frequently need to know how many duplicates there are for each value in a column of duplicated data.<\/p>\n<p>Use the COUNTIF formula to calculate the frequency of each element in your Excel worksheet.<\/p>\n<p><strong>The steps to count each occurrence of a duplicate record separately are described below:<\/strong><\/p>\n<p>Step 1: To duplicate the formula down to other cells, enter the following formula in C2, then choose C2, then drag the fill handle:<\/p>\n<p><strong>=COUNTIF($A$2:$A$9, $A2)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6515 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-18.png\" alt=\"Duplicates Value in Excel\" width=\"488\" height=\"270\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-18.png 488w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-18-300x166.png 300w\" sizes=\"(max-width: 488px) 100vw, 488px\" \/><\/p>\n<p>As demonstrated in the above screenshot, the formula counts the occurrences of each name: &#8220;John &#8221; occurs 3 times, &#8220;Jane &#8221; &#8211; 2 times, &#8220;Michael &#8221; . &#8220;Samantha &#8221; and &#8220;Emily &#8221; only once.<\/p>\n<p>Use the following formula to determine the first, second, third, etc. occurrences of each item:<\/p>\n<p><strong>=COUNTIF($A$2:$A2, $A2)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6516 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-19.png\" alt=\"Duplicates Value in Excel\" width=\"479\" height=\"271\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-19.png 479w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-19-300x170.png 300w\" sizes=\"(max-width: 479px) 100vw, 479px\" \/><\/p>\n<p>You can also count the instances of duplicate rows in a similar way. The main difference is that COUNTIF will no longer work; you must use COUNTIFS instead.<\/p>\n<p>=COUNTIFS($A$2:$A$9, $A2, $B$2:$B$9, $B2)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6517 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-20.png\" alt=\"Duplicates Value in Excel\" width=\"491\" height=\"229\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-20.png 491w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-20-300x140.png 300w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/p>\n<p>You can hide unique values and only see duplicates after the duplicate values have been counted, or you can do the opposite. Apply Excel&#8217;s auto-filter to achieve this.<\/p>\n<ul>\n<li>\n<h3><strong>Determine the overall number of duplicates in a column.(s):<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Using any of the formulae we used to find duplicates in Excel (with or without first occurrences) is the simplest approach to count duplicates in a column. Using the COUNTIF algorithm below, you can then determine the number of duplicate values:<\/p>\n<p><strong>=COUNTIF(range, &#8220;duplicate&#8221;)<\/strong><\/p>\n<p>Where &#8220;duplicate&#8221; is the term you used in the duplicate-finding formula.<\/p>\n<p>In this illustration, our duplication formula looks like this:<\/p>\n<p><strong>=COUNTIF(C2:C9, &#8220;Duplicate&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6518 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-21.png\" alt=\"Duplicates Value in Excel\" width=\"516\" height=\"235\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-21.png 516w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-21-300x137.png 300w\" sizes=\"(max-width: 516px) 100vw, 516px\" \/><\/p>\n<p>Use a more complicated array formula to count duplicate values in Excel. This method has the benefit of not requiring a helper column:<\/p>\n<p><strong>=ROWS($A$2:$A$9)-SUM(IF( COUNTIF($A$2:$A$9,$A$2:$A$9)=1,1,0))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6519 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-22.png\" alt=\"Duplicates Value in Excel\" width=\"592\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-22.png 592w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-22-300x107.png 300w\" sizes=\"(max-width: 592px) 100vw, 592px\" \/><\/p>\n<p><strong>Note:<\/strong> Remember to enter the formula by pressing Ctrl + Shift + Enter because it is an array formula. Please be aware that this calculation includes initial occurrences when counting duplicate records.<\/p>\n<p>To determine the count of duplicate rows in a dataset, replace the COUNTIF function with the COUNTIFS function and include the columns that need to be checked for duplicates in the formula.<\/p>\n<p><strong>=ROWS($A$2:$A$9)-SUM(IF( COUNTIFS($A$2:$A$9,$A$2:$A$9, $B$2:$B$9,$B$2:$B$9)=1,1,0))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6520 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-23.png\" alt=\"Duplicates Value in Excel\" width=\"640\" height=\"202\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-23.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-23-300x95.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<h2><strong id=\"how-to-use-excel-to-filter-duplicates\">5. How to use Excel to filter duplicates?<\/strong><\/h2>\n<p>If you want to simplify your data analysis, you can apply filters to display only duplicate records. Alternatively, if you need to focus on unique entries, you can hide the duplicates and view only the distinct records. Here are solutions for both situations.<\/p>\n<ul>\n<li>\n<h3><strong>How to display and hide duplicates in Excel?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>To easily identify all duplicates in your Excel table, choose an appropriate formula from the available options.<\/p>\n<p><strong>The steps to display and hide duplicates in Excel are described below:<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Select the dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6521 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-24.png\" alt=\"Duplicates Value in Excel\" width=\"472\" height=\"220\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-24.png 472w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-24-300x140.png 300w\" sizes=\"(max-width: 472px) 100vw, 472px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Go to the \u201cData\u201d tab and click on the \u201cFilter\u201d button.<\/p>\n<p>Another option is to go to the \u201cHome\u201d tab, select \u201cSort &amp; Filter\u201d, and then click on \u201cFilter\u201d in the \u201cEditing\u201d group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6522 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-25.png\" alt=\"Duplicates Value in Excel\" width=\"558\" height=\"295\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-25.png 558w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-25-300x159.png 300w\" sizes=\"(max-width: 558px) 100vw, 558px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Next, click on the filtering arrow located in the header of the Duplicate column, and mark the checkbox for &#8220;Duplicate&#8221; to display all duplicates. Select OK.<\/p>\n<p>Conversely, to hide the duplicates, select &#8220;Unique&#8221; to view only the unique records.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6524 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-26.png\" alt=\"Duplicates Value in Excel\" width=\"284\" height=\"449\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-26.png 284w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-26-190x300.png 190w\" sizes=\"(max-width: 284px) 100vw, 284px\" \/><\/p>\n<p>The result looks like below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6525 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-27.png\" alt=\"Duplicates Value in Excel\" width=\"344\" height=\"145\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-27.png 344w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-27-300x126.png 300w\" sizes=\"(max-width: 344px) 100vw, 344px\" \/><\/p>\n<p>Conversely, to hide the duplicates, select &#8220;Unique&#8221; to view only the unique records.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6526 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-28.png\" alt=\"Duplicates Value in Excel\" width=\"269\" height=\"421\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-28.png 269w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-28-192x300.png 192w\" sizes=\"(max-width: 269px) 100vw, 269px\" \/><\/p>\n<p>The result looks like below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6527 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-29.png\" alt=\"Duplicates Value in Excel\" width=\"327\" height=\"179\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-29.png 327w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-29-300x164.png 300w\" sizes=\"(max-width: 327px) 100vw, 327px\" \/><\/p>\n<p><strong>Note:<\/strong> A helpful tip to activate filtering automatically is to transform your data into a fully functional Excel table. To do this, highlight all the data and use the Ctrl + T keyboard shortcut.<\/p>\n<ul>\n<li>\n<h3><strong>How to filter duplicates based on how often they appear?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>If you want to view the 2nd, 3rd, or any specific instance of duplicate values, you can use the formula mentioned earlier to count the occurrences of duplicates.<\/p>\n<p>Once you have applied the formula, filter your table, and choose the particular occurrence(s) you wish to view. For instance, to display the 3rd occurrence, you can apply a filter as shown in the provided screenshot.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6528 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-30.png\" alt=\"Duplicates Value in Excel\" width=\"572\" height=\"154\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-30.png 572w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-30-300x81.png 300w\" sizes=\"(max-width: 572px) 100vw, 572px\" \/><\/p>\n<p>Click the filter arrow in the header of the \u201cOccurrences\u201d column (the formula column) and then select Number Filters &gt; Greater Than to display all duplicate data, that is, occurrences greater than 1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6529 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-31.png\" alt=\"Duplicates Value in Excel\" width=\"345\" height=\"375\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-31.png 345w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-31-276x300.png 276w\" sizes=\"(max-width: 345px) 100vw, 345px\" \/><\/p>\n<p>Click the OK button after selecting &#8220;is greater than&#8221; in the first box and entering 1 in the box next to it:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6530 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-32.png\" alt=\"Duplicates Value in Excel\" width=\"470\" height=\"192\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-32.png 470w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-32-300x123.png 300w\" sizes=\"(max-width: 470px) 100vw, 470px\" \/><\/p>\n<p>The result looks like below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6531 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-33.png\" alt=\"DDuplicates Value in Excel\" width=\"298\" height=\"170\" \/><\/p>\n<p>Similarly, you can display the 2nd, 3rd, or any subsequent duplicate occurrence by typing the desired number in the box next to &#8220;is greater than&#8221;.<\/p>\n<ul>\n<li>\n<h3><strong>How to delete duplicates from Excel?<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Excel duplicates can be removed by selecting them, doing a right-click, and selecting \u201cClear Contents\u201d (or by selecting Clear &gt; Clear Contents from the Home tab&#8217;s Editing group). You will end up with empty cells because this will just erase the contents of the cells. The same result can be obtained by selecting the filtered duplicate cells and pressing the Delete key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6532 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-34.png\" alt=\"Duplicates Value in Excel\" width=\"538\" height=\"301\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-34.png 538w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-34-300x168.png 300w\" sizes=\"(max-width: 538px) 100vw, 538px\" \/><\/p>\n<p>Filter duplicate rows, drag the mouse over the row headings to pick the rows, right-click the selection, and then select \u201cDelete Row\u201d from the context menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-6533 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-35.png\" alt=\"Duplicates Value in Excel\" width=\"639\" height=\"194\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-35.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/05\/Duplicates-Value-35-300x91.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<h2><span class=\"x3jgonx\">Application of <\/span>Duplicates Value in Excel<\/h2>\n<ul>\n<li><strong>Data Cleaning:<\/strong> Identifying and removing duplicate values is crucial for cleaning datasets, ensuring data accuracy, and maintaining integrity.<\/li>\n<li><strong>Data Validation:<\/strong> Detecting duplicates helps in validating data input, preventing errors, and maintaining consistency in datasets.<\/li>\n<li><strong>Data Analysis:<\/strong> Eliminating duplicates before analysis ensures that each data point is considered only once, leading to accurate insights.<\/li>\n<li><strong>Conditional Formatting:<\/strong> Applying conditional formatting to highlight duplicate values visually aids in quick identification and analysis.<\/li>\n<li><strong>Database Management:<\/strong> Removing duplicate entries in databases improves efficiency and prevents redundancy in data storage.<\/li>\n<li><strong>Error Prevention:<\/strong> Eliminating duplicate values reduces the risk of errors in calculations, summaries, and reports based on the data.<\/li>\n<\/ul>\n<p>For ready-to-use Dashboard Templates:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Duplicates Value in Excel refers to the presence of identical data entries within a dataset, which can often lead to inaccuracies or inefficiencies in data analysis. By identifying and removing duplicate values, users can ensure data integrity and accuracy in their spreadsheets. Excel offers various tools and functions to detect and manage duplicate values, such &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-find-duplicates-value-in-excel\/\"> <span class=\"screen-reader-text\">How to find Duplicates Value in Excel?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[1],"tags":[737],"class_list":["post-6497","post","type-post","status-publish","format-standard","hentry","category-general","tag-duplicates-value-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6497","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=6497"}],"version-history":[{"count":4,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6497\/revisions"}],"predecessor-version":[{"id":9192,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/6497\/revisions\/9192"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=6497"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=6497"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=6497"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}