excel logo

Excel PivotTables and Dashboards

Duration: 
1 day
Level: 
Intermediate

Objectives: 

An Excel dashboard is a visual dynamic interface showing a summary of consolidated data on a single screen. On this course you will learn how to create Dashboards using the existing tools in Excel.  You don’t need any knowledge of Visual Basic macros and there is no need for extra ‘addins’. Suitable for Excel 2013 and 2016 users.

Pre-requisites: 

Some experience of using Tables and basic PivotTables would be advantageous.

Course Content: 

Tables

  • Turn your data into a Table
  • The importance of Tables!
  • Table Slicers

Conditional Formatting

  • Data Bars & Icon Sets
  • Setting the threshold values

Creating a PivotTable

  • Setting & formatting Fields
  • Sorting and grouping data
  • Filtering data
  • Copying PivotTables (shared cache)
  • PivotTables options & layouts

Creating a PivotChart

  • Chart types
  • Hiding fields & chart elements
  • Creating dynamic labels

Map Charts

  • Layout and formatting charts 

PivotTable Slicers

  • Resizing and formatting
  • Shared Slicers
    (Setting Report Connections)

PivotTable Formulas

  • Extract data using GETPIVOTDATA
  • Updating PivotTables

Sparklines

  • Creating a line Sparkline
  • Formatting the Sparkline

Formatting the Dashboard

  • Moving PivotCharts
  • Creating a shared legend
  • Tips for aligning/sizing objects
  • Applying & Modifying a Theme

Protecting & Hiding Data

  • Hiding screen elements
  • Protecting Slicers and other elements
  • Macro to hide the ribbon 

Download a Course Outline in PDF Format:

Scroll to Top