Microsoft Excel Advanced Pivot Tables - 1 Day Course - Sydney

Use complex functionalities of Pivot Tables and analyse multiple files using the Power Pivot Add-in.

This course is ideal for

This course has been developed for people wanting to use more complex functionality of PivotTables within Microsoft Excel, including the PowerPivot add-in which allows you to analyse multiple files.

What you will learn

• Create and modify existing PivotTables.

• Work with calculated fields and summary tools.

• Analyse multiple data sources with the PowerPivot add-in.

• Create simple dashboards.

Topics covered in this training course

Basic PivotTable review

• What is a PivotTable?

• Prepare your data

• PivotChart revision

• Filtering data

• Formatting values

• PivotTable value field properties

• Produce multiple PivotTables

• Grouping date data

• PivotTable format and display

• Classic view

• Create PivotCharts

PivotTable properties

• Dynamic data ranges

• Use tables as data sources

• Dealing with zeros

• Data drill down

• Refresh PivotTable data

• Change the data source

• Move a PivotTable

PivotTable views

• Manually sort data

• Group fields

• Work with Slicers

• Work with timelines (2013)

• Conditional Formatting

Subtotals and summarising

• Manipulate display of totals

• Advanced value field settings

PivotTable calculations

• Calculated fields

• Problems with calculated fields

• Calculated items

• GETPIVOTDATA function

PowerPivot

• Install PowerPivot (2010)

• Activate PowerPivot

• Understand PowerPivot

• Import a text file into PowerPivot

• Copy data into PowerPivot

• Create relationships between data

• Create a PivotTable in PowerPivot

Create a dashboard with PowerPivot and Slicers

• Collect and link data

• Create PivotCharts

• Create a Slicer connected to multiple sources

• Link Slicer values to cells