Wednesday, August 6, 2014

Work: Excel 201 (Pivot Tables)

That's right... not 101. This is probably an Excel Pivot Table 101, although it does assume you know how to create and use basic Pivot Tables so maybe 102. Anywho...

I found this great video that demos a lot of features (by Mike Alexander with DataPig Technologies) that I did not know was possible (although I have very little exposure to pivot tables). It is a rather long video so I tried to break it down to the parts that it demonstrates.

Excel 2010 (with some mentions to 2003)

Video History

(I only provide notes for 2010. See video for 2003)
I don't like the player. It is hard to navigate to the specific sections.

00:30 Customize Pivot Table
03:20 Including items with no values
05:00 Preserve Formatting
06:00 Show Value As Options -  Percentage of Total, Running Total
09:30 Sorting, Top 10, Filtering
11:25 Custom Row, Column Sorting
13:05 Grouping - Date by Month, Quarter, Year
16:10 Grouping - By weeks
17:55 Histogram
21:15 Auto Filter Pivot Table
23:20 Applying Calculations to Filter Tables
32:00 Multiple Consolidation Range Pivot Table
35:20 Running Show Pages

Key Information

Many of these features are good for sales analysis (I think). Since I am working on release analysis metrics, I did not need all these features. The main features that are very useful are Grouping and Applying Calculations to Filter Tables.

Steps

Including items with no values - Field Settings > Layout & Print > Show items with no data
Preserve Formatting  - Right Click > Pivot Table Options > Layout & Format > Auto-fill column widths on update
Customize Pivot Table - Directly edit column title in spreadsheet or Right Click Title > Value Field Settings
Auto Filter Pivot Table - Select cell off data > Data > Filter
Running Show Pages - Options > Options > Show Report Filter Pages


Reference

1 - http://www.datapigtechnologies.com/Webinars/PivotTableTips.htm