Great Canadian Training Company color logo

Creating Interactive Dashboards in Excel

Creating Interactive Dashboards in Excel

Course Length:

12 hours (2 days)

Learning Objectives

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


  • Create a dashboard from start to finish
  • Automate workbook functionality
  • Apply conditional logic
  • Visualize data by using basic charts
  • Implement advanced charting techniques
  • Analyze data by using Pivot Tables, slicers, and Pivot Charts

Target Student

This course is designed for students who already have foundational knowledge and skills in Excel and who wish to begin taking advantage of some of the higher-level functionality in Excel to summarize table data by using functions, charts, and Pivot Tables, and display those elements in a dashboard format.

Course Outline

Section 1: Dashboard Design Principles

Defining a Dashboard

First Steps - Things to Consider Before Creating a Dashboard

Dashboard Design Principles

Using Layout & Placement to Draw Focus

Dashboard Wire Frame

Formatting Numbers Effectively

Using Titles & Labels Effectively

Consistency is Key!

Colour – Best Practice

Keyboard Shortcuts


Section 2: Dashboard Formula Toolbox

Excel Formulas You’ll Need

Working with Excel Tables

Named Ranges

IF

IFERROR

COUNT, COUNTA, COUNTIF

SUMIF(s) & AVERAGEIF(s)

CHOOSE

VLOOKUP

INDEX

MATCH


Section 3: Data Validation

Using Data Validation to Create Drop-down Lists

Generating Unique Lists

Creating Multi-Level Drop-down Lists


Section 4: Conditional Formatting

The Basics

Conditional Formatting and Drop-down Lists

Conditional Formatting and Checkboxes

Tips for Using Conditional Formatting with Dashboards


Section 5: Linking

Linking to Single Cells

Linking Multiple Cells

Using the Camera Tool

Creating Linked Images Based on User Selection


Section 6: Charts

Creating a Chart

Working with Chart Elements

Changing the Chart Type

Adding Data Labels

Charting a Non-contiguous Range

Switching Row and Column Orientation

Creating Combination Charts

Linking Data Labels

Editing the Chart Axis and Gridlines

Adding and Editing Axis Labels

Adding Graphics to a Chart

Converting a Chart to a Picture

Creating Sparklines


Section 7: Pivot Tables, Slicers, and Pivot Charts

Creating and Modifying a Pivot Table

Grouping & Sorting Pivot Table Data

Set Number Formatting

Changing Summary Calculations

Adding Multiple Subtotal Calculations

Prevent Column Widths from Changing on Update

Suppressing Subtotals

Creating Pivot Charts

Adding & Formatting Slicers


Section 8: Creating Interactive Dashboard Components

Introduction to Form Controls

Combo Box – Select from a Drop-down

Checkbox – Check/Uncheck Options

Spin Button – Move Up & Down

Option Buttons – Choose One Option

List Box – Select from a List of Options

Creating Navigation Buttons


Section 9: Putting It All Together

Creating a Dashboard from Start to Finish

Adjusting Slicer Report Connections

Hiding Gridlines, Columns, Rows, and Headings

Hiding Ribbons and Sheet Tabs

Automatically Refreshing Your Pivot Table on Updates

Securing Your Dashboard

A seal with “Guaranteed to Run” written on it. In the middle is a person giving a thumbs up, standing behind a checkmark

Creating Interactive Dashboards in Excel

Upcoming Classes

All Public classes are held online
with a live instructor
Dates Price Qty
April 25-26, 2024
9:00 AM to 4:00 PM ET
$995.00 Online
July 9-10, 2024
9:00 AM to 4:00 PM ET
$995.00 Online
October 1-2, 2024
9:00 AM to 4:00 PM ET
$995.00 Online
* Applicable taxes will be applied on checkout

I want to talk to someone about private or customized training


Have questions about this course? See below or reach out to our training coordinators.

Group of 4 or more? Consider private training

The Great Canadian Training & Consulting Company Advantage

When you train with us, your training will not just check a box. We make sure it is GREAT!

  • Experienced Trainers
  • Guaranteed to run classes
  • Learning Guarantee
  • Anytime after-training support for 30 days
  • Bilingual training, manuals & post-training support available

This course comes with

PDF Reference Guide

Take the Reference Guide anywhere and save the environment with an electronic copy of your training Reference Guide for use on any of your computers or devices


Use Your Own Files

Whether it's one-on-one or group training you can give us files to include in your training session and help you apply what you learn even more!


Printed Reference Guide

Instead of the PDF that comes with your course, purchase a printed paper Reference Guide for use during your class and future reference