EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Pivot Table Count Unique
 

Pivot Table Count Unique

Madhuri Thakur
Article byMadhuri Thakur

Updated May 30, 2023

Pivot Table Count Unique

 

 

Pivot Table Count Unique (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • 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. 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 hose 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:

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 can see that the count for countries we traded is 12 in the pivot table, which is not a unique count. We need to amend 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 applies to Excel’s 2013 or later version.

Let us only consider the data shown in the above screenshot, 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 will 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 can 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 the Create PivotTable window that pops up, you can 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 the pivot table needs to be created, has two choices. Either you can create it on a new worksheet or the existing worksheet. Click on the radio button next to it. We will choose the Existing Worksheet option.

Existing Worksheet

Step 7: Give the location cell where you want 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 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 Destination Country count as expected. To get the same, follow the step below:

Step 10: Right-click on the 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 answering 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 Destination Country column.

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 in Pivot Table Count Unique

  • 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 Excel’s unique count of values. You need to use the Add this data to the Data Model option 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, 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. Excel Pivot Table
  4. Pivot Table Examples

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Pivot Table Count Unique Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Pivot Table Count Unique Excel Template

EDUCBA

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

🚀 Limited Time Offer! - 🎁 ENROLL NOW