Share Description
In this course, you will learn :
- How to use PivotTables in Microsoft Excel to summarise, sort, count, and chart your data. Curt demonstrates how to navigate the complexities of PivotTables while maximising their power.
- This course teaches you how to create PivotTables from a single or multiple data source, how to add calculated fields, filter your results, and format your layout to make it more readable.
- Learn how to use macros, DAX expressions, and the PowerPivot add-in to enhance PivotTable for analysing millions of rows of data.
Syllabus :
1. Creating and Pivoting PivotTables
- Introducing PivotTables
- Formatting data for use in a PivotTable
- Creating a PivotTable
- Creating a Recommended PivotTable
- Pivoting a PivotTable
- Configuring a PivotTable
- Connecting to an external data source
- Consolidating data from multiple sources
- Managing PivotTables
2. Summarizing PivotTable Data
- Managing subtotals and grand totals
- Changing the data field summary operation
- Summarizing more than one data field
- Creating a calculated field
- Grouping PivotTable fields
- Using PivotTable data in a formula
- Drilling down to the underlying data
3. Sorting and Filtering PivotTable Data
- Sorting PivotTable data
- Creating a custom sort order
- Filtering a PivotTable field by selection
- Filtering a PivotTable by rule
- Filtering a PivotTable using a search filter
- Filtering a PivotTable using slicers
- Formatting slicers
- Filtering with report filter fields
- Clearing and reapplying PivotTable filters
4. Formatting PivotTables
- Applying a PivotTable style
- Creating a PivotTable style
- Changing the PivotTable layout
- Changing the data field number format
5. Applying Conditional Formats to PivotTables
- Highlighting cells by applying a rule
- Highlighting the top or bottom values in a PivotTable
- Formatting cells using data bars
- Formatting cells using color scales
- Formatting cells using icon sets
- Editing a conditional formatting rule
- Controlling how multiple rules are applied
- Deleting a conditional formatting rule
6. Creating and Manipulating PivotCharts
- Creating a PivotChart
- Pivoting a PivotChart
- Filtering a PivotChart
- Formatting a PivotChart
- Changing a PivotChart's layout
- Changing a PivotChart's chart type
- Adding a trendline to a PivotChart
7. Printing PivotTables
- Printing a PivotTable
- Printing headers at the top of each printed page
- Printing each item on its own page
- Printing a PivotChart
8. Manipulating PivotTables Using Macros
- Recording and reviewing an Excel macro
- Running an Excel macro
- Creating a simple PivotTable presentation kit
9. Starting with PowerPivot and the Data Model
- Enabling PowerPivot in Office 2013 Professional Plus
- Introducing PowerPivot
- Importing PowerPivot data
- Adding tables to the data model
- Managing table columns
- Creating relationships between tables
- Creating a PowerPivot PivotTable
- Filtering data using a timeline
10. Introducing Data Analysis Expressions (DAX)
- Introducing the DAX language
- Using DAX operators
- Surveying DAX functions
- Adding calculated columns
- Adding calculated fields
- Creating aggregate calculations
- Creating filtered calculations
11. Visualizing Power View Data Using Matrices, Cards, and Tiles
- Starting out with Power View
- Installing Microsoft Silverlight
- Creating a table or matrix
- Creating a card
- Creating a tile
- Filtering Power View objects
- Formatting Power View objects
12. Visualizing Power View Data Using Charts and Maps
- Creating a column or bar chart
- Creating a pie chart
- Creating a line chart
- Creating a map
- Creating maps with multivalue series
- Creating chart multiples
