Microsoft Excel Dashboards & Reports
This is 2-Day Excel Dashboards and Reports training help you create a dynamic “dashboard” or “report” to present timely summary data or metrics/KPIs. You can use Excel dashboard to filter data and switch views. Dashboard may contain tables, charts, data validation, pictures and other visualizations of data. It displays key trends, comparisons and data graphically provide user a high-level overview of the business and help you make decisions.
Objective:
At the end of the training, participants are able to:
Objective:
At the end of the training, participants are able to:
- Understand excel dashboard design principles
- Prepare excel dashboard data model
- Understand advanced PivotTables and PivotCharts
- Add interactive controls to show various views
- Automate repetitive task and processes with Macros
- Create eye-catching visualizations
- Learn different chart types to display data
- Create impressive dashboards and what-if analyses
Outline:
-
Understanding Dashboard Design Principles
- What is a dashboard?
- Purpose and benefits of dashboards
- Principles of great dashboard design
- Common mistakes when building dashboards
-
Preparing Dashboard Data Model
- Understand the key concept of dashboard data model
- Organizing the data
- Creating Excel tables
- Applying table names
- Refreshing tables with new data
-
Chartless Visualization Techniques
- Create sparklines
- Understanding conditional Formatting
- Managing the Conditional Format Rules
- Using Symbols
-
Automating Dashboard with Functions
- Use IF, nested IFS and IFS for logical test
- Embed AND or OR function in IF for logical test
- Use IFERROR function to handle errors
- Use VLOOKUP function to retrieve data
- Use MATCH and INDEX functions to pull data from a table
- Why INDEX and MATCH is better than VLOOKUP
-
Advanced Pivot Table Techniques
- Use calculated fields to create your own formulas
- Use calculated items to sum other items within the same field
- Add slicers and timeline for interactivity
- Use GetPivotData function to extract data from pivot tables
- Slicer report connections to link multiple pivot tables
- Create custom calculations for
- Difference from and % Difference From
- % of Row and % of Column
- Running Total in
-
Advanced Charting Techniques
- Band chart-show performance against target range
- Actual vs budget charts
- Combination two chart types
- Conditional formatting for column chart
- Frequency distribution
- Thermometer chart
- Doughnut chart
- Pareto Chart
- Bullet graphs
- Panel charts
- Speedometer gauge charts
-
Dashboard Macros
- Simple and complex Macros
- Dynamic Dashboards using Macros
-
Creating Dynamic Dashboards with Excel for Financial Reporting
- Complete Financial KPI Dashboard
- Best Practices in Dashboard Design
-
Hands-On Dashboard Projects
- Create excel dashboard reports with
- Excel Functions and charts
- Excel Table
- PivotTables and PivotCharts
- Macros
-
Datasets:
- Sales dataset
- HR dataset
- Financial dataset
Duration:
2 days (14 hours)
Target Audience:
This course is aimed for Excel users who responsible to prepare Excel based dashboards, scorecards or KPI reports. This training is highly recommended for analysts, managers, reporting professionals, and anyone who deals with business, management and financial reporting.
Methodology:
Demonstration, practical learning, case discussion and exercises.
Prerequisite(s) or equivalent knowledge:
Participants must have basic knowledge of Excel Charts, PivotTables, Functions and Formulas.
Methodology:
Demonstration, practical learning, case discussion and exercises.
Prerequisite(s) or equivalent knowledge:
Participants must have basic knowledge of Excel Charts, PivotTables, Functions and Formulas.
