During our consulting services for excel, we face one question that in Ms-Excel 2016 version Pivot Table distinct count is not available. We have covered 3 topics in this tutorial as follows.
1. How to get a distinct count in Ms-Excel 2013, 2016 and Office 360 Pivot Table.
2. How to convert Date which in Text Format to Date Format.
3. How to group data field at Year, Quater and Month Level.
► How to convert a dates which are formatted as text to date value.
When we export data to excel from other ERP we may face a situation that date column contains dates in text format where we can not perform data level operations. This problem can be solved by using =datevalue() function. This function converts date to the serial number and we can apply the desired format.
► How do we group dates into Year & Months in a pivot table?
In Pivot Table date should be a group or not can be defined at the application level, if by default grouping is not enabled we can group date field at various time dimensions.
►How do I turn on distinct counts in the pivot table?
Usually, Distinct count is database-level functionality, thus while creating a pivot table we should instruct excel to treat our data as a database that enables 'distinct count' feature.
► If you need help growing your business using data analytics check out Canny Informatics @
#businessintelligence #msexcel
Credits for Pictures and Sound
Thanks to www.freepik.com and www.bensound.com; we have used royalty-free resources from their websites.
0 Comments