Visualizing Data and Building Dashboards in Excel & Power BI

Overview

Dive into a comprehensive video tutorial on data visualization and dashboard creation in Excel and Power BI, led by Excel MVP and Highline College Professor Mike Girvin. Learn the fundamentals of data visualization, including Edward R. Tufte's principles, table formatting rules, and conditional formatting. Master various chart types such as column, bar, line, and scatter charts. Explore advanced techniques for creating dashboards using PivotTables, Power Query, and DAX formulas. Discover how to build dynamic dashboards with array formulas and PivotTables in Excel, and leverage Power BI Desktop for sophisticated data modeling and online sharing. Gain practical tips on formatting, updating, and optimizing your dashboards across different platforms. Download the provided files to follow along and apply your new skills to real-world data visualization challenges.

Syllabus

1. Introduction
2. Topics
3. Why Visualize? Table or Visualization?
4. Edward R. Tufte and High Data/Ink Ratio Rule and “No Chart Junk Rule”
5. Tables Formatting Rules
6. Conditional Formatting
7. Column and Bar Charts to compare differences across categories
8. Cross Tab Chart: Clustered Column / Bar & Stacked Column / Bar
9. Line Chart: 3 examples
10. Line Chart and IF Function for line chart that shows revenue and emphasizes promotions for company.
11. X-Y Scatter Chart: Relationship Between Two Numbers
12. Excel Comprehensive Dashboard example with PivotTables and Charts. Print Setup to allow printing.
13. PivotTable Custom Style
14. Power BI Comprehensive Dashboard example with Power Query, Data Modeling, Time Dimension Table, DAX Formulas and Publishing and Sharing Online.
15. Power Query to take data from multiple worksheets in multiple Excel files and transform it into a single table. Learn how to use Excel.CurrentWorkbook function, The Merge feature to lookup prices, and the Add Column / From Number / Standard feature to automatically generate Calculated Custom Columns.
16. Array Formula Dashboard Tips: Conditional Formatting and Dynamic Charts
17. PivotTable Dashboard Tips: PivotTable to create Dynamic Dashboard Title
18. Import Excel Power Query query into Power BI Desktop
19. Power BI Desktop Dashboard Tips: Formatting Date Slicer, Invert Picture, Conditional Formatting
20. Add New File To Folder and Update Three Dashboards: Array Formula, PivotTable and Power BI
21. Summary and Conclusions
22. Closing and Video Links