Building Dynamic Dashboards For Business Intelligence with EXCEL (IN-CLASS)
Registration
  • S$750
  • 2-days
  • Intermediate Level
Download Course

Covid-19 (Temporary Notice)
As part of our social responsibility, we will be limiting all courses to a maximum of 9 people. Our placement will be 1 person per desk, seating arrangements that allow provision of sufficient distancefrom personal contact. Precautionary measures for COVID-19 will be in place.

About this 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. 

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 version. It will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)

  • 21-22 Dec 2020
  • 22-23 Apr 2021
  • 02-03 Aug 2021
  • 22-23 Dec 2021
  • *SDF for all companies
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 building
  • Outline 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 sets
  • Insert 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 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.

About 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) in Singapore.

She has trained different levels of management executives from small-medium size organisations, government offices and multinational companies such as PSA International, Rio Tinto, SingTel, DFS Galleria Singapore, Canon Singapore, HP Singapore, CPF Board, MOE, HPB, HDB, National Environment Agency (NEA), Public Utility Board (PUB), Inland Revenue Authority of Singapore (IRAS), SPRING Singapore, Temasek Polytechnic, Republic Polytechnic, Nanyang Polytechnic, Singapore Expo, Changi Airport Group, DHL, Barclays Capital, Far East Organizations, Intrack Market Service (Malaysia), DENZA (ShenZhen) and many more.

She has also conducted customized training and one-to one coaching sessions for many companies’ executives on Microsoft Office 2003/2007/2010/2013/2016 versions. She is a much-sought-after trainer, judging by the very good evaluation she received from her past participants.

 

Comprehensive. Very good course and would recommend to anyone who wants to learn to use dashboards.

William Chan, Deputy Director, Singapore Polytechnic

Excellent! I will recommend my colleague to attend!

Phong Joo Tin, Marketing Manager, Singtel

This course was very effective in bringing across the various uses of Excel as an analytical tool. Important point was the need to plan ahead and to create the staging area on 'Data Models' to facilitate generation of dashboards for reporting

Sigit Gunawan, Director, Agri-Food & Veterinary Authority

Unique to this Course

Free Excel Templates and Spreadsheet

Cutomised case study for In-house companies to suits your business needs

Free comprehensive list of Excel resources

After Course support