![]() ![]() However, for more complex scenarios such as large ranges (lots of cells), lots of empty spaces, it is better to use a more robust and efficient formula. The method mentioned above is useful when you need to quickly count the number of unique values in a small range. =SUMPRODUCT(( Range “”)/(COUNTIF( Range, Range &””)) Method #2: Complex and Robust formula For example, if there are empty cells in the range you can do this: However, you might encounter some scenarios where you might need to tweak it a little bit. The formula shown above is the simplest way of counting unique values. The result of these formulas is shown in figure 2 below. ![]() ![]() We can count the unique values by writing the following formulas in cell H3 and H5 respectively. I have already highlighted the unique values for the convenience. Consider the values given in figure 1 below. The first method to count the unique values is by combining SUMPRODUCT() and COUNTIF() functions. We will discuss both ways in this tutorial. This task can be done in at least two ways. The following figure shows another example to highlight the first occurrence of each value.įigure 3: Another example of highlighted values at the first occurrence How to Count Unique ValuesĪ very important feature of Excel is that you can count the number of unique values in a given range. You will see that the values are highlighted at the first occurrence as shown below.įigure 2: Highlighted values at the first occurrence The general pattern for the formula is =COUNTIF( fir st cell of range locked with dollar signs : first cell of range unlocked, first cell of range unlocked ) = 1įigure 1: Steps to highlight the first occurrence of each value Type ‘ =COUNTIF($A$1:A1,A1)=1‘ in the formula bar.Select the range with the duplicate values (i.e.Instead of highlighting only the values that appear once in the range, sometimes we want to highlight only the first occurrence of each value. You will see that the unique values in the selected range are highlighted as shown in figure 3 below.įigure 3: Highlighted Unique Values How to Highlight the First Occurrence of Each Value Click on the down arrow and select ‘ Unique‘ as shown in figure 2 below. The default option selected is ‘ Duplicate‘. You will see a window shown in Figure 1 below.By unique values I mean values that only appear once in a range.įollow these steps to highlight unique values in a range: You can follow almost the same steps to highlight unique values in a range. If you read the first section, you already know how to highlight duplicates in Excel. You will see that duplicate values are highlighted after the second occurrence as shown in figure 2 below.įigure 2: Highlighted values after the second occurrence How to highlight unique values Steps are shown below for your convenience.įigure 1: Steps to highlight the values after the second occurrence Change the format as desired for duplicate values, you can change the format for Numbers, Font, Borders, and Fill. ![]() The general pattern for the formula is =COUNTIF( first cell of range locked with dollar signs : first cell of range unlocked, first cell of range unlocked ) > 1 Click on ‘ Use a formula to determine which cells to format‘.Go to ‘ Home’ ‘ Conditional Formatting‘.Select the range with duplicate values (i.e.This can be accomplished by following the next steps: However, sometimes we want to highlight the second occurrence or later occurrences of the duplicate values. If you use the steps shown above, you’ll be able to highlight ALL the occurrences of the values with duplicates. You will see that duplicate values are highlighted as shown below:įigure 4: Highlighted duplicate values How to highlight cell rules after second occurrence You will see a window as shown below, Click “ OK“.Click on ‘ Conditional Formatting‘ ‘ Highlight Cells Rules‘ ‘ Duplicate Values‘.įigure 2: Steps to highlight duplicate values.Now if we have the duplicate values in a column or columns (as we have in columns A and B in figure2 below), the following steps can be carried out to highlight the duplicate values. The ‘Conditional Formatting’ menu option can be found on the Home tab of Excel 2013 as shown below. You can do it with just a few clicks through the ‘Conditional Formatting’ menu option. Highlight the first occurrence of each valueįinding duplicates in existing data is one of the important features of MS Excel.Highlight duplicate values starting at the second occurrence of each value.In this post I’ll cover all you need to know to deal with duplicate/unique values in MS Excel.Īfter reading this post you’ll be able to: If you have worked with MS Excel for a while I bet you have had the need to highlight/count unique or duplicate values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |