Creating Dashboards with Excel Masterclass: Data Visualization for Business Reporting

Section One: Data Visualization Principles For Dashboard Design

• What Is A Dashboard?
• Purpose And Benefits Of Dashboards
• Understanding Dashboard Design Principles
• Layout, Color, And Display
• Common Mistakes When Building Dashboards
• Choosing The Right Data Visualization To Communicate Information Effectively
• Displaying Trends With Charts
• The Anatomy Of An Excel Chart
• When To Use A Secondary Axis
• Combining Two Chart Types
• Formatting Tricks
• Adding Icons And Images To Dashboards
• Preparing, Understanding And Organizing Your Data
• Tools And Tricks To Clean Data Before Using It To Build A Report Or Dashboard
• Naming Cells And Ranges
• Creating Excel Tables & Applying Table Names

Section Two: Advanced Charting In Excel

• In-cell Charts
• Step Charts
• Frequency Distribution
• Speedometers
• Bullet Graphs
• Waterfall Chart
• Pyramid Chart And Funnel Charts
• Actual Vs Budget (Target) Charts - Floating Markers
• Band Chart - Show Performance Against A Target Range
• Conditional Colors In A Column Chart
• Panel Charts

Section Three: Other Visualization Techniques In Excel - Using Sparklines And Conditional Formatting

• Creating Sparklines - Line, Column And Win/Loss
• Sparkline Formatting And Options
• Sparkline Tips And Tricks
• Applying Conditional Formatting
• Data Bars, Color Scales, And Icon Sets
• Conditional Formatting Options
• Using Symbols To Enhance Reporting

Section Four: Automating Your Dashboards 

• Using Structured References In The Formula
• Learn To Nest Functions Together To Create Robust Formulas
• Use If, Nested Ifs And Ifs For The Logical Test With Single Or Multiple Conditions
• Embed And Or Or Function In If For Robust Logical Tests
• Trap And Handle Errors With Iferror
• Use Choose To Return A Value From An Array-Based On The Index Number
• Aggregate Data With Single Criterion Using Sumif, Countif, Averageif
• Tabulating Information Using Multiple Criteria With Sumifs, Countifs, Averageifs
• Lookup And Retrieve Data From A Specific Column In A Table Using Vlookup
• Use Match To Find The Relative Numeric Position Of An Item In A Range
• Use Index To Extract A Value From A Table (Or Range)
• Index And Match - A Powerful Combo That Has More Flexibility And Speed
• Why Index Match Is Better Than Vlookup
• Using Row(S) And Column(S) For Indexing
• Rank And Sort Your Data Using Rank, Large And Small
• Use ‘database Functions’ Dsum, Dcount And Daverage To Filter Information In A List Or Database Based On Set Criteria
• Using Text And Concatenate To String Numbers And Text Together To Show In A Dashboard Or Chart.

Section Five: Refreshing Data

• Organizing Your Worksheets
• Raw Data Worksheet(S): Importing And Linking Data
• Calculation Worksheet And Dashboard Worksheet
• Steps In Refreshing Your Reports With New Data
• Case Study: Finance KPI Dashboards