How to Count Distinct Values in Excel: A Comprehensive Guide

Counting distinct values in Excel is a handy skill when working with data. It helps you understand the uniqueness of your entries without duplications. Here’s how to do it: use the "Remove Duplicates" tool or apply the "COUNTIF" and "SUMPRODUCT" functions. This guide will walk you through both methods step-by-step, ensuring you can quickly identify distinct values in any dataset.

Step by Step Tutorial on How to Count Distinct Values in Excel

This guide will help you count distinct values in Excel using two methods: the "Remove Duplicates" tool and the "COUNTIF" and "SUMPRODUCT" functions. By following these simple steps, you’ll be able to manage and analyze your data more effectively.

Step 1: Open your Excel spreadsheet

First, make sure your data is in an Excel spreadsheet.

Open Excel and load the spreadsheet that contains the data you want to analyze. Ensure the data is organized in columns for easier handling.

Step 2: Select your data range

Highlight the cells that contain the data you want to examine.

Click and drag to select the range of cells that include your data. This step is crucial because it tells Excel which data to process for counting distinct values.

Step 3: Use the "Remove Duplicates" tool

Go to the "Data" tab and click "Remove Duplicates."

In the toolbar, you’ll find the "Data" tab. Click on it, then find the "Remove Duplicates" button. Clicking this will open a dialog box allowing you to select columns to check for duplicates.

Step 4: Confirm the columns for duplicate removal

In the dialog box, select the columns you want to check for duplicates and click "OK."

Make sure only the relevant columns are checked. This ensures that Excel only removes duplicates based on your specified criteria.

Step 5: View the results

Excel will display a message showing the number of duplicate values removed and how many unique values remain.

After clicking "OK," Excel will inform you about the number of duplicates it removed. The remaining values are the distinct ones.

Step 6: Use the "COUNTIF" and "SUMPRODUCT" functions for more control

Alternatively, use the formula =SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10)).

This formula counts distinct values in a more controlled manner. Simply adjust the cell range to match your data set.

Step 7: Enter the formula in a new cell

Type the formula into a blank cell to get the count of distinct values.

Make sure you press "Enter" after typing the formula. Excel will then calculate the number of distinct values in your selected range.

Once you’ve completed these steps, you’ll know the number of distinct values in your dataset. This helps in making better data-driven decisions and analysis.

Tips for Counting Distinct Values in Excel

  1. Use Named Ranges: Assign a name to your data range for easier reference in formulas.
  2. Filter Data: Apply filters before using the "Remove Duplicates" tool to refine your results.
  3. Pivot Tables: Use pivot tables for a more advanced analysis of distinct values.
  4. Check for Errors: Ensure your data doesn’t have errors or blank cells that may affect the count.
  5. Use Conditional Formatting: Highlight duplicates first to visually inspect data before removal.

Frequently Asked Questions

What is the difference between unique and distinct values?

Unique values appear only once in a dataset, while distinct values are all the values without duplicates.

Can I count distinct values in Excel without removing duplicates?

Yes, you can use the "COUNTIF" and "SUMPRODUCT" functions to count distinct values without altering your dataset.

What if my data has blank cells?

Blank cells can affect the counting process. It’s best to remove or fill them before counting distinct values.

How do I count distinct values in a column with text and numbers?

Use the "COUNTIF" and "SUMPRODUCT" functions to handle datasets with mixed data types.

Is there an Excel function specifically for counting distinct values?

No, Excel does not have a specific function for counting distinct values, but you can use a combination of existing functions like "COUNTIF" and "SUMPRODUCT."


  1. Open your Excel spreadsheet.
  2. Select your data range.
  3. Use the "Remove Duplicates" tool.
  4. Confirm the columns for duplicate removal.
  5. View the results.
  6. Use the "COUNTIF" and "SUMPRODUCT" functions for more control.
  7. Enter the formula in a new cell.


Counting distinct values in Excel can seem like a daunting task, but with the right tools, it’s a breeze. Whether you use the straightforward "Remove Duplicates" tool or the more versatile "COUNTIF" and "SUMPRODUCT" functions, you’ll have your data cleaned up in no time. Knowing how to count distinct values is essential for anyone dealing with data – it ensures your analysis is accurate and reliable. So, next time you’re swimming in a sea of numbers and text, remember these methods to keep everything shipshape. For more Excel tips and tricks, keep exploring and practicing, and soon you’ll be an Excel wizard.

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time.
Read our Privacy Policy