Creating Dynamic and Insightful Dashboard with Excel

Dashboards is a very powerful tool that enable a user to quickly make sense of the raw data by presenting them in visually colorful charts and tables. It also gives valuable insights into the key performance indicators (KPIs) of the business. The most important aspect of dashboards is it is highly interactive where the user can filter and change its views. An Excel dashboard is a single page of interactive charts that display important information relevant to the business.

Course Objective

This 1 day course will train participants how to create quick and effective dashboard using PivotTable and PivotCharts, create data validation drop-down list to control a chart, use Offset function to create dynamic chart data series and dynamic lookup table, create Check Box, Option Buttons, Scroll Bar, Combo Box and Spin Button to control interactive dashboard.

Course Outlines

Chapter 1: Excel Dashboard Introduction

  1. What is Excel Dashboard?
  2. Dashboard Design Guidelines
  3. Preparing Dashboard Source Data

Chapter 2: Controlling Dashboard using Form Controls

  1. Understanding Types of Form Controls
  2. Creating Reference Tables for Charts
  3. Creating the Check Boxes Form Controls
  4. Creating the Option Buttons Form Controls
  5. Creating the Scroll Bar Form Controls
  6. Creating the Combo Box Form Controls
  7. Creating the Spin Buttons Form Controls

Chapter 3: Creating PivotTable Driven Dashboard

  1. Converting Source Data to Excel Table
  2. Creating PivotTables from Excel Table
  3. Creating, customizing and formatting PivotCharts
  4. Adding Slicers to Dashboard
  5. Adding Timelines to dashboard
  6. Creating New Slicer and Timeline Styles
  7. Connecting multiple PivotTables to Slicer and Timeline

Chapter 4: Creating Dynamic Chart and Table for Dashboard

  1. Defining Named Ranges for Dashboard Chart
  2. Creating Drop-down List to Control Chart
  3. Using the MATCH function to lookup data
  4. Creating Dynamic Ranges for Chart using OFFSET function
  5. Creating and formatting interactive charts
  6. Creating Dynamic Lookup Table using OFFSET function
Who Should Attend
This intermediate level course is for anyone who has a good knowledge of the workings within Excel and need to make formal presentations of their raw data by using interactive dashboard. The participants must have experience in creating PivotTable and PivotChart.

  • Accountant/Finance, Human Resource, Business, Sales, Data Analysts, Stock or Forex Traders and Business Owners
  • Excel Users whom are keen to learn how to create dynamic and interactive Dashboard reports and improve the design of their existing Excel reports
Participants have to bring laptop with Microsoft Excel 2016 / 365 / 2019 program for Windows.

This course is not for MAC version Excel program.

About The Trainer

Microsoft Excel Expert and Microsoft Certified Trainer: Valene Ang

Valene Ang is a Microsoft Certified Trainer and Master Instructor of Microsoft Office Specialist (MOS). She brings with her 20 years’ experience in corporate training and consultancy. Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outline and course material, assisting corporate clients in business data analysis and providing dynamic report solutions.

She had conducted many Microsoft Office workshops in Singapore and Malaysia. Her training focuses on providing practical solutions to real life Excel problems. As a Certified Trainer, Ms Ang has trained hundreds of executives, managers and directors from across medium sized organisations, government offices and multinational companies. Some of her high profile clients including PSA International, DFS Galleria Singapore, Neptune Orient Lines Limited (NOL), National Environment Agency (NEA), Inland Revenue Authority of Singapore (IRAS), CPF Board, Public Utility Board (PUB), Canadian High Commission, British High Commission, Temasek Polytechnic, Republic Polytechnic, Baxter Healthcare Pte. Ltd, and many more.

Hilton Hotel, Singapore
28 Jun 2021

Time

  9:00 am - 5:00 pm

Duration

1 Day

Cost

  S$500 (Nett)

Hilton Hotel, Singapore

Including Tea Breaks and Lunch
RSS
Follow by Email