Course Description
As data continues to grow at an exponential rate, data visualization helps the stakeholders in a company who aren’t data-savvy to digest and understand big data. Excel is an excellent tool to make powerful dashboards that can provide analysis, insights, and alert managers in a timely manner.
This training course covers all the functions of Excel and their usage and empowers the trainees to use the tool as efficiently as possible.
What Do Participants Learn?
- Understand Excel Dashboard Design Principles And Fundamentals
- Use Professional Data Visualization Techniques To Build Stunning And Informative Excel Dashboards
- Gain Visualization Tips And Tricks
- Communicate Effectively The Story Behind Your Data
- Master Effective Storytelling Techniques With The Best Selection, Design, And Presentation Of Data, Tables, Charts, Graphs, And Other Visuals.
- Display Key Trends, Comparisons, And Data Graphically For Greater Clarity And Faster Insights
- Learn Different Chart Types To Display Data In The Most Meaningful Way
- Learn Smart Techniques That Make Charting Work Fun And Productive
- Learn Various Advanced Features In Excel To Save Time
Who Should Attend?
- Analysts
- Managers
- Reporting professionals
- Business owners
- Executives
- Anyone responsible for preparing Excel-based dashboards, scorecards or KPI reports.
What Will the Learning Experience Include?
Phase: 1
Introduce
- Comprehensive pre-program activities include:
- Web-based information forms & surveys completed by attendee.
- Direct consultation with the attendee about the expectations.
- During the training, participants engage in data, activities, and conversations that lead to insight and knowledge.
- Participants learn from expert trainers who have both academic and business experiences.
- Highly applicable training content & instructive activities for adding depth to training topics.
- **A half-day site visit for integrating the experience & plan next steps. Opportunities to provide connections, ideas & support.
Phase: 2
Explore & Practice
Phase: 3
Apply
- Apply & sustain the learning experience by using this ongoing support:
- To ensure participant has new skills or behavior progress.
- Optional, fee-based mentoring & coaching with the trainer.
- Training materials & additional documents (e-books, pdf files, presentations and articles)
- Evaluate your training experience by giving us feedbacks and help us to reach our organizational goals.
- Participant's Evaluation
- Trainer's Evaluation
Phase: 4
EVALUATE
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