![count unique values excel count unique values excel](https://trumpexcel.com/wp-content/uploads/2019/05/Distinct-Count-Formula-Helper-Column-in-Pivot-Table-Data.png)
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.
![count unique values excel count unique values excel](https://i.stack.imgur.com/0Jvte.jpg)
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.
COUNT UNIQUE VALUES EXCEL HOW TO
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
![count unique values excel count unique values excel](https://i.ytimg.com/vi/znXCniLou8Y/maxresdefault.jpg)
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.
![count unique values excel count unique values excel](https://cdn.extendoffice.com/images/stories/doc-excel/count-unique-values/doc-count-unique-in-a-list-1.png)
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.