Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to count unique values excluding duplicates in Excel?

Author Siluvia Last modified

When working with lists of data in Excel, especially those involving names, IDs, or other repeating entries, you may need to determine how many values occur only once — that is, to count the truly unique values while completely ignoring duplicates. For instance, imagine a list of names where some names are repeated. If you want to find out how many names appear only a single time in the list (excluding all that appear more than once), Excel offers several ways to accomplish this. This article provides a comprehensive guide on multiple methods to achieve this goal, covering formulas, VBA ,PivotTable and built-in features. Advantages and potential limitations of each method are also discussed to help you select the one that fits your specific scenario.

Count unique values excluding duplicates with a formula
Easily count unique values excluding duplicates with an amazing tool
Count unique values excluding duplicates with VBA code
Count unique values excluding duplicates with Pivot Table

Count unique values excluding duplicates with a formula

Suppose you have a data range as shown in the screenshot below, and you want to count only the names that appear just once in the entire list. Using Excel formulas for this task is straightforward and requires no extra tools or add-ins. This method is especially suitable when you have a moderately sized range and prefer a pure Excel approach.

a screenshot showing the original values and the total count of unique values

1. Click to select a blank cell where you want the result to appear. For best practice, choose a cell outside your dataset to avoid overwriting data.

2. Enter the following formula and press Enter to display the count of unique values that appear only once (excluding all values that appear more than once):

=SUM(IF(FREQUENCY(MATCH(B3:B14,B3:B14,0),ROW(B3:B14)-ROW(B3)+1)=1,1))

a screenshot of using formula to count unique values excluding duplicates

Notes:
1) In the above formula, B3:B14 is the range containing the values to be analyzed. Adjust the range reference according to your actual data. The range can extend across as many rows as needed.
2) For array formulas in older Excel versions, you may need to press Ctrl + Shift + Enter after typing the formula, instead of just Enter. In Excel 365 and Excel 2019 or later, regular Enter works.
3) Take special care with empty cells in the range, as they can affect results. Try to clean your dataset or use the formula only on non-blank lists for accurate outcomes.
4) If your data range is very large, formula calculation may be slower — in that case, consider using other solutions below.

Scenario & Pros/Cons:

  • Works well for standard and small-to-medium data ranges.
  • No add-ins required; native Excel function.
  • May require array formula entry; performance may slow for massive datasets.

 

Easily count unique values excluding duplicates with an amazing tool

Kutools for Excel provides an intuitive feature called Count Unique Values that lets you quickly count only those values that appear just once in your list, without any need for complicated formulas or time-consuming manual work. This method is excellent for users seeking both flexibility and simplicity, and it supports larger datasets with ease.

1. Select a blank cell to display the result, making sure it will not overwrite any of your existing data. Then go to Kutools > Formula Helper > Formula Helper.

a screenshot of enabling the Formula Helper

2. Inside the Formulas Helper dialog box, perform the following actions:

  • Locate and select Count unique values from the Choose a formula list.
    Tip: Use the Filter box to quickly search by entering keywords related to "unique".
  • Indicate the Range that contains your data to be analyzed.
  • Click OK to insert the function and show the count of values that occur only once in your list.

a screenshot of selecting a range for the Count unique values formula

This approach instantly calculates the count of unique values, helping you avoid manual counting or formula troubleshooting. The result reflects the number of items that appear only one time in your selection:

a screenshot showing the result

Tips and Notes:

  • With Kutools, there is no need to enter complex formulas or worry about formula errors.
  • This utility supports both continuous and non-continuous ranges.
  • If you frequently perform such data analysis, using Kutools can save significant time and reduce mistakes.
Scenario & Pros/Cons:
  • Best for users who want a quick, error-free solution.
  • Handles larger data ranges more efficiently than formulas.
  • Kutools needs to be installed and activated before use.

 

Count unique values excluding duplicates with VBA code

For scenarios where you need to automate this task or repeatedly count unique values that appear only once across multiple sheets or workbooks, using VBA (Visual Basic for Applications) is a practical approach. This solution lets you count items that are present in your range just once, ignoring all items that reflect more than one occurrence.

Applicable scenarios:

  • Automating the process for large or multiple datasets
  • Integrating into Excel macros or batch processes
  • Users comfortable with basic VBA operations
Pros/Cons:
  • Highly flexible, reusable for future data analysis
  • Customizable for enhanced reporting
  • Requires basic familiarity with VBA; code must be added manually

 

Steps:

1. Open the VBA editor: Click Developer Tools > Visual Basic. In the new Microsoft Visual Basic for Applications window, click Insert > Module.

2. Paste the following code into the Module window:

Sub CountUniqueOnlyOnce()
    Dim WorkRng As Range
    Dim cell As Range
    Dim dict As Object
    Dim singleCount As Long
    Dim Key As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select range to count unique and non-duplicate values:", xTitleId, WorkRng.Address, Type:=8)
    
    If WorkRng Is Nothing Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In WorkRng
        If Not IsEmpty(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        End If
    Next cell
    
    singleCount = 0
    
    For Each Key In dict.Keys
        If dict(Key) = 1 Then
            singleCount = singleCount + 1
        End If
    Next Key
    
    MsgBox "Count of unique values that appear only once: " & singleCount, vbInformation, "Result"
End Sub

3. Click the Run button button or press F5 to run the code. A dialog box will prompt you to select your data range. After you select and confirm, a message box displays the count of unique items appearing only once.

Precautions and troubleshooting:

  • Ensure you have saved your workbook before running macros.
  • This code ignores empty cells by default.
  • If your Excel security settings prevent macro running, adjust them from the Trust Center under Excel Options.
  • If you encounter an error, make sure that you are selecting a valid single-column or single-row range.

 

Count unique values excluding duplicates with Pivot Table

Pivot Tables offer a flexible, interactive way to summarize data in Excel, and you can use them to count how many distinct items occur only once in your data range. This solution is particularly worthwhile when you already use Pivot Tables for other analyses or want a no-formula, menu-driven route. It is especially suitable for users handling large datasets or wanting dynamic, filterable reports.

Advantages:

  • Easy to update by simply refreshing the Pivot Table
  • Visual, with built-in filtering and sorting functions
  • Zero formulas required

 

Steps:

  • Select the complete range of your data (including the column with the values you want to check).
  • Go to Insert > Pivot Table. In the popup, choose where to place the Pivot Table (new sheet or existing sheet).
  • In the PivotTable Fields pane, drag the column header (for example, "Name") into both the Rows area and again into the Values area. In the Values area, make sure it is set to Count (if not, click and change the calculation type from Sum or other to Count).
  • The Pivot Table displays each item with the number of times it appears. To see only those items that occur once, use the filter drop-down on the count column, select Number Filters > Equals >1. This filters the table, showing only the values that appear just once.
  • Count the number of remaining visible items — this is your count of unique (non-duplicated) values.

Precautions and troubleshooting:

  • Blank or empty cells will be listed separately in the Pivot Table; you may want to filter them out.
  • Refresh your Pivot Table after changing or updating underlying data.
  • Pivot Tables work well with large sets of data, but do not update in real time unless refreshed manually.
  • If you wish to automate the filtered count, consider using the SUBTOTAL or COUNTA functions on the Pivot output.
Scenario & Pros/Cons:
  • Very suitable for summary reporting, large data analysis, or where you want to see a live list of items counted only once.
  • Requires several steps but is formula-free.

 

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Demo: Count unique values in a column in Excel

 

 

 

  Kutools for Excel includes 300+ powerful features for Microsoft Excel. Free to try with no limitation in 30 days. Download now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in