Creating Interactive Dashboards in Excel
(2013, 2016, 2019, 2021, 365)

This course will provide you advanced training on creating dashboards. The ability to analyze massive amounts of data, extract actionable intelligence from it, and present that information to decision makers is the cornerstone of driving a successful organization that is able to compete at a high level.

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