Pivot Tables are essential for turning raw data into meaningful summaries. In this course, participants learn how to create, customise, and enhance Pivot Tables to explore patterns and generate insights. They will apply advanced sorting, filtering, calculations, and slicers to work more efficiently with Excel data.
Learning Outcomes:
Create and customise Pivot Tables for data analysis
Perform calculations and create summaries
Use slicers, timelines, and multiple data ranges
Build PivotCharts and interactive dashboards
Apply formatting and layout options effectively
Key Topics:
Creating Pivot Tables and data layout
Custom calculations and summary functions
Slicers, filters, and groupings
Advanced formatting and data relationships
PivotCharts and multiple data sources
Module 1. Making Data Work For You
- Maximize the Power of PivotTable
- Ask the Questions
- Get the Answer
- Review
Module 2. A Quick Tour
- Determine What You Need to Know
- Identify the Source
- Creating the Pivot Table
- Working with Pivot Table Layout
- Review
- Practice – Creating the PivotTable
Module 3. Manipulating the PivotTable
- Rearranging the LayoutAdding New FieldRenaming a FieldFormatting NumbersChanging Sorting OrderShowing Top 10Refreshing DataReviewPractice – Manipulating the PivotTable
- Rearranging the Layout
- Adding New Field
- Renaming a Field
- Formatting Numbers
- Changing Sorting Order
- Showing Top 10
- Refreshing Data
- Review
- Practice – Manipulating the PivotTable
Module 4. Digging Deeper
- Grouping Items
- Displaying Subtotals
- Hiding/Showing Grand Totals
- Formatting PivotTable
- Using Slicer
- Formatting Slicer
- Review
- Practice – Grouping Item and Using Slicer
Module 5. Beyond the Basics
- Creating s Second Data Field
- Changing the Summary Function
- Displaying Numerical Value as a Percentage of the Total
- Creating a Custom Calculation
- More Function in Custom Calculation
- Review
- Practice – Creating Custom Calculation
Module 6. More PivotTable Calculations
- Using Formulas
- Creating a Calculated Field
- Creating a Calculated Item
- Using GetPivotData Function
- Turn off GetPivotData Function
- Review
- Practice – More PivotTable Calculations
Module 7. Optional Topics
- Creating PivotChart
- Publishing PivotTable to the Web
- Timeline in PivotTable
- Creating Relationships between Mutiple Tables
- Mutiple Tables in Data Analysis
- Drill To in PivotTable
- Using Multiple Source Ranges
- Review
