# Count Distinct Values In Excel: Classic To Modern Approaches

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

## Mastering Weighted Averages with SUMPRODUCT in Excel

This guide will help you learn how to use the SUMPRODUCT function in Excel for calculating weighted averages, with practical examples and clear instructions.

## Mastering Excel Shortcuts: A Complete Guide

Unlock the full potential of Excel with this comprehensive guide to mastering shortcuts. Improve your productivity and streamline your workflow with key combinations.

## Transportation Data Management with VBA

Learn how to use VBA in Excel to efficiently manage transportation data.

## Comprehensive VBA Inventory Management System

A project focused on implementing an extensive VBA-based inventory management system within an Excel spreadsheet to streamline data handling.

## Mastering VBA Arrays for Spreadsheet Automation

This project aims to streamline and boost efficiency in Excel spreadsheet tasks using VBA arrays.

## Mastering Data Analysis with Pivot Tables in Excel

Learn to analyze and summarize complex data using Excel’s powerful Pivot Table feature.

## Financial Data Management with VBA

This project teaches how to manage and automate financial data in Excel using VBA programming.

## Does Excel Work On Mac? How is it Different to Windows?

Hey there, fellow Mac users! Have you ever wondered how you can level up your data management game with...

## How to a Take Screenshot in Excel: A Guide for Windows & Mac

As a data analyst or a spreadsheet enthusiast, youâ€™re probably familiar with the many ways Excel can...

## How to Sum a Column in Excel: 5 Ways (Shortcut Included)

One of the best things about Excel is how quickly and easily it can add up all the numbers in a column....

## How to Calculate Interpolation in Excel

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## How Long Does it Take to Learn Excel?

So you're looking to learn Excel, one of the most widely used software applications in the...