Lesson 1: Organising Worksheet Data
- Apply Basic Sorting to a Data Range
- Advanced Sorting
- Summarise Data with Subtotals
Lesson 2: Analysing Data using Filters and Outlines
- Autofilters
- Advanced Filters
- Use Database Functions
- Use Outlines to Organise Data
Lesson 3: Use Conditional Formatting
- Preset Conditional Formatting
- Custom Conditional Formatting
- Sorting and Filtering using Conditional Formats
Lesson 4: What If Analysis
- Creating a Single Input Data Table
- Creating a Two-Variable Data Table
- Creating Scenarios
- What If Analysis using Goal Seek
- Consolidating Data
- Link Workbooks using Formulas
Lesson 5: Getting the Data to your Workbook
- Import or Connection
- Data from other Files
- Retrieving Data from other Applications
- Using Web Based Data
Lesson 6: Preparing Data for Analysis
- Data Cleansing and Manipulation Tools
- Introduction to Data Tables
- Creating and Working with Data Models and Data Relationships
- Workbook Analysis with Inquire
Lesson 7: Using Data Tables, Slicers and Functions
- Data Tables and Slicers
- Analysis with Excel Functions and Data Validation
Lesson 8: Working with the Data Visually
- Quick Visual Analysis
- Visualise Trends with Sparklines
- Using Charts and Trendlines
- Lines and Error Bars
Lesson 9: Working With PivotTables
- Introducing PivotTables
- Creating PivotTables from a Single Table or List
- Filtering, Grouping and Summarising Data in a PivotTable
- Creating PivotTables using Workbook Relationships
- Creating PivotTables with External Data Model Connections
- Using Sets, Calculated Fields and Calculated Items
- Slicers and Timelines in PivotTables
Lesson 10: PivotCharts
- Create PivotCharts
- Creating Decoupled PivotCharts
- Shaping and Filtering the Data using PivotCharts
Lesson 11: Power View Reports
- Creating Power View Reports
- Using Tables, Cards and Matrices
- Charting in Power View Reports
- Mapping Geo-Data
- Power View Options
Lesson 12: Introduction to Microsoft Power Query
- What is Power Query?
- Power Query Data Sources
- Data Import Process and Query Editor Overview
- Data Transforms using the Query Editor
- Column, Row and Calculated Transform Actions
- Combining Data Sources as a Mash Up
Lesson 13: Introduction to PowerPivot
- Concepts and Components
- Working with the Manage Console
- Creating and Editing Data Relationships
- Calculated Fields and KPIs
- Creating a Pivot Based Dashboard from PowerPivot