Excel 2013 Pivot Table In-Depth

Print in

Objective:

Introduction

Excel’s most powerful analytical tool is the PivotTable. In this course, you’ll be taught how to leverage PivotTables to summarize, sort, count, and chart your data in Microsoft Excel. This course shows you how to navigate the complexity of PivotTables while taking advantage of their power. You will learn how to build PivotTables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable.

Course Objective

  • Preparing data source to use in Pivot Table
  • Creating a PivotTable
  • Get data from external source
  • Summarizing multiple data fields
  • Managing subtotals and grand totals
  • Grouping PivotTable fields
  • Filtering with selections, rules, search filters, slicers, and timeline
  • Applying PivotTable styles
  • Formatting cells with conditional formatting
  • Show different calculations in PivotTable value fields
  • Create calculated fields using formulas
  • Create calculated items with combine fields
  • Creating PivotCharts

Target Audience

  • 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.

Course Outline

  • Introduction to PivotTable
  • Preparing data source to be use in PivotTable
  • Creating the PivotTable
  • Using external data source in PivotTable
  • About PivotTable Fields Pane
  • About PivotTable structure
    • The Values area
    • The Columns area
    • The Rows area
    • The Filters area
  • Building up the PivotTable
  • Pivoting the PivotTable
  • Show report filter pages
  • Removing field from the PivotTable
  • Managing PivotTable
    • changing PivotTable name
    • select the entire PivotTable
    • deleting a PivotTable
    • resetting PivotTable using Clear All
    • Show or hide field headers
    • Show or hide the Expense and Collapse button
  • Grouping fields
    • Multi-levels grouping
    • Grouping a date field
  • Sorting and filtering PivotTable data
    • Sorting Pivottable data
    • Sorting PivotTable label
    • Filtering the Row Area
    • Filtering the Column Area
    • Clearing Individual Filter
    • Clearing All Filters
    • Using Slicer to Filter Data
      • Moving the Slicer
      • Changing the Slicer Style
      • Applying Filter Using Slicer
      • Clearing Filter Using Slicer
      • Deleting the Slicer
    • 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 PivotTable
    • Refresh PivotTable manually
    • Refresh PivotTable automatically when opening the file
  • Formatting the PivotTable
    • Formatting number field
    • Changing the values label
    • Changing the report layout
    • Changing PivotTable styles
    • Managing subtotals
    • Managing grand totals
    • Inserting blank rows after each item
    • Collapse or Expand a Single Item Level
    • Collapse or Expand the Entire Item Level
  • Summarizing PivotTable Data
    • Changing the data field summary operation
    • Summarizing more than one data field
  • Working with PivotChart
    • Creating a PivotChart
    • Moving the PivotChart to a new worksheet
    • Changing the chart style
    • Controlling the chart using PivotTable
    • Hiding the legend from the PivotChart
    • Display Data Table from the PivotChart
  • Printing PivotTables
    • Printing a PivotTable
    • Printing headers at the top of each printed page
    • Printing each item on its own page
    • Printing a PivotChart
  • Calculation in PivotTable
    • Calculated field
      • Create calculated field
      • Edit calculated field
      • Delete calculated field
    • Calculated Item
      • Create calculated item
      • Edit calculated item
      • Delete calculated item
    • 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)

For individial, you may submit your claim via http://www.skillsfuture.sg/credit.

Register online @ http://one.pa.gov.sg

SDF Applicant, please register @ https://www.skillsconnect.gov.sg

IT Centre reserves the right to reschedule the course without prior notice due to class size or unforeseen circumstances.