Enter the following details, as shown in the succeeding image. Step 3: The pop-up window titled “new formatting rule” appears. Note: The “new rule” option helps highlight a specific count of duplicates using the COUNTIF formula. Step 2: In the Home tab, select “conditional formatting” from the “styles” section. Step 1: Select the range A2:C8 in the given data table. The steps to find the duplicate values for specific number of occurrences are listed as follows: In the following table, we want to check and show the duplicate values with three occurrences. Let us consider an example to identify the specific number of duplications. #2 – Conditional Formatting (Specific Occurrence)
The duplicate cells are highlighted in the data table, as shown in the following image.In the first box on the left side, select “duplicate.” In the “values with” drop-down, select the required color to highlight the duplicate cells. The pop-up window titled “duplicate values” appears.From the drop-down menu, select “highlight cell rules” and click on “duplicate values.” In the Home tab, select “conditional formatting” from the “styles” section.Select the data range (A1:C13) where duplicates are to be found.That way, the “DATA ARRAY” of text is converted into an array of number.The steps to find the duplicates in excel with the help of conditional formatting are listed as follows: If a record appears twice, the second time it’s first position is repeated. The array version of the MATCH formula returns the first position of each record within the list of records.What does this formula do in the background? The basic formula is FREQUENCY (please refer to this article for more information). The complete formula is =SUMPRODUCT((C2:C11>40)/COUNTIFS(A2:A11,A2:A11&""))Įxplanation of the FREQUENCY array formula to count the number of unique records But answering the question of how many people had a result higher than 40 (without the condition that it must have happened in the first trial) is possible to answer with this formula. This question requires two conditions (higher than 40 and the first trial). The second question of your example is “How many people had a result higher than 40 in the first trial?”. But again, only one criteria or condition is possible. You can of course use different criteria. If the condition is, that empty cells don’t count, the condition would be COUNT_RANGE””. The condition can be entered instead of the 1 in the beginning of the SUMPRODUCT formula like in the image on the right-hand side.
Because this formula regards empty cells as one unique record, the condition might be, that cells mustn’t be empty. This method is not suitable for the second question of our example.Ĭount number of unique records under one condition using the SUMPRODUCT formula.Blank cells are ignored in the number in the bottom left corner of the screen.In the bottom left corner of the window Excel shows the number of entries selected like this “Count: x”.Īlternatively, you could use the COUNT and COUNTIFS formulas. Select the list which now doesn’t have any duplicates any longer.If your data has headers, set the relating tick at “My data has headers”. Select the pasted values and click on “Remove Duplicates” on the “Data” ribbon.That way, formulas will be removed so that the values don’t change through possible underlying formulas within the pasting process. Recommendation: Instead of simply pasting the cells, paste them as values by pressing Ctrl + Alt + V on the keyboard. Select the list or column you want to know the number of unique values of and copy it to a new sheet or empty space on the same sheet.
It’s a built-in function in Excel and you can find it within the “Data”-ribbon.
Excel formula to remove duplicates and count update#
If you only want to count the number of unique records once and don’t have to automatically update the result, you could use the function called “Remove Duplicates”. Steps for using the “Remove Duplicates” function for counting unique values.