Knowledge Pack: Data Analytics

Data Analytics Knowledge Pack
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.


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