DURATION
2 days
INTRODUCTIONS
There is valuable information hidden in the sea of data. A picture is worth a thousand words - use Excel dashboard to present information visually and beautifully within a single page that allows users to understand what is going on and make business decisions. Excel dashboard reports allow managers to have a high-level overview of the business and help them make decisions. Excel is an excellent tool to make powerful dashboards that can provide analysis, insights, and alert managers in a timely manner.
TARGET AUDIENCE
This course is crucial for analysts, managers, reporting professionals, business owners, executives, and anyone responsible for preparing Excel based dashboards, scorecards or KPI reports.
PRE-REQUISITE
Participants should be comfortable with basic Excel tools and functions. At a minimum, participants should know how to navigate confidently in Excel, create and use simple formulas and link between workbooks.
LEARNING OUTCOME
By the end of the course, participants will:
- Understand Excel dashboard design principles and fundamentals.
- Create dynamic interactive charts.
- Understand advanced PivotTables and PivotCharts
- 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.
- Develop effective storytelling techniques using appropriate charts and graphs in dashboards.
Module 1: Introduction to Dashboard Report
- What is Dashboard Reports?
- Reports
- Dashboard
- Designing Dashboard Report with MS Excel
- Case Study – Training Service
Module 2: Charts
- Excel Chart Types
- Application of Chart Types
- Working with Chart Series
- Creating Chart
- Adding New Data Series
- Deleting Data Series
- Changing Data Series via Select Data Source Dialog Box
- Controlling a Data Series by Handling Data
- Using Secondary Axes
- Charting Techniques
- Adding lines and background elements to a chart
- Stacking and overlaying charts
- Merging charts on a chart sheet
Module 3: In-cell Charting Techniques
- Visualizing Data with Conditional Formatting
- Using Conditional Formatting Options
- Visualizing Data with Sparkline
- Use sparklines to show data trends
- What are sparklines?
- Why use sparklines?
- Create a Sparkline
- Customize sparklines
- Control which value points are shown
- Change the style of or format sparklines
- Show or hide data markers
- Show and customize axis settings
- Handle empty cells or zero values
Module 4: Interactive Charts
- Table and Slicers
- What is a Table?
- Creating Tables
- Sorting Data in a Table
- What is an AutoFilter?
- Creating a Custom AutoFilter
- Create a slicer to filter your table data
- Pivot Charts
- Using PivotChart Reports
- How to create a PivotTable Report
- How to create a PivotChart Report
- Slicing a PivotChart
- Timeline with PivotTable/PivotChart (Optional – Excel 2013 Only))
- Differences between PivotChart and standard charts
Module 5: Using Embedded Controls
- Types of Form Controls
- Applying form controls to Dashboard Reports
Programme scope, delivery arrangements and suitability are confirmed during enquiry.