Important

If you wish to disburse your SkillsFuture Credit to ISCA, please login to SkillsFuture Credit (SFC) portal (http://www.skillsfuture.sg/credit) with your Singpass,  within 60 days before the course start date to submit the SkillsFuture Credit Claim and obtain the necessary approval. For more information on SFC procedure, please click here.
 
Failure to submit claim application via SkillsFuture Portal and obtain necessary approval before the course start date will result in an administrative fee of $42.80 levied to revise the invoice based on the amount of SkillsFuture Credit you have indicated to use.

Notice

With effect from 2018, the course outlines will be revised to include Excel 2016 functionality and features. Please click here to view the 2018 outlines and dates.

Our Computer lab and software will be upgraded to support Excel 2016 to ensure a greater learning experience for our participants. Participants may bring along their personal laptop if they prefer to practise the hand-on exercises using Excel 2013.

Programme Objective

PivotTable for Excel 2010 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. 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. The new Slicer feature is an easy-to-use filtering component that allows you to quickly filter the data in a PivotTable report with just a mouse click. 

This 2-days workshop will equip the participants with the essential skills to prepare the raw data for PivotTable, create dynamic PivotTables and PivotCharts reports, layout and format the PivotTable Report, as well as build up an interactive and informative 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. The participants will have chance to try out a few case studies based on real-world problems.

Programme Outline

1. Introducing PivotTables and PivotCharts

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

2. Creating PivotTables from Local Data

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

• 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 

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

• Filtering PivotTable with Report Filter
• Changing Report Filter Layout
• Filtering PivotTable with Label Filters
• Filtering PivotTable with Value Filters
• Filtering Multiple Items with Search Box
• Extracting Records to another Worksheet 

• 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 

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

• 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 

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

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

• 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 

• Creating PivotTable and PivotChart Report
• Moving PivotChart to another Worksheet
• Removing Field Buttons from PivotChart
• Changing PivotChart Size and Alignment
• Linking PivotChart Label to a Cell
• Connecting a Slicer to a PivotTable
• Customizing and Formatting Slicer 
• Connecting a Slicer to Multiple PivotTables 

Training Methodology
Lecture style, with hands-on exercises

Intended For

This intermediate level workshop is for all existing Microsoft Excel users who intend to create effective and dynamic business reports using PivotTable and PivotChart. The participants must know how to build a simple PivotTable. This course is NOT for beginners who are not familiar with Excel commands and those who seldom use Excel program.