Count Unique Values in a Pivot Table

HomePivot TableCount Unique Values in a Pivot Table

Using a Data Model with a Pivot Table

The data model is yet another thing I love about the newer versions of Microsoft Excel. If you are using Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013, you have the access to Data Model.

  1. To start with, click on any cell in the data and go to the “Inset” tab in your ribbon.
    1-go-to-insert-tab
  2. Here click on the pivot table and a dialogue box appears.
    2-click-on-pivot-table-dialogue-box-1
  3. Now tick mark the box at the bottom of the dialogue box, “Add this data to the Data Model” and hit OK.
  4. After this, you will get a usual pivot table and arrange your data in the pivot table fields as you did earlier. This will give you the same pivot table you had earlier, but the pivot table fields look a little different.
    3-usual-pivot-table-1
  5. Here’s the trick: click on the small arrow next to “Count of Service provider” in the Pivot Table fields.
    4-count-of-sevice-provider-1
  6. After this click on the “Value Field Settings”.
    5-value-field-settings-1
  7. Now scroll down to the end to get “Distinct Count” and click OK.
    6-distinct-count-click-ok-1
  8. Here we go: you have a Distinct/Unique Count for each region in the pivot table.
    7-distinct-count-of-each-reason-1

Therefore, we have only 18 unique service providers in the country.

Using the Function COUNTIF

Another approach to calculating the Unique entries is simply using the formula COUNTIF in your Datasheet.

  • Let’s start by adding a column to your data with a header of your choice. Here we will call it “Count No.”
  • Add this formula (=IF (COUNTIF ($B$2: B2, B2)>1,0,1)) to the cell D2 and drag it till the end.
using-the-function-countif

How this Formula Works??

First of all, we have fixed the start point of the range, also called Absolute i.e $B$2. This means it will not change even if you drag your formula downwards. Now when you drag the formula downwards to D3, this formula becomes IF(COUNTIF($B$2:B3,B3)>1,0,1)

Read it like

Countif ($B$2:B3,B3) will give you the number of times B3 exists between the range $B$2:B3. IF function is used to add a condition: IF ((the number of times B3 Exists in a given range) is greater than 1, then give 0 else return 1)

Now, if the name in the given column exits more than 1 time, the formula will return you 0 else you will get 1. Therefore, for all those repeated names you will get 0 in the column Count no.

  1. Now create a Pivot Table with your data.
    9-create-a-pivot-table-1
  2. Here you need to add Location to the ROWS and Count No to the values.
    10-add-locations-to-the-row-1
  3. Boom!! The Pivot Table is ready with unique entries in each Pivot Table.
    11-pivot-table-with-unique-entries

Use Power Pivot to Count Unique Values

Here comes the most powerful method to identify the unique entries; Power Pivot. Make sure that you got the Power Pivot tab in your ribbon. If you cannot find the tab, check out this tutorial.

  1. As said earlier, first of all, make sure that the Power Pivot tab is enabled.
    12-power-pivot-to-count-unique-values
  2. After that go to the Data model and click on the Manage button.
    13-data-model-click-manage-button
  3. Here you will get a window opened, which surely will be blank in case this is the first time you are importing the data.
    14-blank-window-opened
  4. Click on Home → Get External Data
    15-go-home-click-external-data
  5. Here you will find multiple options and sources available to upload the data. But we need to upload a simple excel. So follow the steps and screenshots and click on “From Other Sources”.
    16-multiple-options-to-upload-data
  6. Now you will again get a dialogue box opened. Scroll to the end to get the option of Excel File and click Next.
    17-dialogue-box
  7. Here you can rename the connection from the default name “Excel”. Click on Browse to choose a path to your data file.
    18-rename-the-connection
  8. Also, if you want the top column to be the header row, tick mark the option “Use the first row as column header” and hit Next.
    19-column-to-be-header-row
  9. In the end, the file is imported to the data model and click on finish.
    20-file-imported-to-the-data
  10. Here we go: that’s a success with all the 28 rows imported. Now, hit on close.
    21-rows-imported-hit-close
  11. Now, this is how it looks like.
    22-sample-looks-like
  12. From here we will create a Pivot Table by Home → Pivot Table
    23-create-pivot-table-by-home
  13. Since we have the data in sheet 1, we will expand the columns by clicking on the small triangle next to it.
    24-expand-the-columns
  14. Now, place the Location on the Rows and the Service providers on the values as we did earlier. This will give a simple Pivot Table with the total number of service providers.
    25-place-location-on-the-rows
  15. Here’s the trick. Now go to the PowerPivot window and click on Measure to get the option of New measure.
    26-power-pivot-window
  16. Now add a description of the desired name and start typing the formula in the formula section.
    27-desired-name-and-formula
  17. As you start typing, you will get the suggestions automatically. Here we need the function of distinct count. Select the distinct count function.
    28-select-distinct-count-function
  18. After this, press the tab button or start a ( bracket and select the column for which we need the distinct count. Like here we need the distinct count of Service providers. Hence, our formula will look like this =DISTINCTCOUNT(Sheet1[Service Provider]))
    29-press-the-tab-button
  19. In the end, select the category. Since we are finding out the unique count of the service providers, we will select the category as “Numbers”.
    30-select-the-category
  20. Change the Format to “Whole number” and hit OK. Another column will be added to the Pivot Table which will give you the unique entries.
    31-change-format-to-whole-number

More on Pivot Table

Sort a Pivot Table | Refresh a Pivot Table | Pivot Table Keyboard Shortcuts | Pivot Table Formatting |Move a Pivot Table | Filter a Pivot Table  | Change Pivot Table Data SourceAdd or Remove Grand Total in a Pivot Table | Add Ranks in Pivot Table | Insert Calculation in Pivot Table | Refresh All Pivot Tables  | Automatically Update a Pivot Table | Running Total in a Pivot Table | Conditional Formatting to a Pivot Table | Pivot Table from Multiple Worksheets | Group Dates in a Pivot Table | Connect a Slicer with Multiple Pivot Tables | Pivot Table Timeline