3815 : Data Analysis Using Excel PivotTables

Benefits of our Training Course
▪ Understanding the Benefits of a PivotTable
▪ Creating PivotTable and Working with Source Data
▪ Designing the Layout and Format of a PivotTable Report
▪ Summarizing your Data with Functions & Formulas
▪ Grouping your data in a PivotTable Report
▪ Organizing your Data
▪ Working with Slicers and PivotCharts
▪ Managing your Source Data
▪ Performing Powerful Data Analysis
▪ Creating Reports with Excel GetPivotData Function

This course is designed for people who want to take their current Excel skills to the next level. This course also referred to people who are familiar with PivotTables and interested in becoming experts through our advanced topics.

Understanding the Benefits of Pivot Tables

·         What is a Pivot Table ?

·         Uses and Examples of PivotTables

·         Main Data Format Categories

Preparing Source Data for PivotTable
·         Next Generation Analysis Tools
·         Introduction to Get & Transform (Power Query)
·         Loading Data from Data Sources
·         Clean and Transform Data
·         Combination Data

Creating Pivot Table and Working with Source Data

·         Creating a Pivot Table using Source Data

·         Creating a PivotTable using an External Data Connections

·         Field & Area Section

·         Using a Report Filter

·         Drilling down for Details

·         Defer Layout Update

·         Changing Data Source

·         Refreshing Worksheet & External Data

·         Moving PivotTables

Designing the Layout and Format of a PivotTable Report

·         Changing the PivotTable Report Format Style

·         Grand Totals & Subtotals Layout

·         Report Layout & Blank Row

·         Classic pivot Layout

·         Expand & Collapse Buttons

·         Move & Remove Fields

·         Show / hide Field & Headers

·         Number Formatting

·         Field Name Formatting

·         Pivot Table Options

·        Show Report  Filter on Separate Worksheets

Summarizing your Data with Totals & Formulas

·         Summarizing Values by using different functions

·         Showing Values as Percentages

·         Showing Differences (year on year, month on month)

·         Showing a Running total / Rank and Index

·         Creating Multiply Subtotals

·         Creating a Calculated Field & Calculated Items

·         Creating a List of Pivot Table Formulas

Grouping your Data in Pivot Table

·         Grouping by Date /Month/Quarters /Years/Half Years

·         Grouping by Sales Ranges /Text Field

·         Grouping by a Custom Date

·         Grouping Two-Pivot Tables

Organizing and Analyzing your Data

·         Sort Using Custom List

·         Sort Row from A-Z and Sales from Z-A

·         Sort Largest to Smallest Grand Totals

·         Filter by Report Filter

·         Filter by Multiple Fields

·         Filter by Multiple Values

·         Conditional Formatting

Working With Slicers and Pivot Charts

·         Insert a Slicer

·         Slicer Styles and Settings

·         Slicer Customization

·         Slicer Connections

·         Adding Sparkline to a Pivot Table Column

·         Insert a Timeline

·         Insert Pivot Charts

·         Pivot Chart Design, Layout, and Format

·         Pivot Chart Customization

·         Saving a PivotChart Template

·         Pivot Charts and PowerPoint Connection

Managing your Source Data

·         Pivot Cache Explanation

·         Ways to Reduced File Memory

·         Sharing Pivot Table via One Drive

Performing Powerful Data Analysis and Data Modeling

·         Pivot Table vs Power Pivot

·         Power Pivot Utilities

·         Creating a Data Model

·         Creating Relationships between Tables

·         Creating Dashboards

Creating Reports with Excel GetPivotData Function

·         GetPivotData Function Introduction

·         Using GetPivotData for Custom Reports

·         Creating Drop Down List with GetPivotData to Filter

·         Getting Totals from Specific Pivot Table with GetPivotData

Summary

·         Frequent PivotTable Questions and Answers

·         Solutions to Common Problems

(Optional) Financial Reporting using Pivot Tables

·         Reports for Sales Managers

·         Scenario Summary Report

·         Calculating Invoice Aging

·         Profit and Loss Statements

·         Sales Forecasting Report

·         Frequency Distribution Table

This course does not map to certification.

 Microsoft Excel 2016 Level I  &  Microsoft Excel 2016 Level II