Knowledge Pack: Data Analytics

Data Analysis and Reports has been a major theme for my articles so far.

Here is a collection of 50+ articles, categorized and sorted in the recommended reading order.

Enjoy!

Data Gathering, Clean up, Transformation and Auditing (ETL)

1. Big Data – Small Problem! Is the data good?
2. Good Data vs. Bad Data: How to identify?
3. Bad Data to Good Data: Pivot Copy Paste
4. Data clean-up: Are you helping Excel or Excel is helping you 😉
5. Best Practice: Always create a Table before creating Pivot Table
6. Data Clean-up: System Reports
7. Data clean-up: Are you helping Excel or Excel is helping you 😉
8. Miraculous Automatic Data Cleanup in Excel
9. Data Clean up using Flash Fill (In-Depth)
10. Consolidating crosstab data
11. Crosstab data consolidation using Power Query
12. Power Query : New Update
13. New version of Power Query released. Update it.
14. New use of Pivot Table – Data Quality Audit
15. Using Power Pivot instead of VLOOKUP
16. The amazing Calculated Items in Pivot Tables
17. Knowledge Pack: Data Accuracy in Excel
18. Analyzing Big Data? Use 64 Bit Office
19. Bad Data to Good Data – Combining Data which is Split into two rows
20. Data Split into Two rows – Cleanup using Power Query

Data Grouping

1. How to correct spelling mistakes while analyzing data?
2. Grouping Text in Pivot Tables
3. VLOOKUP based text grouping
4. Ageing analysis in seconds (Number Grouping in Pivot)
5. Ageing Analysis (Numeric Grouping in Pivot) – Part 2
6. Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3
7. Grouping Dates into months, quarters and years – in seconds!
8. Custom Date Grouping using VLOOKUP
9. Grouping with Date – Time Data using Pivot
10. Knowledge Pack – Grouping Data (Text, Numbers and Dates)

Data Analysis and Interpretation

1. Instant benefit: Try Distinct Count wherever you are using Count
2. New way of filtering reports – Slicers!
3. New way of filtering reports: Slicers – Part 2 (Filtering Filters)
4. Pivot: Running Totals and Ranking
5. Adding formulas OUTSIDE Pivot Tables = Inefficiency
6. Adding calculations WITHIN Pivot Tables
7. Pivot Table – Show Values As – Part 2
8. Pivot : Show Values As – Part 3
9. Introducing Power Pivot – do you need it?

Social Analytics

1. Facebook analytics using Excel 2013 Power Query: Without programming!
2. WhatsApp Group Chat Analytics using Excel and Power Query
3. WhatsApp Chat Analysis using Microsoft Word
4. WhatsApp Chat Analysis using Microsoft Word – Part 2
5. WhatsApp chat analytics: PowerShell Script
6. Marketing: Twitter Analytics using Excel

Data Visualization

1. Understanding Dashboards – the practical way.
2. Working with Excel data? There is a revolution happening. Learn Power BI
3. Using color to simplify interpretation and enhance analysis
4. How to highlight entire row based upon value in a single column
5. Show High, Medium, Low values in few clicks
6. High, Medium, Low – visual categories – In-Depth
7. Instant, Interactive 3D mapping of your data – Power Map
8. Using categories with 3D Map
9. Uncover hidden secrets: 3D, Time Animated Map using Power Map
10. High impact data presentations using Power Map Tour
11. Quick Analysis in Excel – Have you even noticed it?
12. Quick Analysis should NOT be done quickly!

Take Action

1. Don’t just make charts the way boss wants. Use Recommended Charts
2. Act Now: Discover one new and useful thing from familiar data

***