Duration
1 day
Overview
This Advanced Microsoft Excel 2016 training class is designed for students to gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros.
Target Audience
Students who have intermediate skills with Microsoft Excel 2016 who want to learn more advanced skills or students who want to learn the topics covered in this course in the 2016 interface.
Module 1: Managing Data
Consolidating Information
Removing Duplicates
Configuring Data Validation
Converting Text to Columns
Module 2: Outlining and Grouping Data
Outlining Data
Showing and Hiding Outline Details
Grouping Data
Creating Subtotals
Removing Outlining and Grouping
Module 3: Data Visualization
Understand Types of Charts
Box & Whisker compares sets of data
Creating Treemap Chart
Creating Waterfall Chart
Module 4: Analyzing Data
Using Goal Seek
Using Data Tables
Module 5: Using Solver
Setting up the Worksheet
Running Solver
Generating Reports and Scenarios with Solver
Modifying Constraints
Setting Solver Options
Module 6: PivotTable Features
Creating a Basic PivotTable
Creating a Basic PivotChart
Using the PivotTable Fields Pane
Adding Calculated Fields
Sorting Pivoted Data
Filtering Pivoted Data
Using Slicers
Creating Timelines
Module 7: LOOKUP & REFERENCE FORMULAS
USE VLOOKUP TO FIND SPECIFIC DATA
USE HLOOKUP TO FIND VALUES IN ROWS
USE INDEX & MATCH TO RETRIEVE DATA
Programme scope, delivery arrangements and suitability are confirmed during enquiry.