• Please note that this course is not suitable for new Excel users
• Work with absolute references
• Create named ranges
• Be able to create formulas using standard aggregate and specialized functions as covered in QAEX13INT Module 3 and QAEX13ITM Module 1.

• Organise worksheet data so that data can be sorted and filtered
• Apply Conditional Formatting
• Import data into Excel 2013
• Create connections to external data sources
• Use Workbook Analysis
• Utilise new Excel 2013 features to analyse data
• Present external and Excel based organisational data using a combination of graphical and data views
• Create PivotTables and analyse data
• Connect to and shape external data using Power Query in Excel
• Use PowerPivot for data connection and analysis
• Use Power Map to create visualisations of geo-data

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