Overview
The world is an ocean of data. The trick is knowing how to find the valueable information in the center of it.
This course is designed to help participants create clear and detailed visualizations of data using Excel Dashboards that can lead to effective business decision-making. Dashboard reports allow managers to have a high-level overview of their business' vital signs through comprehensive analysis and insights. The more visually appealing and interactive the data, the easier it is to act on it.
This training will cover key concepts such as Excel Dashboard design principles, Charting Techniques and Conditional Formatting. All tools participants can use to create more dynamic and interactive dashboards that can help organizations make the best out of their data.
Learning Outcomes
By the end of the course, participants will:
• Understand Excel dashboard design principles and fundamentals.
• Create dynamic interactive charts.
• Understand advanced Pivot Tables and Pivot Charts.
• Gain visualization tips and tricks.
• Learn how to build stunning and informative Excel dashboards.
• Learn various advanced features in Excel to save time.
• Display key trends, comparisons and data graphically for greater clarity and faster insights.
• Learn different chart types to display data in the most meaningful way.
• Learn smart techniques that make charting work fun and productive.
• Create interactive controls that enable users to customize their view.
Who Must Attend
This workshop is highly recommended for:
• Directors
• General Managers
• Chief Financial Officers
• Finance Managers
• Accountants
• Marketing Managers
• Sales Managers
• HR Managers
• Business Owners
• Anyone who deals with business, management and financial reporting
Course Details
COURSE DAY 1
Module 1: INTRODUCTION
Overview of training aim, objectives and agenda
Module 2: DASHBOARD DESIGN PRINCIPLES
• What is a dashboard?
• Purpose and benefits of dashboards
• Understanding dashboard design principles
• Layout, color and display
• Common mistakes when building dashboards
Module 3: PREPARING DASHBOARD DATA
• Understanding your data
• Organizing data
• Tools and tricks to clean data before using it to build a report or dashboard
Module 4: DATA VISUALIZATION – PICKING THE RIGHT DISPLAY
• The anatomy of an Excel chart
• Choosing the right data visualization to communicate information effectively
• Displaying trends with charts
• When to use a secondary axis
• Combining two chart types
• Formatting tricks
• Adding icons and images to dashboards
Module 5: USING SPARKLINES & CONDITIONAL FORMATTING
• Creating sparklines – line, column and win/loss
• Sparkline formatting and options
• Sparkline tips and tricks
• Applying conditional formatting
• Data bars, color scales and icon sets
• Conditional formatting options
• Using symbols to enhance reporting
COURSE DAY 2
Module 6: ADVANCED CHARTING TECHNIQUES
• In-cell charts
• Step charts
• Actual vs budget (target) charts – Floating Markers
• Band chart - show performance against target range
• Conditional colors in column chart
• Frequency distribution
• Panel charts
• Speedometers
• Bullet graphs
• Waterfall chart
• Pyramid chart and funnel chart
Module 7: NAMED RANGES AND EXCEL TABLE
• Naming cells and ranges
• Creating Excel tables
• Applying table names
• Using structured references in formula
• Refreshing tables with new data
Module 8: AUTOMATING DASHBOARDS WITH FUNCTIONS
• Learn to nest functions together to create robust formulas
• Use IF, Nested IFs and IFS (Excel 2016) for logical test with single or multiple conditions
• Embed AND or OR function in IF for robust logical tests
• Trap and handle errors produced by other formulas or functions with IFERROR
• Use CHOOSE to return a value from an array based on index number
• Aggregate data with single criterion using SUMIF, COUNTIF, AVERAGEIF
• Tabulating information using multiple criteria with SUMIFS, COUNTIFS, AVERAGEIFS
• Lookup and retrieve data from a specific column in table using VLOOKUP
• Use MATCH to find the relative numeric position of an item in a range
• Use INDEX to extract a value from a table (or range)
• INDEX and MATCH - powerful combo that has more flexibility and speed
• Why INDEX MATCH is better than VLOOKUP
• Using ROW(S) and COLUMN(S) for indexing
• Rank and sort your data using RANK, LARGE and SMALL
• Use ‘database functions’ DSUM, DCOUNT and DAVERAGE to filter information in a list or database based on set criteria
• Using TEXT and CONCATENATE to string numbers and text together to show in a dashboard or chart.
Module 9: BUILDING INTERACTIVE CHARTS AND DASHBOARDS CONTROLS
• Using form controls: Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Scroll Bar, Label and Group Box
• Set up dynamic data validation list
• Creating dynamic named range with OFFSET and COUNTA
• Creating dynamic named range with INDEX
• Using the INDIRECT function
Module 10: BUILDING DYNAMIC CHARTS
• Selecting data with a combo box (or data validation list) for dynamic Excel charting
• Using a check box to show or hide data or trend lines
• Scrolling a dynamic excel chart through time
• Using helper columns to manipulate your data
• Linked textbox for dynamic chart titles
• Tricks to avoid crashing lines in line chart
• Linking legends to cell contents
Module 11: PUTTING IT TOGETHER
Creating your dashboard
Methodology
1. Practical and Hands On.
2. Question & Quick Discussion
3. Coaching – trainer will go around helping participants with specific questions (applicable if the class is a small size)
4 . Post Test – By the end of the training, participants will take a Excel Advanced test as a benchmark of their achievement in 2 days
Course Leader
B.UGESH NAIR
Data Analyst & Professional Trainer
HRDF Certified Trainer
Google Certified Educator
Training of Trainers in Entrepreneurship & Skill Development
Certified Financial Planner (CFP)
HRDF Certified Train-The-Trainer (TTT)
Training of Trainers in Entrepreneurship & Skill Development
Bsc (Hons) Computer Science (UK)
International Advanced Diploma in Computer Engineering
Diploma in Electronic and Information Technology
A technically experienced trainer, who has a proven track record of training corporations on Data Analytics using easy-to-understand methods to crunch huge data. Vast experience using Excel, PowerBI & Data Analytic Tools. Ugesh enjoys helping his clients understand the stories that their data tell about their business, thus enabling them to make informed, data-driven decisions to propel future growth.
Ugesh Nair is a disciplined thinker who is capable of working across organizational boundaries in a very demanding and high-output environment. He comes to you with a background in effectively delivering integrated IT solutions, along with a genuine passion for Training the organization to excel in the growth of the team. As a true all-rounder, he is able to carry out assigned projects and tasks in a timely, diligent, and professional manner. Ugesh has been conducting trainings since 2014.
(Full Trainer Profile available upon request)
OR
We have a faculty of equally talented trainers who can cater to your organisation's specific needs in the event of substitution.
For training and advisory services, feel free to contact us at info@itrainingexpert.com or call us at +603 8082 3707 | +603 8074 9056 | +6012 6869 628 | +6018 2175 123
Testimonies
Investment
In house arrangement only.
Click here to Request for In house Quotation
Payment mode:
Payment must be 100% upfront upon confirmation.
