Creating Interactive Dashboards in Excel

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