CREATING DASHBOARDS WITH
EXCEL MASTERCLASS:
DATA VISUALISATION FOR BUSINESS REPORTING
Download brochure
Click on the link below to request for Excel Dashboard Training brochures
| 10-11 Aug | Kuala Lumpur |
| 10-11 Aug | Live Online Class |
Key Topics Covered
- PivotTables, PivotCharts & Slicers
- Excel functions like SUMIFS, VLOOKUP, INDEX & MATCH
- Advanced charting with combination charts
- Visual story-telling techniques
Workshop Overview (Excel dashboard training)
There is valuable information hidden in the sea of data. A picture is worth a thousand words - use Excel dashboard to present information visually and beautifully within a single page that allows users to understand what is going on and make business decisions. Excel dashboard reports allow managers to have high-level overview of the business and help them make decisions. Excel is an excellent tool to make powerful dashboards that can provide analysis, insights and alert managers in a timely manner.
A good Excel dashboard is:
- visually appealing for decision makers to understand key information clearly
- highly dynamic, allowing managers to easily dig into your data
- easy to update
Key Takeaways (excel dashboard training)
By the end of the course, participants will:
- Understand Excel dashboard design principles and fundamentals
- Create dynamic interactive charts
- Understand advanced PivotTables and PivotCharts
- Gain visualisation tips and tricks
- Learn how to build stunning and informative Excel dashboards
- Learn various advanced features in Excel to save time
- 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
- Develop effective storytelling techniques using appropriate charts and graphs in dashboards
Methodologies
Clear explanation of theories coupled with hands-on exercises for a firm grasp of Excel Dashboards through:
- Practical Examples
- Case Studies
- Short Exercises
- Group Discussions
Prerequisites
Participants should be comfortable with basic Excel tools and functions. At a minimum, participants should know how to navigate confidently in Excel, create and use simple formulas and link between workbooks.
Who Should Attend
This workshop is crucial for analysts, managers, reporting professionals, business owners, executives, and anyone responsible for preparing Excel based dashboards, scorecards or KPI reports. This training is highly recommended for:
- Directors
- General Managers
- Chief Financial Officers
- Finance Managers
- Accountants
- Marketing Managers
- Sales Managers
- HR Managers
- Business Owners
- Anyone who deals with business, management and financial reporting
Software Used
The course is demonstrated using Microsoft Excel 2016, but participants can use Excel 2010 or 2013 if they prefer.
ProgramME Agenda (excel dashboard training)
Session 1: INTRODUCTION
- Overview of training aim, objectives and agenda
Session 2: USEFUL EXCEL TIPS & TRICKS
- Centre across selection
- Keyboard shortcuts for selecting cells and ranges
- View multiple worksheets at the same time
- Protect cells, sheets and workbooks
Session 3: NAMED RANGES AND EXCEL TABLE
- Naming cells and ranges
- Creating Excel tables
- Applying table names
- Using structured references in formula
- Refreshing tables with new data
Session 4: DASHBOARD DESIGN PRINCIPLES
- Purpose and benefits of dashboards
- Understanding dashboard design principles
- Layout, colour and display
- Create your own colour theme
- Common mistakes when building dashboards
- Dashboard checklist
Session 5: PREPARING DASHBOARD DATA
- Understanding your data
- Organising data
- Tools and tricks to clean data before using it to build a report or dashboard
Session 6: DATA VISUALISATION – PICKING THE RIGHT DISPLAY
- The anatomy of an Excel chart
- Choosing the right data visualisation to communicate information effectively
- Displaying trends with charts
- Formatting tricks
- Adding icons and images to dashboards
Session 7: USING SPARKLINES & 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
Session 8: AUTOMATING DASHBOARDS WITH FUNCTIONS
- Learn to nest functions together to create robust formulas
- Use IF, Nested IFs and IFS (Excel 2016) for logical test with single or multiple conditions
- Embed AND or OR function in IF for robust logical tests
- Trap and handle errors produced by other formulas or functions with IFERROR
- 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 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 - powerful combo that has more flexibility and speed
- Why INDEX MATCH is better than VLOOKUP
Session 9: ADVANCED CHARTING TECHNIQUES
- Combining two chart types with combination charts
- When to use a secondary axis
- Actual vs budget (target) charts – floating markers
- Band chart - show performance against target range
- Conditional colours in column chart
- Frequency distribution
- Panel charts
- Speedometer gauge charts
- Bullet graphs
- Use Error Bars and XY Scatter to show actual against multiple benchmarks
- Data labels formatting techniques
- Add custom data labels with Value From Cells
- Creating drop-down (data validation) list
- Creating dynamic text boxes
Session 10: PIVOTTABLES & PIVOTCHARTS
- Using calculated items to sum other items in the same field
- Using calculated columns to perform calculations on other fields
- Create custom calculations for
- Difference From and % Difference From
- % of Row and % of Column
- Running Total In
- Create interactive dashboards with PivotCharts
- Hide or show field buttons in PivotCharts
- Use GetPivotData function to extract data from pivot tables
- Add slicers and timeline for interactivity
- Slicer report connections to link multiple PivotTables
Session 11: HANDS-ON DASHBOARD PROJECTS
- Create dashboard reports with
- Excel functions and charts
- PivotTables and PivotCharts
- Datasets:
- HR dataset
- Banking dataset
- Bring Your Own Data (BYOD)