Microsoft Excel Dashboard Reporting for Beginners
Step by Step Guide on how to Visualize Your All Data, At A Glance
Take Your Excel Skills to the Next Level. Data Modelling and Visualization
Dashboards are the key in today’s data-driven environment. A Dashboard is a one-pager report that would instantly answer important questions such as which regions are performing better and which products should the management focus on. Dashboards could be static or interactive (where the user can make selections and change views and the data would dynamically update).
So, what exactly is an Excel Dashboard? An Excel Dashboard is an amazing tool when it comes to tracking KPIs, comparing data points, and getting data-backed views that can help management to make sound decisions.
- Do you find pulling and manipulating data from multiple sources manually challenging?
- Have you ever wondered about how you could generate relevant information and gain insights from your data using Excel?
- Do you like to create dynamic report using Excel for data visualization and analysis?
If you are trying to find solutions for the above questions, this is the right course for you. In this course, you will learn how to create dynamic dashboards from scratch using various tools and functions in Microsoft Excel.
- The best practices to follow while creating a Dashboard
- The features and tools you can use in Excel to create an interactive Dashboard
- Create a quick and effective Excel Dashboard using PivotCharts, Slicers and Timelines
- Use drop-down list with functions such IF, MATCH and OFFSET to control a dynamic chart
- Add Check Boxes, Option Buttons, Combo Boxes and Scroll Bars to Excel Dashboard
- Create summary reference table using IF, COUNTIF, SUMIF and AVERAGEIF functions
Who Should Attend?
This workshop is highly useful for experienced Excel users who wants to use Excel to visualize the Key Performance Indicators (KPIs) and other strategic data for your organization at a glance. Learners must have basic PivotTable knowledge.
- Accountant/Finance, Human Resource, Business, Sales, Data Analysts, Stock or Forex Traders and Business Owners
- Experience Excel Users who already know how to create PivotTable and PivotChart as well as create nested functions and complex formulas.
- Excel Users whom are keen to learn how to create dynamic and interactive Dashboard reports and improve the design of their existing Excel reports
- Excel Users who wants to know more about Excel power lookup functions to create dynamic data lists and explore Excel form controls
Note: This workshop is only for Windows version Microsoft Excel users. Participants are required to bring a laptop equipped with Microsoft Excel 2016/ 2019/ 365 version.
The workshop was well-planned and designed. Well-versed in the subject taught. One of the most useful course I have attended; my teammates and I will try to sign up for similar courses conducted by Ms Valene. She is an excellent trainer, She is patient, explains concepts well and paces the class well. Excellent facilitator. Ministry of Trade and Industry
Trainer’s teaching is excellent. The workshop is very good, practical examples and exercises. One of the best Excel courses I’ve ever attended thus far. The trainer is very knowledgeable and have very good rapport with the group. Health Promotion Board
Perfect pace. I have learnt a lot – Everything is useful. Enjoyed your lessons, Valene. Thank you. Learnt an in depth with regards to excel as my works uses a lot of excel. Overall is good. Trainer is very knowledgeable and experience. Ministry of Law
MS 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.
Module 1: Introducing Excel Dashboard
You will learn the reasons and benefits of using Excel Dashboard, design principles and guidelines to build an effective dashboard, create an outline for Dashboard, select the right elements for dashboard, select and clean the data sources, create reference tables and PivotTables from data sources.
- Understanding Excel Dashboard
- Understanding Dashboard Design Guidelines
- Preparing Data Sources for Dashboard
- Preparing Reference Tables for Dashboard
Module 2: Creating an Excel Dashboard
You will learn how to create an Excel Dashboard using various features and advanced functions available in Excel.
- Defining Named Ranges for list items and chart data
- Creating Drop-down List for Chart and use the MATCH function to lookup data
- Creating Dynamic Ranges for Chart using nested function that consists of IF and OFFSET functions
- Creating and formatting interactive charts
Module 3: Creating Quick and Effective Dashboard
You will learn how to create an effective dashboard quickly using Excel reporting tools such as Table, PivotTable, PivotCharts, Slicer and Timeline.
- Creating and formatting Excel Table, remove duplicate records and create calculated columns in the table
- Creating PivotCharts and PivotTables from Excel Table
- Create Combo chart, customize and format the Combo chart using Chart Tools
- Adding Slicers and Timelines to dashboard, format Slicer and Timeline and connect multiple PivotTables to a Slicer or Timeline
Module 4: Adding Controls to Excel Dashboard
You will learn how to use different types of form controls in Excel such as check box, combo box, option button and scroll bar to control your dashboard interactively. You will also learn how to use advanced functions such as IF, SUMIFS, COUNTIF and OFFSET functions to create reference tables for various form controls and create Stacked Column chart.
- Understanding Form Controls
- Controlling Dynamic Chart with Check Boxes
- Controlling Dynamic Chart with Option Buttons
- Controlling Dynamic Chart with Scroll Bar
- Controlling Dynamic Chart with Combo Box