Course Detail()

Notice
As this class places strong emphasis on the hands-on experience, you will be working with data for the majority of the time in the class. To optimize the learning experience, it is mandatory for all participants to be equipped with a laptop, pre-installed with either Excel 2013 /2016 /365 /2019 (full licence) or any latest version. Kindly note that Mac Excel version has limited Excel functionality. Use of dual monitor is encouraged as you will be using 2 applications concurrently.

Programme Objective

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

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
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
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
Formatting PivotTables
- Applying PivotTable Styles
- Changing PivotTable Style Options
- Applying Number Format
- Creating Custom Formats
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
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
Using Color Visualizations
- Applying Conditional Formats
- Managing Conditional Formatting Rules
- Sorting Data by Color
- Filtering Data by Color
- Creating and Customizing Sparklines
- Removing Sparklines

DAY 2

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
Creating Custom Calculations
- Creating Calculated Items
- Creating Calculated Fields
- Listing Custom Formulas
- Removing Custom Calculations
- Creating Formulas Outside PivotTable
Creating PivotCharts
- Creating PivotChart from Excel Table
- Creating PivotChart from Existing PivotTable
- Changing PivotChart Types
- Customizing PivotChart Elements
- Formatting PivotChart
- Deleting PivotChart
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
Creating Interactive Report
- 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
- Customizing and Formatting Slicer
- Connecting a Slicer to Multiple PivotTables
- Connecting a Timeline to Multiple PivotTables

Pre-requisites

Please take note of the following admission requirements:

Trainings will be conducted on Zoom platform, thus video camera and microphone are compulsory
Mandatory for video camera to be turned on throughout the course
Display your official name (as per NRIC) in Zoom, to facilitate attendance taking

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.

Schedule & Fees

Testimonial

Funding

No funding Available!

Programme Facilitator(s)