Description

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends of a  large amounts of data. You can use a PivotTable to analyze numerical data in detail and answer unanticipated questions about your data. Moving rows to columns or columns to rows (or “pivoting”) to see different summaries of the source data. Both PivotTables and Pivot Charts enable you to make informed decisions about critical data in your company.

Learning Outcome and Goals

• Identify Excel Data thoroughly & Prevent common mistake in Excel Reports
• Manage & Prepare Database/Excel List effectively
• Generate Quick Reports using Columns/Row and Value in PivotTable
• Create Calculated Fields and Item in the report
• Visualize report with Chart using Pivot Chart
• Apply quick Analysis using Slicer to Filter the Report

Course Requirement

• Participants should be able to use a PC at the beginner level
• Basic knowledge and functionality of Microsoft Excel
• Microsoft Office 2013 and above

Course Information

Course Outline

Get to know a PivotTable

• The Best Practice using a database for PivotTable

Creating Pivot Table

• Database Pre-requisite for preparing a PivotTable Report
• Designing a PivotTable
• Adding Elements to the Report
• Creating a Report Filter
  • Use Table Field as Report Filter
  • Use the Report Filter
  • Reset the Filter
• Use Slicers to Filter Report
  • What is Slicers?
  • Remove Slicer
• Update the Data Source
  • Changes Data Sources
  • Create a dynamic Range for the Data Table

Customizing a PivotTable

• Format a PivotTable
  • Use PivotTable Style
  • Number and Text Format
  • Explore the PivotTable Options
  • Use the Value Field Settings
• Subtotals
• Show Value As
• PivotTable Print Options
• Grand Totals
• Report Layout

Grouping, Sorting and Filtering

• Grouping Pivot Fields
  • Dates
  • Number Fields
  • Text
• Ungrouping
• Sort& Filtering the PivotTable

Use “Fields, Item and Sets”

• Creating Calculated Field
• Creating Calculated Item
• Edit and Delete Calculated Field or Item

Pivot Charts

• Convert PivotTable to PivotChart

PivotTable Wizards

Multiple Consolidation Ranges