About Workshop
Pivot tables are the single most powerful command in all of Excel. With a pivot table, you can take 1 million rows of transactional data and transform it into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables allow you to change your analysis on the fly by simply moving fields from one area of a report to another. No other tool in Excel gives you the flexibility and analytical power of pivot tables.
It is widely agreed that close to 50 percent of Excel users leave 80 percent of Excel untouched. That is, most users do not tap into the full potential of Excel’s built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for more than 15 years, they remain one of the most underutilized tools in the entire Microsoft Office Suite. Having picked up this book, you are savvy enough to have heard of pivot tables or even have used them on occasion. You have a sense that pivot tables have a power that you are not using, and you want to learn how to leverage that power to increase your productivity quickly.
After this course, you will be able to create pivot tables, increase your productivity, and produce reports in minutes instead of hours. You will also be able to output complex pivot reports with drill-down capabilities accompanying charts. Most importantly, after the course, you will be able to build dynamic pivot table reporting systems.
Learning Outcomes:
1. Gain fundamental concepts and skills of Pivot Table
2. Create, Customize, Group, Sort and Filter Pivot Data
3. Perform Calculations within Pivot Table
4. Use Pivot Charts and other Visualizations
5. Analyze Disparate Data Sources with Pivot Table
6. Share Pivot Table with Others
7. Analyse OLAP data and Mash up data with Power Pivot
Workshop topics to be covered:
Session One:
Fundamental Concepts and Skills of Pivot Table
Understand Pivot Table and its Uses
Anatomy of a Pivot Table
Pivot Table – Behind the Scene
Prepare data for Pivot Table Reporting
Session Two
Create, Customize, Group, Sort and Filter Pivot Table Data
Create Pivot Table and Use slicer in Pivot Table
Customize a Pivot Table by changing layout, appearance, styles and themes.
Change summary calculations, add/remove subtotals and Running Total, % of and Rank options
Group, Sort and Filter Pivot Data
Session Three:
Performing Calculations within Pivot Tables
Get introduced to calculated items
Creating calculated fields and items
Understanding rules of Pivot Table Calculations
Managing and Maintaining Pivot Table Calculations
Session Four:
Pivot Charts and Other Visualizations
Create a Pivot Chart
Understand the rules of Pivot Chart
Use conditional formatting with Pivot Tables
Session Five:
Analysing Disparate Data Source
Use multiple consolidation ranges
Analysing the anatomy of a Consolidation range Pivot Table
Building a Pivot Table using Microsoft Access Data
Building a Pivot Table with SQL Server Data
Session Six:
Share Pivot Tables with Others
Share Pivot Table with other versions of Office
Share Pivot Table to The Web using web Services in Excel
Create and share static image of Pivot Table
View Excel 2010 Pivot Table in Sky Drive
Session Seven:
OLAP Data and Power Pivot
Understanding OLAP and connecting to OLAP Cube
Create offline cubes and break down the Pivot Table mold with Cube function
Install Power Pivot and building a Power Pivot report
DAX calculations and other Notes
![](Templates/"../images/bodyedge_tr.gif"width="2"){kind=small}
![](Templates/"../images/bodyedge_tl.gif"width="2"){kind=small}
