Description

how to get unique count in pivot ?

# How to Get Unique Count in a Pivot Table in Excel

Excel is a powerful tool for data analysis, and a pivot table is one of its most powerful features. It allows you to quickly summarize and reorganise intricate data in a way that makes it easy for you to make business decisions. However, did you know that Excel also has a feature that allows you to get a unique count of specific values directly within a pivot table? This post will break down how to do it in a few steps.

When you want to use a pivot table to count unique values, the process can be a bit trickier than it seems. Here’s a step-by-step guide to achieve that in Excel, using the information gleaned from a variety of Excel experts and their blogs.

### Creating a Pivot Table with Unique Count in Excel

1. **Select Your Data:** Firstly, carefully choose the table or range that you want to create your pivot table from. Highlight the cells, and everything is set to go.
2. **Insert the Pivot Table:** Go to the “Insert” tab, and find PivotTable under the tables section. Select “PivotTable” and in the new dialog box, make sure your range is correctly selected (or enter it manually if needed), then choose where you want the pivot table to be placed. It is highly recommended that you mark the option to add this data to the data model.
3. **Configure the Pivot Table Fields:** After inserting the Pivot Table, you will see a list of available fields or the “Field List” to the right. Pull in the fields you want to summarize and place them appropriately into the Row Labels, Column Labels, and Values panes.
4. **Change Aggregate Function to Distinct Count:** Once your values are set, you need to switch the aggregate function from “Sum” or “Count” to “Distinct Count”. To do this, right-click the Value field under the Values Pane, and select “Value Field Settings.” Under “Summarize Value By,” select “Distinct Count,” and click OK.
5. **Applying the Distinct Count:** Click OK and the Pivot table will automatically update to reflect the distinct count of the variable in question.

### Additional Tips and Tricks

– **Excel Versions:** The Distinct Count feature is generally available in Excel 2013 and later versions.
– **Data Model for Old Excel Versions:** If you’re using an older version of Excel, you could create a unique identifier in your source data by concatenating the columns that need to be counted uniquely and then create a pivot table off of the newly created field, but it’s much simpler to use the data model approach in newer Excel versions.
– **Data Integrity:** Ensure that there are no spaces or typos in the table before creating the pivot table; these will all be counted as separate values.

### Tools and Resources

* [WallStreetMojo – Pivot Table Count Unique](https://www.wallstreetmojo.com/pivot-table-count-unique)
* [ExtendOffice – Count Unique Values in Excel Pivot Table](https://www.extendoffice.com/documents/excel/2127-excel-pivot-table-count-unique-values.html)
* [ExcelJet – Pivot Table Unique Count](https://exceljet.net/pivot-tables/pivot-table-unique-count)
* [YouTube Video – Count Distinct in Pivot Table](https://www.youtube.com/watch?v=sId8vpdp1qw)

### Conclusion

Whether you’re running a business or analyzing data for personal projects, being able to calculate the distinct count of items directly from Excel’s Pivot Table feature is a valuable skill. While getting the unique count in a Pivot Table is more convoluted than a simple COUNT function, the effort is well worth it when you need to sift through large quantities of data to see a distinct count. Remember that some advanced features like distinct count require use of Excel’s data model, or may need user-defined functions for older versions of Excel. But for the most part, you’ll have modern, efficient tools available to help make short work of summarising unique item counts in a Pivot Table.

Using the steps I outlined above, you can create unique count summaries in moments and keep your data precise and easy to comprehend. Don’t let a challenge with unique values hold you back from knowing precisely what your data is communicating. Happy analyzing!