"A Training Solution Provider delivering Learning Skills to keep forever"

Call the JCS training team free now 0800 5425 150 Or mail Email | training@jcstraining.com

Excel PivotTables And Dashboards

Course Overview

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 ‘add-ins’. Some experience of using Tables and basic PivotTables would be advantageous. Suitable for Excel 2013 and 2016 users.

 

Bar PivotChart

Shared legend

Sparklines

Shared Slicers to filter data

Data Bars

Map Chart

Excel Interactive Dashboard using PivotTables and Slicers

Course Duration: 1 day

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