LEADS Corporation Limited
HOME > LTCL > Training > Microsoft Excel 2016 – PivotTable in Depth

Microsoft Excel 2016 – PivotTable in Depth

The extended features of PivotTable is available in the business intelligence (BI) PowerPivot, that can be used in the version Excel-2010, 2013 and 2016 by which you can summarize your reports from multiple worksheets or different types of external data sources and no limit in data volume that can handle billions of rows with amazing speed.

This training content has been designed so that they can learn the techniques to prepare various types of analytical summary reports from different types of data sources in various approaches within a few minutes that will save huge times. They will also be able to prepare automated interactive dashboard report using PivotTable, PivotChart and PowerPivot with PivotTable Tools. To create the automated dashboard you do not need to be a master of Excel and, you do not have any knowledge about programming language; just being a regular Excel user is fine.

Learning Outcome

At the end of the session, You will be able to

Know the techniques to Map a database so that you can generate PivotTable reports

Create dynamic PivotTable and fix various formatting

Use the techniques to handle millions of data and present the core information.

Share PivotTable with others in the web.

Install PowerPivot and create PivotTable using various types of internal and external data sources.

Create relationship among various linked tables and use of Data Analysis Expression (DAX)

Create world class dashboard easily using PivotTable, PowerPivot, PivotChart and Pivot tools.

Course Outline

Session-1

Introduction of PivotTable in Excel-2016

Difference of PivotTable in Excel 2007, 2010, 2013 and 2016 version

Structure of a PivotTable

Data mapping to prepare a PivotTable

Creating a PivotTable in Excel

Understanding PivotTable Field List

Row Labels, column labels, values area, report filter

Examples of various types of PivotTable in different angles

Various types of report layout-report in compact form, report in tabular form

Session-2

Automated page break after each item group

Customizing subtotal at any row label data and subtotal category

Changing PivotTable data source

Changing type of calculation in value fields

Creating a running sum column in values field

Inserting a calculated field and calculated item

Decorating reports and changing styles

Containing customized data format that will not be changed on refresh

Containing customized column width that will not be changed on refresh

Applying conditional formatting to a PivotTable that will not be changed on refresh

Sorting and filtering techniques in PivotTable data

What is slicer in PivotTable?

Creating, changing and formatting slicer

Make your PivotTable dynamic using slicer

Session-3

Drilling down of PivotTable data

Filling empty cells with zero (0)

Sharing PivotTable to the web using web services in Excel

Expand/Collapse the group (manually & automatically)

Show text in PivotTable values area

Creating PivotChart from existing workbook data

Creating PivotChart from external data source (from Access/SQL Server)

Create PivotTable using multiple consolidation ranges

Make your data source dynamic

Session-4

What is PowerPivot Add-Ins?

PC Requirements and process to install PowerPivot

Activating PowerPivot in MS Excel-2010, 2013 and 2016

Clear concept about Excel table

Creating PivotTable using PowerPivot window linking tables within the Excel workbook

Creating PivotTable using PowerPivot window linking tables/queries from external data source (from Access/SQL Server)

Creating relationships among the tables

Creating calculated column using Data Analysis Expression (DAX)

Techniques to use fields and slicers from PowerPivot Field List

Session-5

Overview of automated Excel dashboard

Techniques to create Excel dashboard

Exclusive practical session to build an interactive Excel dashboard report using Excel PivotTable, PivotChart and Slicers

Overview of the Whole Session

Consolidation of Learning

Brainstorming

Q & A Session

Who can Attend

Regular Excel users having basic Excel-2007/2010 Knowledge, data analyzers, management reporters, project managers, decision makers and teachers who wish to gather comprehensive knowledge to prepare summary reports from billions of data using PivotTable and PowerPivot.

 

Learning Methodologies

Lecture and presentation with Microsoft Excel-2016

Hand working with real case study

Completely interactive and participative

Training booklet that will be interactively connected

Dashboard Project

Open discussion (Q & A session)



Start Course