Microsoft Excel 2016 Master Class with Data Analysis and Power Tools Training in Hong Kong

• 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 QAEX16INT Module 3 and QAEX16ITM Module

Dual monitor setup is required for when attending this course via the QA Attend from Anywhere delivery method..

Learning Outcomes

• Organise worksheet data so that data can be sorted and filtered
• Apply Conditional Formatting
• Import data into Excel 2016
• Create connections to external data sources
• Use Workbook Analysis
• Utilise new Excel 2016 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

Course Outline

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

• Pre-set 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
• Creating a Forecast Sheet
• 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 Tables
• Creating and working with Data Models and Data Relationships
• Workbook Analysis with Inquire

Lesson 7: Using Tables, Slicers and Functions

• 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