EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Pivot Table Count Unique
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Pivot Table Count Unique

By Madhuri ThakurMadhuri Thakur

Pivot Table Count Unique

Pivot Table Count Unique (Table of Contents)

  • Introduction to Pivot Table Count Unique
  • What is Count Unique?

Introduction to Pivot Table Count Unique

A pivot table is the best feature that Excel includes within. It takes rows into consideration and then allows us to summarize the data based on either sum or count or average or any other numerical aspect of the data we want. The best part, it also helps us counting the text values. By far, the count function within the pivot table is quite of use and needed every now and then by analysts. However, when we are in need to count unique values, there is no default function under Excel. With some tweak and twist, we can count the unique values in the Excel pivot table.

What is Count Unique?

Suppose you are running a business and have sold hoses pumps to different countries in the past 12 months. Now, you want to check how many unique customers (in our example, consider the country as a customer) you have achieved during this period. The chances are that we have traded with multiple countries and out of which we only want to figure out the unique country values.

Just a small example for your reference here. We are not using the entire year’s data. Since it is a day-wise trade, it will be a huge one. Just for a realization, see the image below:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this Pivot Table Count Unique Excel Template here – Pivot Table Count Unique Excel Template

Pivot Table Count Unique 1-1

If we go through the data, we can say that there are three unique country values: India, United Arab Emirates, and the USA. However, if we do a pivot table and count the Destination Country, then the results are somewhat weird, as shown in the screenshot below:

Count of Desination 11

You could see that the count for countries we traded is 12 in the pivot table, which is definitely not a unique count. We need to make some amendments under the pivot table to get the unique count. In this article, we will make you walk through the method using which we can count the unique values in the Pivot Table. Just follow the steps below:

Before we proceed further, let me clear a thing for you. This technique of getting unique values in the pivot table is applicable for Excel’s 2013 or later version.

Let us consider the data shown in the above screenshot only, which is spread across A1:D13.

Pivot Table Count Unique 1-1

Step 1: Select the data range for this entire tabular data that spreads across A1 to D13.

Pivot Table Count Unique 1-2

Step 2: Now, we are going to add a pivot table for a selected range of data. For that, go to the Insert tab placed on the upper ribbon in the Excel sheet. Click on the Insert menu tab.

Pivot Table Count Unique 1-4

Step 3: Once you click on the Insert menu tab, you’ll see different insert options associated with the selected range of cells; out of those, under the Tables group, you could see PivotTable. Click on it to add a pivot table for a given range of data.

Pivot Table Count Unique 1-5

You have a shortcut to add a pivot table. You can use Alt + N + V as a keyboard shortcut to insert a pivot table. This will also allow you to add a pivot table in Excel using the keyboard shortcut.

Step 4: As soon as you click on the PivotTable option under the Tables group inside the Insert tab, a new window will pop up named Create PivotTable, as shown in the image below:

Pivot Table Count Unique 1-6

Step 5: Within Create PivotTable window that pops up, you have the option to add the range of cells on which you need to apply the pivot table. This can be done under the Select a table or range section. By default, our range of data has been selected at the start of the creation itself.

Pivot Table Count Unique 1-7

Step 6: Destination, where pivot table needs to be created, has two choices. Either you can create it on a new worksheet or on the existing worksheet. We will choose the Existing Worksheet option. Click on the radio button next to it.

Existing Worksheet

Step 7: Give the location cell where you wanted to store the pivot table in the existing worksheet. I will use the location as cell H2.

create pivot table - location

Step 8: At the bottom of the Create PivotTable window, you’ll see a checkbox Add this data to the Data Model. You need to tick select this checkbox. This will allow us to count the unique values in the pivot table.

add pivot table

Click on the OK button, and that’s it. We will have a blank pivot table, as shown in the screenshot below:

pivot table 1

Step 9: Under Pivot Table Fields, select Item under Rows and Destination Country under the Values section as shown below. You can do this by dragging and dropping fields with the mentioned name under the respective tabular section.

Pivot Table Fields

We are still not getting a unique count of Destination Country as we were expecting. To get the same, follow the step below:

Step 10: Right-click on Count of Destination Country Column, and you will see a list of options as shown below. Select the Value Field Settings… option out of those.

value Field Settings

This will open up the Value Field Settings window, as shown below. We clearly could see that the current summarization type is COUNT for value fields (i.e. Destination Country column). Since the COUNT option only counts the number of rows present in source data, in our case, it is giving an answer as 12 because we have 12 rows in the given data for Destination Country.

Count of Destination

However, it should not be like this; we need unique distinct values to be counted for the Destination Country column. To achieve this, you need to do something.

Navigate through the Summarization type using the scroll bar and click select the Distinct Count option to count the unique values. Click on the OK button once done.

value Field Settings 11

As soon as you select the Distinct Count and hit the OK button, you’ll get the value for the count of the Destination Country column, as shown in the screenshot below. It specifies the unique count of values present under the column named Destination Country.

value Field Settings 12

This is how we can get the count of unique values under PivotTable in Excel. We will end this article here. We have some points to be remembered for the same.

Things to Remember

  • The method used in this example is working on Excel’s 2013 and later versions. For previous versions of Excel, you might need to use different techniques such as Pivot of Pivot Table, etc., to get the unique count (This is out of the scope of this article).
  • There is no default way to capture the unique count of values in Excel. You need to use the Add this data to the Data Model option, which is at the bottom of the Create PivotTable window.

Recommended Articles

This has been a guide to Pivot Table Count Unique. Here we discuss How to use Pivot Table Count Unique along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Pivot Table Filter
  2. Pivot Table Slicer
  3. Pivot Table Examples
  4. Pivot Table Formula in Excel
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,547 ratings)
Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Training (23 Courses, 9+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Pivot Table Count Unique Excel Template

EDUCBA

Download Pivot Table Count Unique Excel Template

EDUCBA

डाउनलोड Pivot Table Count Unique Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more