Knowledge Pack: Data Analytics

image 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
  21. Data Clean Up: Column groups into tabular columns

Data Grouping

  1. How to correct spelling mistakes while analyzing data?
  2. Grouping Text in Pivot Tables
  3. Ageing analysis in seconds (Number Grouping in Pivot)
  4. Ageing Analysis (Numeric Grouping in Pivot) – Part 2
  5. Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3
  6. Grouping Dates into months, quarters and years – in seconds!
  7. Custom Date Grouping using VLOOKUP
  8. Grouping with Date – Time Data using Pivot
  9. 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


10 thoughts on “Knowledge Pack: Data Analytics

  1. Pingback: Why do I blog in random order? | Efficiency 365

  2. Pingback: Beyond Pivot Table – unbelievable analytics using Excel (10 min video) | Efficiency 365

  3. Pingback: Office 365 Worst Practices – Part 6 – Power BI tools not deployed / used | Efficiency 365

  4. Pingback: List of Knowledge Packs | Efficiency 365

  5. Pingback: Instant benefit: Try Distinct Count wherever you are using Count | Efficiency 365

  6. Pingback: Simplifying Pivot Tables by hiding unwanted columns (using Power Pivot) | Efficiency 365

  7. Pingback: Revolutionary new method of making amazing reports – without Excel – for free | Efficiency 365

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s