Building Dynamic Dashboards For Business Intelligence with EXCEL

About the course

A business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators for an enterprise. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen. They may be tailored for a specific role and display metrics targeted for a single point of view or department. The essential features of a BI dashboard product include a customizable interface and the ability to pull real-time data from multiple sources.

This course is customized for corporate managers and executives who are required to prepare various types of impressive executive management reports and business key performance reports. The main objective of this course is to provide the participants invaluable insights and equip them with advanced charting and reporting skills to professionally present their reports.

Who is this course for?

This fast pace Intermediate-Advanced level course is aimed at frequent Excel users who have to prepare interactive, informative and maintenance-free reports for management to analyze key pieces of business information. 

Course content

Day 1

Understanding Excel Dashboards

A business dashboard gives business leaders valuable information and provides a quick outlook of all the most valuable numbers, which allows them to perform an accurate business analysis and make actionable decisions.
Participants will learn about:
– Dashboard definition and design principles
– Fundamentals on Excel Dashboard
– buildingOutline the structure of a dashboard
– Design dashboard layout using frames

Creating Slicer Driven Dashboard

Slicers are an easy-to-use filtering component that give you the ability to quickly filter your Dashboard and its respective views as well as add an attractive and interactive user interface to your dashboards.
Participants will learn how to:
– Prepare dynamic data source using Excel Table
– Create relationship between two table
– Create PivotTables using Data Model
– Create and customize PivotChart
– Create slicer and Timeline, connect Slicer and Timeline to multiple PivotTable
– Create Combo chart and create new Slicer style

Using Advanced Charting Techniques

The benefit of using advanced charts in Excel is they enable you focus on a specific data and make it easily understandable. You can use them in your dashboards to provide visualizations that help focus attention on key trends, comparisons, and exceptions.
Participants will learn how to:
– Create Waterfall chart, Histogram and Pie of Pie charts
– Show forecasting trend with Trendline
– Compare different targets with chart
– Create in-cell data bar and icon setsInsert in-cell line Sparklines, column Sparklines and Win/Loss Sparklines

Presenting Your Dashboards

Excel and PowerPoint are perfect partners for presenting dashboard on big screen. Simple dashboard on multiple slides will work better than one complex dashboard on a single slide.
Participants will learn how to:
– Create an embedded Excel dashboard in PowerPoint slide
– Create a linked Excel dashboard in PowerPoint slide
– Convert chart into picture
– Capture a snapshot of chart with Screenshot
– Use Camera tool and Linked Picture option to take live picture of a cell range

Day 2

Adding Interactive Controls to Dashboard

Excel offers a set of Form controls such as option buttons, combo boxes and checkboxes that allow you to add interactivity to your dashboard and make it easier for users to interact with the charts and tables on dashboard.
Participants will learn how to:
– Set form controls properties
– Use various form controls such as button, combo box, check box, scroll bar, spin button, group box and option button to manipulate charts
– Define dynamic range names
– Create summary tables using OFFSET, INDEX and MATCH functions
– Create charts using names
– Control chart series using data validation drop-down list.

Creating Analytical Dashboards

Analytical dashboards are designed to help decision makers, executives and senior leaders, establish targets based on insights into historical data, set goals and understand what and why something happened so that appropriate changes can be implemented. For example, a Sales dashboard enable you to monitor and investigate different aspects of sales process from the beginning until the end of its lifecycle and it’s useful to showcase sales data during annual meetings or quarterly meetings.
Participants will learn how to:
– Prepare dynamic data source using defined name and Table
– Create dynamic table in dashboard using CHOOSE, SUMIFS and other functions
– Create dynamic chart labelsUse slicer to control data labels
– Create slicer and format slicer

Using Macro Buttons to Control Dashboard

Dashboard automation in Excel can be achieved using macro buttons. Macro Recorder is a handy tool for a beginner who is unable to write the VBA (Visual Basic for Applications) code by hand to create macros.
Participants will learn how to:
– Record macro using Macro Recorder and edit macro using VBA (Microsoft Visual Basic for Application)
– Assign macro to a button on a worksheet and to a command button on the Quick Access Toolbar
– Record macro to sort and filter tables and charts on a dashboard

Case Study

Participants will have chance to design a user-friendly dynamic dashboard. The case study helps participant to apply the skills and functions they learned from the training so that they know exactly how to build interactive and informative dashboards in their work.


Please read carefully...

You should be a confident Excel user who has experience in using and maintaining Excel reports and be able to create Excel table-driven reports and charts. A pre-requisite questionnaire will be provided to you to ensure the suitability and level is right for you.

The course will actively be using Excel 365 subscription versionIt will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)

Valene Ang

Valene Ang

Valene Ang is a Certified Microsoft Training Specialist who has more than 19 years of training experience and working with companies to improve their data processes. Her qualifications include a Bachelor’s in Business Computing, Microsoft Certified Trainer (MCT), Certified Instructor of Microsoft Certified Application Specialist (MCAS), and Master Instructor of Microsoft Office Specialist (MOS). Valene also holds an Advanced Certificate in Training and Assessment (ACTA) – a national qualification awarded by WDA (Workforce Development Authority)…

The course has been extremely useful in building my understanding for Power BI. Will be handy for my day-to-day work.
Tan Kok Joo, Director
Bank of Singapore
Mind-blowing professional training course that helped my team with techniques to structure/plan a good presentations before diving into it.
Valerie Lee, Head, Corporate Development
Sembcorp Industries Ltd
This training broaden my view on what value I can contribute to my company and the goals I need to set. Thank you.
Vony AgustianaKhristanti, Purchasing Officer
A great course that is well-structured and relevant to my work. Definitely opened my eyes to new and better ways of analysis data!
Crystal Lee, Marketing Manager
Relevant and insightful and know-how for engaging audience effectively. I like the pace of the course coupled with plenty of hands-on exercises
Daniel Ch’ng, Project Director
Great 2 days lesson to learn how to fully manipulate Powerpoint and see the application in a different light.
Melvin Kwong, Account Manager