How to Get Unique Count in a Pivot Table

To count unique values in a pivot table, please follow the steps below. Please note that unique count only if you have Excel 2016 or after (download data sample).

Step 1: Select all data by click all columns headers;

Step 2: Click the "Insert" Tab from the ribbon, and click "PivotTable" in the "Tables" section;

Step 3: In the "Create PivotTable" window, choose the pivot table location, and check "Add this data to the Data Model";

– You can select "New Worksheet" to have the pivot table in a new worksheet, or you can select "Existing Worksheet" then click anywhere to have the location.

Step 4: To count how many persons in each region, we need to drag "Region" to the "Rows" field, and drag "Name" to the "Value" field. However, this will have us the row count, not the unique count;

Step 5: To change from count to unique count, please click on "Count of Name", and click "Value Field Settings";

Step 6: In the dialog box, select "Distinct Count"; if you cannot see the selection, please scroll down the bar to have it.

Step 7: Click "OK" at the bottom, you will have the unique count for each region.

2 Comments

  1. Sam Khan April 13, 2021
    • David April 13, 2021

Leave a Reply