Dashboard Reporting in Excel
Course overview
When you
think of data, do you think of endless rows and columns in spreadsheets? This
interactive and practical learning course shows you how to make the most of
your data by exploring new ways to conceptualize and present key information
and produce reports that are impressive and easy to understand. Learn how to:
- Develop
interactive data dashboards
- Creative
stunning visuals to represent trends
- Define
KPIs and measure business performance against goals
This
course is designed for accountants & auditors, finance professionals and business
& financial analysts. Suggested audience titles include accounts manager,
finance manager, finance director, CEO, CFO, audit manager, MIS manager etc.
Pre-requisites
- Microsoft
Excel basic working knowledge
- Business
acumen
Take away
- Organize
and cleanse data for reporting
- Prevent
invalid data entry and control data input
- Use
slicers and filters to control dashboards
- Design
visual dashboards with charts
- Learn
advanced chart types, pivot charts, scatter graph & combo charts
- Use
visual formatting features like conditional formatting, spark lines, traffic
lights and conditional formatting
Duration
2 days
Course outline
Day 1
|
Advanced Pivot tables
|
Pivot table basics
|
Calculating cumulative % and
running totals
|
Conditional formatting in
pivot tables
|
Linking multiple pivot
tables with slicers
|
Performing yearly,
quarterly and monthly analysis
|
Understanding Dashboards
|
What is a Dashboard
|
Sample dashboards
|
Design concepts &
best practices
|
Data validation, named
ranges & tables
|
Named ranges in Excel
|
Excel range vs tables
|
Finding invalid data
& formula errors
|
Understanding data
validation
|
Preventing incorrect data
entry
|
Custom error message for
data entry
|
Using drop-downs
|
Using formulas in tables
|
Using names in formula to
simplify report building
|
Conditional formatting,
spark lines & shapes
|
Understanding conditional
formatting
|
Data bars, color scales
& Icon sets
|
Identifying outliers,
top/bottom values
|
Using Spark lines & columns
|
Using formulas in
conditional formatting
|
Day 2
|
Advanced charts
|
Chart basics
|
Combo charts
|
Customizing charts
|
Dynamically update chart
range
|
Scatter graph charts
|
Using Pivot charts
|
Advanced Pivot tables
|
Breaking apart pivot
table for customized dashboard
|
Custom calculation in
Pivot tables
|
Generating multiple sheet
reports from Pivot tables
|
Pareto analysis with
pivot tables - top N values
|
Retrieving values from
pivot table
|
Advanced Excel formulas
|
Logical functions (IF,
AND, OR, NOT)
|
Lookup function II
(GETPIVOTDATA, OFFSET)
|
Lookup functions
(VLOOKUP, HLOOKUP, INDEX, MATCH)
|
Text functions (LEFT,
RIGHT, MID, LEN, CONCATENATE, FIND)
|
Introduction to array
formulas
|
Using form controls
|
Combo box & check box
|
Spin button & option button
|
Using form controls to
control dashboard
|
