A dashboard is a visual display of critical information needed to achieve one or more objectives which fits entirely on a single screen. Dashboards are designed with a specific purpose and typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions.

Dashboards are made up of tables, charts, and numbers that allow you to track KPIs, metrics, and other data points in one visual, central place. They give you a high-level view of work, helping you make quick decisions and keeping everyone up to date. A dashboard’s visual nature simplifies complex data and provides an at-a-glance view of current status or performance in real time. They can be used in any industry, for almost any purpose. You could make a project dashboard, financial dashboard, marketing dashboard, and more.

Objective

✔ This course will train participants how to plan, design and create dynamic dashboard reports from raw data using Microsoft Excel 2016 features such as PivotTable, PivotChart, Slicer, Timeline, drop-down list, Conditional Formatting, Form Controls and dynamic functions.

✔ This course is for those who has no experience in creating dashboard. Participants will learn features and skills to build a dynamic information dashboard from source data quickly.

✔ At the end of the course, participants will know how to create dashboard to summarise, present and communicate data concisely and clearly, how to use visual effects to improve their dashboard design.

Outline

Chapter 1: Introduction to Dashboard
1.1 Overview of Dashboard
1.2 Dashboard Design Principles
1.3 Dashboard Design Guidelines
1.4 Overview of Data Sources

Chapter 2: Creating Quick and Effective Dashboard
2.1 Creating and Formatting Excel Table
2.2 Creating Calculated Column in Table
2.3 Create PivotChart from Excel Table
2.4 Using Chart Elements and Chart Styles
2.5 Hide Field Buttons from PivotChart
2.6 Aligning Pivot Charts and Formatting Axis
2.7 Create Combo Chart
2.8 Use Slicer to Filter PivotChart
2.9 Use Timeline to Filter PivotChart
2.10 Connecting Multiple PivotTables to Slicer
2.11 Connecting Multiple PivotCharts to Timeline

Chapter 3: Creating Dynamic Chart and Reference Table
3.1 Naming the Data Range
3.2 Using MATCH Function
3.3 Creating Drop-Down List
3.4 Using OFFSET Function
3.5 Defining Dynamic Named Ranges
3.6 Creating Dynamic Charts
3.7 Formatting the Charts
3.8 Creating Dynamic Table Lookup

Chapter 4: Using Form Controls on Dashboard
4.1 Introducing Form Control
4.2 Showing Developer Tab
4.3 Using SUMIF, COUNTIF and IF Functions
4.4 Creating Check Box Form Control
4.5 Creating Dynamic Stacked Column Chart
4.6 Creating Option Button Form Control
4.7 Creating Combo Box Form Control
4.8 Creating Scroll Bar Form Control

Who should attend

✔ This Intermediate to Advanced level course is for experience Microsoft Excel users who wish to explore how to create dynamic dashboard that enhance their report with easily understood and interactive data displays.

✔ Participants must have attended my course on “Dynamic Reporting with Excel PivotTable and PivotChart” or have knowledge and experience in using PivotTable and PivotChart.

✔ Participants must know how to create Table, perform custom sorting and filtering, using advanced functions such as IF, SUMIF, COUNTIF and create nested functions.

Methodology

Hands-on practical exercises with step-by-step instructions by the trainer.

Testimonial

Valene Ang's Profile

Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has broad experience in corporate IT training and course materials development.

Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.

Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc..