Subscribe e-Newsletter
    Member Login
    Course Information
    Email
    Pass
    Forget password? Click here
    Event Profile
    Date Aug 01, 2018
    Time 9:00am to 5:00pm
    Venue Mandarin Orchard Singapore
    333 Orchard Road
    Singapore 238867
    Fee
    7% GST will apply
    SGD 395.00
    For Member
    SGD 375.25
    NoteBreakfast, two tea breaks and buffet lunch will be served; complimentary car parking coupon will be provided upon request
    Other Date(s)1) Dec 19, 2018
    2) Jan 30, 2019
    3) Feb 27, 2019
    4) Mar 27, 2019
    5) May 08, 2019
    6) Jul 03, 2019
    7) Sep 04, 2019
    8) Nov 20, 2019
    Trainer
    Activity
    You may reach us via
    T: (65) 9879 6267
    E: info@ccisg.com
    To register via fax, fill in This Form and fax to (65) 6310 5430.
    Excel’s most powerful analytical tool is the Pivot Table. In this course, you’ll be taught how to leverage Pivot Tables to summarize, sort, count, and chart your data in Microsoft Excel. This course shows you how to navigate the complexity of Pivot Tables while taking advantage of their power. You will learn how to build Pivot Tables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable.


    Participants should bring a laptop with Microsoft Excel 2013 / 2016
    Objective
    • Preparing data source to use in Pivot Table
    • Creating a Pivot Table
    • Get data from external source
    • Summarizing multiple data fields
    • Managing subtotals and grand totals
    • Grouping Pivot Table fields
    • Filtering with selections, rules, search filters, slicers, and timeline
    • Applying Pivot Table styles
    • Formatting cells with conditional formatting
    • Show different calculations in Pivot Table value fields
    • Create calculated fields using formulas
    • Create calculated items with combine fields
    • Creating Pivot Charts
    Outline
    • Introduction to Pivot Table
    • Preparing data source to be use in Pivot Table
    • Creating the Pivot Table
    • Using external data source in Pivot Table
    • About Pivot Table Fields Pane
    • About Pivot Table structure
      o The Values area
      o The Columns area
      o The Rows area
      o The Filters area
    • Building up the Pivot Table
    • Pivoting the Pivot Table
    • Show report filter pages
    • Removing field from the Pivot Table
    • Managing Pivot Table
      o changing Pivot Table name
      o select the entire Pivot Table
      o deleting a Pivot Table
      o resetting Pivot Table using Clear All
      o Show or hide field headers
      o Show or hide the Expense and Collapse button
    • Grouping fields
      o Multi-levels grouping
      o Grouping a date field

    • Sorting and filtering Pivot Table data
      o Sorting Pivot Table data
      o Sorting Pivot Table label
      o Filtering the Row Area
      o Filtering the Column Area
      o Clearing Individual Filter
      o Clearing All Filters
      o Using Slicer to Filter Data
    • Moving the Slicer
    • Changing the Slicer Style
    • Applying Filter Using Slicer
    • Clearing Filter Using Slicer
    • Deleting the Slicer
      o Filter date field using Timeline
    • Elements of the Timeline
    • Changing the filter types of the Timeline
    • Applying filter with Timeline
    • Add another criterion to an existing filter
    • Clearing filter in the Timeline
    • Moving & resizing the Timeline

    • Changing the Timeline style
    • Deleting the Timeline
    • Refreshing Pivot Table
      o Refresh Pivot Table manually
      o Refresh Pivot Table automatically when opening the file
    • Formatting the Pivot Table
      o Formatting number field
      o Changing the values label
      o Changing the report layout
      o Changing Pivot Table styles
      o Managing subtotals
      o Managing grand totals
      o Inserting blank rows after each item
      o Collapse or Expand a Single Item Level
      o Collapse or Expand the Entire Item Level

    • Summarizing Pivot Table Data
      o Changing the data field summary operation
      o Summarizing more than one data field
    • Working with Pivot Chart
      o Creating a Pivot Chart
      o Moving the Pivot Chart to a new worksheet
      o Changing the chart style
      o Controlling the chart using Pivot Table
      o Hiding the legend from the Pivot Chart
      o Display Data Table from the Pivot Chart
    • Printing Pivot Tables
      o Printing a Pivot Table
      o Printing headers at the top of each printed page
      o Printing each item on its own page
      o Printing a Pivot Chart

    • Calculation in Pivot Table
      o Calculated field
    • Create calculated field
    • Edit calculated field
    • Delete calculated field
      o Calculated Item
    • Create calculated item
    • Edit calculated item
    • Delete calculated item
      o Calculations in Show Value As
      ▪ % of Grand Total
      ▪ % of Column Total
      ▪ % of Row Total
      ▪ % of Parent Row Total
      ▪ % of Parent Column Total
      ▪ % of Parent Total
      ▪ Running Total In
      ▪ % Running Total In
      ▪ Rank Smallest to Largest
      ▪ Rank Largest to Smallest
      ▪ % of
      ▪ Difference From
      ▪ % Difference From
    • Solution for Multiple Pivot Tables using the same source data (optional)
    • Consolidating data from multiple sources (optional)
    • Converting a crosstab data into normal data list (optional)
    Who should attend
    • People who wants to learn how to create Pivot Table in a proper way
    • People who wants to go beyond the basic Pivot Table like using formula to create calculated fields
    • People who wants to summarize their data in different ways
    • People who wants to generate more reports which is hard to achieve in normal spreadsheet
    • People who wants to generate chart that can be change easily using Pivot Table

    Prerequisite

    • Must have attended Excel Basic or has been using Excel for the past three years continuously
    • Must know how to use Excel basic functions
    • Basic knowledge on Excel chart
    Methodology
    This is a one-day hands-on course. The trainer will explain on the topic and then guide you through with step-by-step practice. You will be provided with real life scenario for individual topics so that you can apply what is being taught. After the hands-on practice on every topic, you’ll have a short Q & A session
    Testimonials
    Well connect and structured. The workshop is very interactive and step-by-step approach. Trainer is very well versed with the software. Quick print to link out when preparation of table.
    National Environment Agency

    Clear and concise excellent trainer.
    ISEAS-Yusof Ishak Institute

    Very informative and impressed by the gravity of pivots function. The workshop is very interesting. Trainer welcome queries, patient and guide us, spot on our weakness or when we are lost.
    Motor-Way Credit Pte Ltd


    The additional functions in Excel could allow us to work efficiently on data entry.
    The workshop is an eye opener for people who wants to learn more functions in Excel.
    Alaster is very knowledgeable.
    National Environment Agency

    Excellent outcome. Workshop very well organise. Trainer speaks very clearly and express very well.
    ARBURG Pte Ltd

    I have learnt some formulas that I can apply at work. Alaster is very knowledgeable.
    Republic Polytechnic
    Alaster Leong's Profile
    Mr. Alaster Leong is an IT Trainer who possesses a strong passion in IT Training. He has more than 25 years of IT experience. He has been providing IT training in government, private institutions and organizations since 1992. He specializes in creative courses such as Adobe Photoshop, Adobe Dreamweaver, Adobe Flash, Adobe Acrobat, Access and Excel VBA, Microsoft Office Applications, etc. He is a certified Microsoft Office Specialist Expert (MOS) in Excel.
    Privacy Policy  |  Terms of Use
    Copyright © 2018 CCISG Pte Ltd  |  ACRA Reg No: 201207591D  |  GST Reg No: 201207591D