Building Dynamic Dashboards For Business Intelligence with EXCEL

About the course

*Eligible for SkillsFuture Credit

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 is a case study-based course. The course will train participants how to create dynamic dashboards using Excel lists or data downloaded from external database, how to create relationship between two tables, how to use advanced Excel built-in functions and reporting features to enhance user interactivity with the dashboard, how to build and manage an efficient data model that feeds the dashboard with updatable business data, and also how to use Macro to manipulate charts and tables, compute and update formula results automatically.

At the end of the course, a case study related to the topics learned will be given. Participants will have chance to discuss the case study scenario and come out with the solution. This case study helps participants to have better understanding of the topics learned and know how to apply the skills in their work. Model answers of the case study and samples of Excel worksheets will be given to participants for reference and revision after the course.

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 advanced level course is aimed at anyone who does not have experience in building Excel dashboard.
  • Managers, executives and marketers who work in various industries will benefit from the course.
  • Participants must have some intermediate level Excel knowledge especially PivotTable and PivotChart, IF, SUMIFS, COUNTIFS, VLOOKUP, INDEX, MATCH functions.
  • Participants must use Excel 365 frequently.
  • This course is not for those who never use or create PivotTable and PivotChart.

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

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 sets
• Insert in-cell line Sparklines, column Sparklines and Win/Loss Sparklines

Creating Slicer Driven Dashboard (Case Study)

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

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 check boxes 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 investigatie 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 labels
• Use 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.

Pre-requisites


Please read carefully...

Prerequisite:
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.

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

Testimonials
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
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
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