Creating Dashboards with Excel Masterclass:.
Data visualisation for business reporting.
Course overview.
EXCEL DASHBOARD TRAINING MALAYSIAThere 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 MALAYSIA- 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
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
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
The programme.
EXCEL DASHBOARD TRAINING MALAYSIASESSION 1Introduction
- Overview of training aim, objectives and agenda
SESSION 2Useful 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 3Named 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 4Dashboard 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 5Preparing Dashboard Data
- Understanding your data
- Organising data
- Tools and tricks to clean data before using it to build a report or dashboard
SESSION 6Data 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 7Using Sparklines & Conditional Formatting
- Creating sparklines – line, column and win/loss
- Sparkline formatting and options
- Sparkline tips and tricks
- Applying conditional formatting
- Data bars, colour scales and icon sets
- Conditional formatting options
- Using symbols to enhance reporting
SESSION 8Automating 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 specifixc 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 9Advanced 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 10Pivottables & 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 11Hands-on Dashboard Projects
- Create dashboard reports with
- Excel functions and charts
- PivotTables and PivotCharts
- Datasets:
- HR dataset
- Banking dataset
- Bring Your Own Data (BYOD)
- Throughout the workshop, participants will work through practical exercises to enhance their knowledge of the tools available to build Excel dashboards
Your trainer.
EXCEL DASHBOARD TRAINING MALAYSIAAlvin Yong is a UK-registered Fellow Chartered and Certified Accountant (FCCA). He obtained his Master of Science in Professional Accountancy (Merit) in 2017 from the University of London.
To bring.
EXCEL DASHBOARD TRAINING MALAYSIAReviews from our Malaysian clients.
EXCEL DASHBOARD TRAINING MALAYSIA“Content is very good, notes are sufficiently detailed, presentation is clear and pace is good. Alvin entertained questions calmly, clearly, and with sufficient depth. Very knowledgeable and qualified for the training subject.”
“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”
“Alvin was well prepared, knowledgeable and easy to follow. Level of detail was where it needed to be and the documentation was very thorough. The course went quickly which was always a good sign.”
“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”
“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”
“The trainer is knowledgeable. He was helpful. He handled the class material well.”
“Very informative. Gave an excellent count of how-to's.”
“Alvin is a very people person. Sound knowledge in his area of expertise. He carried out the programme well.”
Related courses.
EXCEL · DATA VISUALISATIONReady when your team is.
Public runs across the region and live online — or we scope an internal cohort around your actual reports.
Enquire about in-house training