If you using Excel versions prior to 2013 then you may want to look away now…only kidding!!!! But this tip really does only apply to Excel 2013 onwards, that is the ability to count unique items in your Pivot Table.
First let’s use a data table in Excel as our data source.
- Select your data set (CTRL+A)
- Hit CTRL+T
- Confirm your data table has headers
- Create your Pivot Table in the usual way by selecting the data table (or the shortcut keys ALT+N+V)
- Insert Tab – Tables Group – Pivot Table
- To ensure you can use the Distinct Count function this is where where MUST tick Add This Data To The Data Model in the Create Pivot Table Dialog Box
(the data model is part of Power Pivot and works even if you dont have it installed)
- Simply drop the field you want to make a unique count of into the Values area in this case it is Product Names
- Then navigate to the Value Field Settings of the value you added
- At the bottom of the list you will now see Distinct Count (this does not appear if you do not click Add To Data Model)
In this example I have chosen to count the number of unique products in the product list. The result is 4 unique items. Easy as that. This is a handy feature that has at last been included in the Pivot Table functionality, saving a lot of time and effort removing the need for extra helper columns and formulas in your data to accommodate a unique or distinct count feature.
Note this is not available in Excel versions prior to Excel 2013.
Want to watch the Video?
- Hide all of your Excel formulas from prying eyes!
- Undertand and debug Excel formula errors
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
