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