Mastering Pivot Table with Microsoft Excel
Mastering Pivot Table with Microsoft ExcelCourse Duration: 2 Days
Who is this course for?Our Microsoft Excel Pivot Tables 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.
PrerequisitesPrior use of Pivot Tables or our Excel - Advance course.
BenefitsAt 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 manipulate complex summaries and analyses of 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 utilise Pivoted data in many different contexts.
Module 1: Pivot Table Fundamentals
- What is a Pivot Table?
- When and why use a Pivot Table?
- Anatomy of a Pivot Table
- Limitations of Pivot Tables
Module 2: Creating Basic Pivot Tables
- Preparing your data
- Tabular Layout
- Removing Section Headings
- Removing Repeated Column Groups
- Eliminating Gaps
- Type Formatting
- Creating Pivot Tables
- Managing Changes in Your Source Data
- Dynamic Named Ranges
- Dealing with Zeroes
- Summary Calculations
- Running Totals and Top 10 reports
- Pivot Table Tools
Module 3: Pivot Table Views
- Conditional formatting
- Sorting, Filtering and Re-ordering
- Saving Custom Views
Module 4: Pivot Table Calculations
- Calculated Fields and Items
- Managing & maintaining Pivot Table calculations
Module 5: Pivot Charts
- What is a Pivot Chart?
- Creating a Pivot Chart
- Pivot Chart Rules
- Pivot Chart Alternatives
Module 6: Working with Data Sources
- Consolidating multiple sheets or ranges of data
- Using external data sources (Access)
Module 7: Automating Pivot Tables with Macros
- Introducing Macros
- Recording Pivot Table macros
- Tidying and refining recorded macros
- The Power Pivot add-in (2010 and above)