Lynda.COM Excel 2010: Pivot Tables in Depth

Author:
Curt Frye

Duration:
03h 42m

Released on:
4/19/2011

Exercise files:
Yes

In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
Topics include:

    Formatting data for use in a PivotTable
    Connecting to an external data source
    Refreshing a data source
    Adding, removing, and positioning subtotals and grand totals
    Creating a calculated field
    Grouping PivotTable fields
    Clearing and reapplying PivotTable filters
    Applying field styles
    Formatting cells
    Creating a PivotChart
    Printing PivotTables and PivotCharts
    Creating relationships between tables in a PowerPivot model
    Using the DAX language for advanced summaries in PowerPivot




Introduction
Welcome           00m 55s
Using the exercise files           00m 43s

1. Creating and Pivoting PivotTables
Introducing PivotTables           04m 02s
Formatting data for use in a PivotTable           04m 31s
Creating a PivotTable           04m 20s
Pivoting a PivotTable           03m 47s
Configuring a PivotTable           03m 23s
Connecting to an external data source           03m 30s
Connecting to an Access database           02m 11s
Consolidating data from multiple sources           04m 37s
Updating and refreshing PivotTable data sources           04m 21s
Managing PivotTables           03m 26s

2. Summarizing PivotTable Data
Adding, removing, and positioning subtotals and grand totals           03m 27s
Changing the PivotTable data field summary operation           04m 35s
Summarizing more than one data field           03m 16s
Creating a calculated field           02m 27s
Grouping PivotTable fields           03m 17s
Using PivotTable data in a formula           04m 47s
Drilling down to the underlying data           01m 44s

3. Sorting and Filtering PivotTable Data
Sorting PivotTable data           02m 00s
Creating a custom sort order           02m 48s
Filtering a PivotTable field by selection           02m 59s
Filtering a PivotTable by rule           02m 56s
Filtering a PivotTable using a search filter           03m 10s
Filtering a PivotTable using slicers           04m 02s
Formatting slicers           03m 43s
Filtering a PivotTable with report filter fields           05m 02s
Clearing and reapplying PivotTable filters           01m 29s

4. Formatting PivotTables
Applying a PivotTable style           05m 05s
Creating a PivotTable style           04m 37s
Changing the PivotTable layout           03m 20s
Changing the data field number format           02m 00s

5. Applying Conditional Formatting to PivotTables
Highlighting cells by applying a rule           02m 54s
Highlighting the top or bottom values in a PivotTable           03m 30s
Formatting PivotTable cells using data bars           03m 50s
Formatting PivotTable cells using color scales           03m 40s
Formatting PivotTable cells using icon sets           02m 45s
Editing conditional formatting rules           02m 15s
Controlling how multiple rules are applied           03m 28s
Deleting a conditional formatting rule           02m 11s

6. Creating and Manipulating PivotCharts
Creating a PivotChart           03m 29s
Pivoting a PivotChart           03m 05s
Filtering a PivotChart           03m 45s
Formatting a PivotChart           03m 35s
Changing a PivotChart's layout           03m 14s
Changing a PivotChart's chart type           04m 30s
Adding a trendline to a PivotChart           02m 22s

7. Printing PivotTables and PivotCharts
Printing a PivotTable           04m 02s
Printing each item on its own page           03m 30s
Printing a PivotChart           01m 55s

8. Manipulating PivotTables Using Macros
Recording and reviewing a macro           04m 10s
Running a macro           05m 57s
Creating a simple PivotTable presentation kit           03m 23s

9. Getting Started with PowerPivot
Introducing PowerPivot           02m 09s
Downloading and installing PowerPivot           02m 36s
Importing PowerPivot data           03m 14s
Managing table columns           04m 01s
Adding tables to a PowerPivot model           02m 27s
Creating relationships between tables           04m 50s

10. Working with DAX Expressions
Introducing the DAX language           02m 58s
Using DAX operators           04m 44s
Surveying DAX functions           02m 40s
Adding calculated columns and measures           04m 22s
Using aggregate functions           04m 24s
Using filters in aggregate functions           05m 22s

Conclusion
Additional resources           00m 59s