PivotTable in Microsoft Excel is the most powerful and versatile reporting tool that enables you to extract information easily from large amount of raw data and transform them into meaningful report. Being able to analyze the raw data in your worksheet effectively and build interactive reports from the data can help you make better business decisions and increase your productivity.

 

PivotTable is extremely user friendly in that you can look at the same data in different ways by moving or pivoting fields of data from one area to another using drag and drop. You can also quickly filter the data in a PivotTable report with just a mouse click by using filtering components such as Slicer and Timeline.

 

This 2-days workshop will equip the participants with the essential skills to prepare the raw data for PivotTable, convert source data to a dynamic data range, create dynamic PivotTable and PivotChart reports, change the report layouts and formats, as well as build up interactive and informative reports from raw data.

Programme Outline

DAY 1

1. Introducing PivotTable and PivotChart

• Overview of the PivotTable
• Exploring PivotTable Features
• Understanding PivotTable and PivotChart Report Limitations
• Overview of the PivotChart
• Exploring PivotChart Features
• Formatting Source Data as Excel Table

 

2. Creating PivotTables from Local Data

• Creating PivotTable from an Excel Table
• Enable Classic PivotTable Layout
• Creating PivotTable from Existing PivotTable
• Using Recommended PivotTable
• Copying PivotTable with Paste Special
• Refreshing PivotTable Data Manually
• Refreshing PivotTable When Open File
• Protecting PivotTable

 

3. Manipulating PivotTable Fields

• Changing Order of Fields
• Changing Report layout
• Repeating Item Labels
• Showing or Hiding Subtotals
• Showing or Hiding Grand Totals
• Displaying Details of PivotTable Data
• Deleting PivotTable

 

4. Formatting PivotTables

• Applying PivotTable Styles
• Changing PivotTable Style Options
• Applying Number Format
• Creating Custom Formats

 

5. Sorting and Grouping PivotTable Fields

• Sorting PivotTable Automatically
• Sorting PivotTable Manually
• Grouping Date Items Automatically
• Grouping Number Items Automatically
• Showing or Hiding Group Details
• Grouping Text Items Manually
• Displaying Subtotals for the Groups

 

6. Filtering PivotTable Data

• Filtering PivotTable with Report Filter
• Show Report Filter Pages
• Filtering PivotTable with Label Filters
• Filtering PivotTable with Value Filters
• Filtering Multiple Items with Search Box
• Extracting Records to another Worksheet
• Filtering PivotTable with Slicer
• Filtering PivotTable with Timeline

 

7. Creating PivotCharts

• Creating PivotChart from Excel Table
• Creating PivotChart from Existing PivotTable
• Changing PivotChart Types
• Customizing PivotChart Elements
• Formatting PivotChart

 

DAY 2

 

8. Performing Calculations

• Summarizing Values with Functions
• Renaming Row and Column Labels
• Showing Values as Percentage
• Specifying Characters for Empty Cells
• Calculating Running Total
• Calculating Difference
• Ranking Values within a Field

 

9. Creating Custom Calculations

• Creating Calculated Items
• Creating Calculated Fields
• Listing Custom Formulas
• Removing Custom Calculations
• Creating Formulas Outside PivotTable

 

10. Creating Reports from External Data

• Creating PivotTable from Text File
• Creating PivotTable Report from Access
• Creating PivotTable Report from Other Sources
• Creating PivotChart Report from Access
• Refreshing PivotTable Automatically
• Setting External Data Properties

 

11. Using Color Visualizations

• Applying Conditional Formats
• Managing Conditional Formatting Rules
• Sorting Data by Color
• Filtering Data by Color
• Creating and Customizing Sparklines
• Removing Sparklines

 

12. Creating Dynamic Slicer-Driven Report

• Apply Conditional Format to PivotTable Values
• Copy the Conditional Format Rule
• Understand Sparklines
• Create Sparkline from PivotTable
• Apply Top 10 and Bottom 10 Filter
• Create Pie Chart from PivotTable
• Create a Combo Chart from PivotTable
• Add Slicer to PivotChart
• Format Slicer with Slicer Tools
• Change the Slicer Settings
• Add Timeline to PivotChart
• Format Timeline with Timeline Tools
• Connect a Slicer Multiple PivotTables

 

Training Methodology
 
Lecture style, with hands-on exercises
 
 

Closing Date for Registration

1 week before programme or until full enrolmen

Intended For

This workshop is for all existing Microsoft Excel users who intend to create effective and dynamic business reports using PivotTable and PivotChart.

Competency Mapping

Category 5 = 14.00 Hours

Programme Facilitator(s)