3815 : Data Analysis Using Excel PivotTables
PivotTables are the Most Powerful feature in Excel that enable you to explore, analyze, summarize, and present your data with just a few clicks.
Excel PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results.
Both PivotTables and PivotCharts enable you to make informed decisions about critical data in your enterprise.
You can also connect to external data sources such as SQL Server tables and Access database to create PivotTables or use existing PivotTables to create new tables.
The course is Microsoft Excel 2013/ 2016/2019/Office 365 compliant.
The duration of the program :
Classroom |
Live Online |
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.
Location | Dates | Time | Delivery Format |
---|---|---|---|
Live Online* | 7,10,14,17-Οκτ | 17:30-20:45 | Instructor Led |
Live Online* | 18,21,25,28-Νοε | 17:30-20:45 | Instructor Led |
* Σύγχρονη εξ αποστάσεως εκπαίδευση με εισηγητή – Virtual Class
Last update : 28/12/2023