Advanced Pivot Table

1.    Understanding the concepts of Pivot Table

1)    Drill Down to underlying detail

2)    Slice & Dice in Pivot Table

3)    Issue about filtering pivot table by selection

4)    Filter pivot table by rules

5)    Retain Items deleted from data source

2.    Handling dynamic data source

1)    Application of table based method

2)    Application of formula driven method

3.    Handling multiple data sources

1)    Consolidation of multiple data sources into one Pivot Table

2)    Limitation of using table as data source

4.    Grouping & Sorting

1)    Grouping items by Date & Time

2)    Grouping items by Number

3)    Grouping items by Text

4)    Issues caused by ungroup/multiple grouping criteria

5)    Sorting items with customised order

5.    Formatting Pivot Table

1)    Case: Highlight top 10 Sales among branch & month

2)    Case: ‘Data’ visualisation

3)    Apply conditional format in Pivot Table

4)    Display text value in value area

6.    Embedding calculation in Pivot Table

1)    Item & Field concept

2)    Insert and sort calculated item (create hierarchy)

3)    Use Calculate Field to Calculate Unit Cost

4)    Case: build FBT model

5)    Documentation

7.    Applying Pivot Table in business analysis

1)    Month on Month analysis – month compared to the same month a year ago

2)    Month over Month analysis – month compared to the previous month

3)    Fixed base analysis – month compared to the chosen base month

4)    Show running total & ranking

8.    Applying Pivot Table in price control

1)    Fine the latest price of each product

2)    Find the price changing frequency of each product

3)    Track the trend in price variation for each product

9.    Best Practices (Dos and Don’ts) in Pivot Table