Sunday, October 16, 2016

Dashboard Reporting in Excel

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

No comments:

Post a Comment