Description

MASTERING PIVOT TABLE WITH MICROSOFT EXCEL

Course Duration: 1 Day

WHO IS THIS COURSE FOR?

This course is suitable for those with a basic working knowledge of Pivot Tables who want a greater understanding of the more sophisticated functions and features available.

PRE-REQUISITES

Prior use of Pivot Tables or our Excel - Advanced Course.

BENEFITS

At the end of this course, you will have a greater understanding of the functionality and potential of Pivot Tables

You will be able to construct and manupulate complex summaries and analyzes data using Pivot Table tools, control visual and data formatting, manage and add to the statistical analysis of your data, deal with varying data sources, automate, troubleshoot and utilize Pivoted data in many different contexts.

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

Informative presentation, group discussion and hands on practical session.

More detail about Iconic Training Solutions Sdn Bhd