Creating Dashboards With Excel Masterclass

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):

• 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.

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)