Counting the unique values in Excel is essential for an accurate analysis of the data. Discover eight practical methods to do it, from the basic functions to advanced solutions such as Power Query and VBA.
Count unique values inExcelIt is a fundamental competence for those who work with data. Understanding how many distinct elements are present in a dataset not only helps to improve data analysis, but can also reveal errors or inconsistencies. In this article, we will explore eight different methods to count the unique values inExcel, each with their own peculiarities and advantages. From the simple countful function to the use ofPower Queries, we will guide you through each technique, offering you practical examples and useful suggestions along the way.
1. Understand the difference between unique and distinct values
Before immersing ourselves in methods, it is important to clarify the difference between unique and distinct values.
1.1 Unique Values
The unique values are those that appear only once in a set of data. For example, on the list{A, B, B, C, C, D}, the unique values are{A, D}.
1.2 Distinct Values
The distinct values include all the different elements present in a dataset. Continuing with the previous example, the distinct values of the list{A, B, B, C, C, D}I am{A, B, C, D}. Here, the counting of the distinct values is four.
1.3 Importance of distinction
This distinction is crucial for data analysis, since depending on your needs, you may need to count only the unique values or all the distinct values.
2. Use of the COUNTIFS function
The COUNTIFS function is a powerful tool for counting values in an interval that meet certain criteria.
2.1 Basic formula
To count the unique values, you can use the following formula:
= SUM (1*(Countifs (B5: B14, B5: B14) = 1))
This formula counts how many elements in the rangeB5: B14they are unique.
2.2 Formula operation
- Countnifsreturns the number of times each element appears in the interval.
- By comparing this number with 1, we get an array of Boolean values (true/false).
- Multiplying by 1, we convert the true in 1 and the false in 0, thus allowing to add the unique values.
2.3 Considerations
For the versions ofExcelolder, it may be necessary to insert this formula usingCtrl+Shift+Enterto activate the matrices.
3. UNIQUE function
Excel offers the Unique function, designed specifically to extract unique values from a list.
3.1 Function syntax
The formula to count the univocal values using Unique is:
= Counta (Unique (B5: B14, False, True))
3.2 Function parameters
- The first parameter is the interval to be analyzed.
- The second parameter, set toFalse, indicates to return the unique lines.
- The third parameter, set toTrue, indicates to return only the elements that appear exactly once.
3.3 Advantages of the function
This function is particularly useful for those who want a simple and direct method to obtain a list of unique values.
4. Conditional formatting to highlight unique values
Conditional formatting offers a visual way to identify the unique values in a data interval.
4.1 Application of formatting
You can apply the conditional formatting rule by following these steps:
- Select the data interval.
- Go to the tabHome.
- Click onConditional formatting.
- You chooseCelle highlighting rulesand thenDuplicate values.
- SelectSinglefrom the menu.
4.2 Data filtering
After highlighting the unique values, you can filter the list based on the color of the cells to view only the unique values.
4.3 Counting unique values
Using the functionSUBTOTAL, you can only count the visible cells:
= Subtotal (103, B5: B14)
5. Use of pivot tables
Pivot tables are a powerful tool to summarize and analyze large quantities of data.
5.1 Creation of a pivot table
Here's how to create a pivot table to count the unique values:
- Select the data.
- Go to the tabinsert.
- SelectPivot table.
- Choose the location for the new pivot table.
5.2 Configuration of the table
Add the fields to count in the areaLinesisValuesof the Pivot table dialog box. Each element with a count of 1 represents a unique value.
5.3 Filtering the results
You can apply a filter on the values to show only those with a count of 1, thus facilitating the identification of the unique values.
6. Dax measures for the counting of unique values
If you use the Excel data model, you can take advantage of Dax to calculate the unique values in a more advanced way.
6.1 Creation of a DAX measurement
You can create a DAX measurement as follows:
= Var MySummary = SUMMARIZE (Range, Range [make], "Unique", IF (Counta (Range [make]) = 1, 1, 0)) Return Sumx (MySummary, [Unique]))
6.2 Operation of the measurement
This measure creates a variable that summarizes the data and returns a count of the unique values directly in the pivot table.
6.3 Advantages of the Dax approach
This method is useful for more complex analysis, where advanced calculations are needed without having to manually filter the data.
7. Power Query for the counting of unique values
Power Query is a powerful Excel tool for the import and transformation of data.
7.1 Accessing Power Query
To access Power Query, follow these steps:
- Select the data.
- Go to the tabData.
- Click onFrom table/interval.
7.2 Group and counting
You can group the data to count the unique values:
- Go to the tabTransform.
- SelectGroup for.
- Choose the field to analyze and selectLines.
7.3 Filtering the results
After creating the summary table, you can filter to show only the values with a count of 1.
8. Use VBA to count the unique values
If you want a personalized solution, you can use VBA to create a function that counts unique values.
8.1 Creation of a VBA function
Open the VBA editor and enter the following code:
Public Function COUNTUNIQUEVALUES(rng As Range) As Integer
uniqueCount = 0
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
If Application.WorksheetFunction.CountIfs(rng, rng.Cells(i, j)) = 1 Then
uniqueCount = uniqueCount + 1
End If
Next j
Next i
COUNTUNIQUEVALUES = uniqueCount
End Function
8.2 Using the function
You can use this function in your worksheet like any other Excel function:
= CounterunaMqualues (B5: B14)
8.3 Advantages of the VBA approach
This method allows you to simplify formulas and adapt the function to your specific needs.
Conclusion
Counting the unique values in Excel may seem like a complex task, but with the right methods it becomes a simple and fast operation. That you choose to use integrated functions such as Countifs and Unique, or more advanced tools such as Power Query and Dax, each method has its advantages. Experiment with these techniques to find the one that best suits your data analysis needs.
Published inExcel
Comment first