WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Pivot Table Count Unique

Pivot Table Count Unique

Excel Pivot Table Count Unique

One of the key elements of Pivot Table is it takes all the rows into consideration and shows the count of the data as those many rows, but in the case of knowing unique count, we don’t have a default option. So, would you like to know how many customers are there instead of how many transactions?? Then this article will take you through of counting unique values in pivot tables.

What is Unique Value Mean?

Assume you have gone to one of the supermarkets 5 times to buy groceries in the last month, now Supermarket owner wants to know how many customers have come in the last month. So here the question is they want to know how many customers have come, not how many times, so in your case, you might have gone 5 times, but the actual customer count is 1 only, not 5.

For example, look at the below data.

Pivot table Count unique values Example 1

In the above data table, we have two customers, “John” & “Peter,” they went to the supermarket on different dates, but when we apply the pivot table to get the total customer count, we will get the result like the below one.

Pivot table Count unique (Count)

Upon applying the pivot table, we got a unique customer count as 7, whereas only two customers have come to the supermarket. So getting a unique count isn’t the straight forward one.

This is what is all about the unique count, but with Pivot Table, we don’t have this luxury of getting unique values straight away, so we need to use a different strategy to get the unique count of values.

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

How to Get Unique Value in Pivot Table?

Note: You need to have Excel 2013 or later versions to use this technique.

Take the above-used data only for this example as well.

Pivot table Count unique values Example 1

Choose the data range from A1 to D8 to insert a pivot table.

Pivot table Count unique values Example 1-1

After selecting the data range to insert a pivot table, go to the INSERT tab in excel and click on the “Pivot Table” option.

Pivot Table Insert

If you hate manual steps, then simply you can press the shortcut in excel of ALT + N + V; upon pressing the above shortcut key, it will open up below the “Create Pivot Table” window for us.

Pivot table Count unique values Example 1-2

In the above window, choose the pivot table destination as “Existing Worksheet.”

Pivot table Count unique values Example 1-3

After choosing the worksheet as this Existing Worksheet, we need to choose the cell address where we need to insert the pivot table.

Pivot table Count unique values Example 1-4

One more important thing we need to make in the above window is we need to make sure the checkbox of “Add this data to the Data Model” is ticked.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1-5 (data model)

Click on “Ok,” and we will have a blank pivot table in place.

Example 1-6

Insert the “Item” field to the “ROWS” area and the “Customer” field to the “VALUES” area.

Example 1-7 (Field values)

Now we are getting the same total as the previous one, but we need to change the calculation type from COUNT to “DISTINCT COUNT.”

Right-click on of customer count cell and choose “Value Field Setting.”

Example 1-8 (Value field settings)

This will open up the below window for us, and in this, we can see actually what is the current summarization type is.

Example 1-9 (Count)

As we can see above current summarization type is “COUNT,” so the pivot table showing the current count of customers as 7 because there are 7 line items in the selected data range of the pivot table.

This is the default summarization that will be chosen in the case of “TEXT” values used in the “VALUES” area of the pivot table.

In the same above window, scroll down to the bottom and choose the summarization type as “DISTINCT COUNT.”

Pivot table Count unique values Example 10

After selecting the summarization type as “DISTINCT COUNT,” click on “Ok,” and we will have a unique count of customers as 2 instead of 7.

Pivot table Count unique values Example 11

So, we have an accurate count of customers in place. Actually, we can see the column header as “Distinct Count of Customer” instead of the previous header of “Count of Customer.”

Like this, using Pivot Table’s “Distinct Count” summarization type, we can actually get the unique count of values.

Things to Remember

  • We need to choose the check the box of “Add this data to the Data Model” while inserting a pivot table.
  • DISTINCT COUNT summarization type is available from Excel 2013 onwards only.

Recommended Articles

This has been a guide to Pivot Table Count Unique. Here we discuss how to count unique values in an excel pivot table with some examples. You may learn more about excel from the following articles –

  • Pivot Table
  • Pivot Table Add Column
  • Pivot Table In Power BI
  • Refresh Pivot Table in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Pivot Table Count Unique Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More