Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Count Distinct Values In Excel: Classic To Modern Approaches

by | 1:57 am EDT | June 08, 2022 | Power BI

In this tutorial, I’m going to go through different approaches to how you can count distinct values in Excel from a more traditional way to a more modern technique. You can watch the full video of this tutorial at the bottom of this blog.

I’ve got a list of names here and I want to know how many unique names there are. There are seven names in total, but how many are not repeated? If I remove the duplicates, I get five.

I’m going to do this in Excel. There are a few ways to do this, and we’re going to look at this from the most classic to the most modern approach. We’ll start with Remove Duplicates, then we’ll do Count Distinct using the data model, and then we’re going to use Dynamic Arrays.

## How To Count Distinct Values In Excel

Here I have a data set of team records from major league baseball from 1995 through 2018. What I want to know is how many unique team names there are. We’ve got this Name column and I want to count these up.

So, we’re going to do three options. First is the most traditional way. I’ll go over to my Data tab and there’s an option to remove duplicates. That’s going to work, but I’m going to copy and paste the Name column to a new sheet because this is going to just ride over this data.

And now, if I remove duplicates, it will give us what remains, which is 35 unique values.

I could also do this COUNTA. I’m using COUNTA rather than just COUNT because COUNTA also picks up text values while COUNT only picks up number values. So, when we run that, we can see 35 unique team names.

So that’s a pretty easy option to use. We didn’t need to write any functions. It’s very low code, but the fact that we have to hard code messes up our original code. That’s not great for me. So, let’s try another option.

Now, this one is related to the Power pivot and the Data model. We’re going to create a pivot table report, and that’s going to let us count those unique values. So, let’s insert a pivot table. We have our Data table here, which is called Teams. We’ll create it in a new worksheet and add it to the Data model.

And if I click, OK, we’ll get what looks like a plain old pivot table. Now, I’m going to put the column, Name in my Value here.

And then, I’m going to click on that Value field and summarize it. This is not available in the classic pivot table. This Distinct Count is only available in this Data model.

We’ll load this in and we get 35. So, there’s option B in Power pivot. This one’s been around for almost 10 years now. It’s not really well known, but it’s very powerful. And if you’re using Power BI, you’re probably familiar with the Data model, Dax, etc. The same basic infrastructure is being used here in Excel.

The final method that I want to show you is by using Dynamic arrays. These are pretty brand new, and you may not have them yet, depending on what version of Excel you use. This is only for 365.

So, I’m going to use UNIQUE, which is going to return all the unique values in an array.

Then, I’m going to select the Name column, and this gives us a dynamic array. In this case, we’re using a formula. We didn’t have to manipulate the source data. We can see where the source data is.

From here, I can do a COUNTA. Now, notice when I select this entire range, there’s this pound sign. This is the dynamic array spill operator. When I do this, it’s telling Excel that I want to operate on the dynamic array that’s associated with this cell; not the cell itself, per se, but the dynamic array started in that cell.

These dynamic arrays are really only a couple of years old and generally released more in a matter of months to most users. This is a new way of doing it.

## Conclusion

I’ve shown you three different ways to count distinct values in Excel. We started with the classic Remove Duplicates approach. We just have to use the menu. There are no functions involved, however, it doesn’t make me comfortable that we have to copy and paste. There’s not a lot of integrity when we do it.

Then we get to the data model, which is pretty cool. This is also menu-driven, with no formulas, and it’s dynamic. Not everybody may know about this though, and we do have to refresh the data if we wanted to get updates. We use Pivot tables or the Pivot Table Report, as it’s called now with Power Pivot.

And then, we got to the very modern method, which uses the unique function, Dynamic Arrays. This is formula-driven, which may be a pro or con, depending on who you ask. The big downside of this one is that it’s a little verbose. We are using two functions. Also, the fact that it’s not as common to have this function available.

If you have other ways of doing this, let us know. We would love to see them. There are always different ways to do things in Excel.

All the best!

George

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...