Excel Analysing and Presenting Data with Dashboards and Pivot Tables

Excel Analysing and Presenting Data with Dashboards and Pivot Tables Course Overview

Course Prerequisites

• • Delegates should have attended the Excel level 3 course, or have equivalent knowledge.

This course is suitable for

Included with this course

• • Lunch and refreshments when training is delivered at our training venues
  • Comprehensive course materials and exercises
  • Telephone and Email Support

Excel Analysing and Presenting Data with Dashboards and Pivot Tables Course Content

• Preparing Data for Analysis

  Absolute Referencing
  Consider data types
  Organising data in way best suited to presentation
  Tables
  Creating Tables
  Advantages of using tables
  Adding data to tables
  Table Options
  Slicers
  Naming cells (Ranges)
  Giving text to cells and ranges of cells
  Create Name from Selection
  Create Names using the Name Box
  Names in formulas
  The Name Manager
  Functions used for data preparation
  Proper
  Upper and Lower
  Left, Right Mid
  Concatenate
  Text to Columns
  Remove Duplicates
  FlashFill
  VLOOKUP
  Understanding Lookup Functions
  Using VLOOKUP
  Approximate and Exact Matches
  Gathering data for analysis using VLOOKUP
  Fixing Common Lookup problems

• Building Insightful Pivot Tables

  Creating a pivot table
  Fields to create row headings
  Fields to create column headings
  Data in the summary area
  Dynamically building a pivot table
  Rearranging fields in a pivot table
  Number formatting of the data area
  Moving pivot tables from one sheet to another or around the same sheet
  Deleting a pivot table

• Summarising Pivot Table Data

  Managing subtotals and grand totals
  Changing the data field calculation (Sum, Count, Average etc.)
  Sort and Filter
  Drilling down to the raw data
  Building and using Report Pages

• Grouping Pivot Data

  Grouping Date Fields
  Including Years when Grouping by Months
  Grouping Date Fields by Week
  Multi Grouping

• Running Totals in Pivot Tables

  Create year on year total comparisons
  Setting Running Total base fields

• Calculated Fields in Pivot Tables

  Setting the number format for a calculated field
  Limitations on calculated fields
  Adding a calculated field to a pivot table
  Creating calculating fields

• Conditional Formatting For Pivot Tables

  Conditional Formatting Types
  Highlight Cell Rules
  Analysis of data using Top Bottom Rules
  Manipulate Icon Sets to fit data types
  Data Bars
  Colour Scales to create data heat maps
  Customising Conditional Formatting with formulas

• Pivot Charts

  Building Pivot Charts
  Editing Pivot Chart Layouts
  Editing Pivot Chart Styles
  Using the built in chart filter buttons for presentation

• Slicers and Timelines

  Building Slicers for easy and interactive filtering
  Formatting Slicers
  Pivot Table connections
  Timelines

• Pivot Table Dashboards

  Developing Layout and Design approach to dashboard sheet
  Adding Pivot Tables and Pivot Charts to dashboards
  Adding dynamic filter and slicer buttons

• Using Simple Macros to make Reports and Dashboards interactive

  Recording a Macro
  Run a Macro
  Delete a Macro
  Assign a Macro to a Button
  Viewing the Code behind a Macro
  Macro Security

• Sample Dashboard Examples

  Sales and employee analysis example
  HR and location analysis example