Creating Dashboards in Excel

Course Description

In this 9-hour workshop, learn the essential skills and techniques needed to create interactive dashboards in Excel. You will learn how to effectively present data in a visually appealing and easy-to-understand format using pivot charts and slicers. This course deals with complex content at a fast pace.

Participant Outcomes

Upon successful completion of this course, you will be able to:

Understand the purpose and benefits of dashboards.
Recognize key components that make-up a well-designed dashboard.
Utilize the data dictionary to better understand the data.
Summarize data efficiently using pivot tables.
Duplicate data and use it to create inherent relationships between pivot tables.
Create and customize pivot charts.
Organize charts and graphs to create meaningful dashboards.

Course Content

Lesson 1: Introduction

Topic A: Course Structure, Pre-requisites and Other Helpful Information
Topic B: What is a Report and a Dashboard?
Topic C: How to Create a Dashboard Step by Step
Topic D: Understand the Data First
Topic E: Utilize a Data Dictionary
Topic F: Importance of Data Cleaning

Lesson 2: Preparing Data for Data Analysis Using Pivot Tables

Topic A: Convert a Range into a Table
Topic B: Create a Pivot Table
Topic C: Re-arrange Fields to Aggregate Data Using the Four Pivot Table Areas (Values, Row Labels, Column Labels and Report Filter)
Topic D: Select the Appropriate Chart Type for the Data
Topic E: Insert Slicers to Slice and Dice Data
Topic F: Use Other Options to Aggregate Data Using Excel Functions

Lesson 3: Activity – Build Pivot Tables and Pivot Charts

Topic A: YTD Sales by Store
Topic B: Monthly Sales Trend by Store
Topic C: Sales by Province
Topic D: Sales by City
Topic E: YTD Units Sold
Topic F: Top 10 Products
Topic G: Sales by Manager
Topic H: Sales by Category

Lesson 4: Activity - Combining Charts to Create Interactive Dashboard

Topic A: Important Elements of a Dashboard
Topic B: Set Up the Dashboard Layout
Topic C: Create a Unified Theme for your Dashboard
Topic D: Add Short Description to Provide Meaningful Information
Topic E: Hide Worksheets (Including Very Hidden)
Topic F: Save the Dashboard as PDF or Picture