Analytics is all about summarizing and grouping. Numbers are grouped into bins, text is categorized and dates are compressed into months, quarters and years. This series covers various quick and powerful methods to perform sophisticated grouping in seconds.
Estimated reading time 10 min
Raw data is usually captured at the lowest level. We need to summarize and categorize it to interpret and analyze it.
This knowledge pack consists of a series of articles which cover Pivot Table, VLOOKUP, and other techniques to group three data types available in Excel : Text, Numbers and Dates.
It is an invaluable reference for common reporting requirements like ABC analysis, Ageing Analysis, Monthly and Quarterly reports, Volume Value Analysis and so on.
Data Grouping Articles
Here is a list of articles. I recommend that you read the articles in the order shown below. Each article builds upon the previous one. All articles have sample files which can be used to follow the instructions.
Once you learn the concepts, try using it on real-life data. This will help you understand the tweaking required to apply this knowledge to your business.
Finally, make sure you share this knowledge with your colleagues, bosses and friends.
- Grouping Text in Pivot Tables
- VLOOKUP based text grouping
- How to correct spelling mistakes while analyzing data?
- Ageing analysis in seconds (Number Grouping in Pivot)
- Ageing Analysis (Numeric Grouping in Pivot) – Part 2
- Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3
- Grouping Dates into months, quarters and years – in seconds!
- Custom Date Grouping using VLOOKUP
- Grouping with Date – Time Data using Pivot
Do post your thoughts, feedback and suggestions. Enjoy.