MASTERING PIVOT TABLE WITH MICROSOFT EXCEL

WHO IS THIS COURSE FOR?

PRE-REQUISITES

BENEFITS

KEY CONTENT

Unit 1: Pivot Table Fundamentals

• What is a Pivot Table?
• When and why use Pivot Table?
• Anatomy of Pivot Table
• Limitations of Pivot Tables

Unit 2: Creating Basic Pivot Tables

• Preparing data
• Tabular layout
• Removing section headings
• Removing repeated column groups
• Eliminating gaps
• Type formatting
  • Adding fields
  • Adding layers
  • Altering structure
  • Report filters
• Managing changes in your source data
  • Dynamic named ranges
  • Dealing with zeroes
• Subtotals
• Summary Calculations
  • Running totals
  • Differences (year on year, month on month)
  • Percentages of rows, columns, totals and other fields
• Running totals and top 10 reports
• Pivot table tools
  • Layout updates
  • Refreshing & restarting
  • Moving Pivot Tables

Unit 3: Pivot Table Views

• Conditional formatting
• Sorting, filtering and re-ordering
  • Slicers
  • Timeline
• Saving Custom Views

Unit 4: Pivot Table Calculations

• Calculated fields and items
• Managing & maintaining Pivot Table calculations

Unit 5: Pivot Charts

• What is Pivot Chart?
• Creating Pivot Chart
• Pivot Chart Rules
  • Managing underlying data
  • Adding layers
  • Altering structure
• Pivot Chart Alternatives
  • Dynamic Charts
  • Transition Tables

Unit 6: Working with Data Sources

• Consolidating multiple sheets or ranges of data
• Using exter data sources

Unit 7: Automating Pivot Tables with Macros

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

METHODOLOGY