Data Analysis Using Excel

Data Analysis – PivotTable

Professional Microsoft Training - PivotTables

This Microsoft Excel – Pivot Tables two days training course is design to equip delegates with the basics of PivotTables starting from how to prepare the data, creating manual PivotTables and using the tools to the most complex features of PivotTables such as performing calculations and analysis using calculated items, calculated field, slicers and building dashboard and data visualization using Pivot Charts.

Pivot Table Fundamentals

  • 1. What is a Pivot Table?
  • 2. When and why use a Pivot Table?
  • 3. Anatomy of a Pivot Table
  • 4. Limitations of Pivot Tables

Creating Basic Pivot Tables

Preparing your data

  • Tabular Layout
  • Removing Section Headings
  • Removing Repeated Column Groups
  • Eliminating Gaps
  • Type Formatting

Managing Changes in Your Source Data

  • Dynamic Named Ranges
  • Dealing with Zeroes

Running Totals and Top 10 reports

Pivot Table Tools

  • Layout Updates
  • Refreshing & Restarting
  • Moving Pivot Tables

Pivot Table Calculations

  • Calculated Fields and Items
  • Managing & maintaining Pivot Table calculations

Pivot Table Calculations

Pivot Chart Rules

  • Managing the underlying data
  • Adding Layers
  • Altering Structure
  • Pivot Chart Alternatives
  • Dynamic Charts
  • Transition tables

Working with Data Sources

  • Consolidating multiple sheets or ranges of data
  • Using external data sources (Access)

Creating Pivot Tables

  • Adding Fields
  • Adding Layers
  • Altering Structure
  • Report Filters

Subtotals
Summary Calculations

  • Running totals
  • Differences (year on year, month on month)
  • Percentages of Rows, Columns, Totals and other fields

Pivot Table Views

  • Conditional formatting
  • Sorting, Filtering and Re-ordering
  • Slicers Excel 2010 version and above

Pivot Charts

  • What is a Pivot Chart?
  • Creating a Pivot Chart

Automating Pivot Tables with Macros

  • Introducing Macros
  • Recording Pivot Table macros
  • Tidying and refining recorded macros
  • The Power Pivot add-in Excel 2010 version and above