Excel 2016 – Advanced

The Excel 2016 Advanced training course builds on the techniques and concepts presented in the Intermediate course. It is designed to help you to extend your knowledge of some of the more specialised and advanced capabilities of Excel.

This course focuses on how to perform more efficient Data Analysis using features such as Pivot Tables, Data Models, Power Pivots and “What If” tools. It covers more advanced use of Calculations and Functions including using formulas to control Conditional Formats. It also looks at controlling and protecting content in a spreadsheet by use of Data Validation and selective password security. Automation using recorded Macros is also covered.

This course is designed for Microsoft Excel users who wish to develop their skills so they can use the advanced techniques to manipulate and interrogate Excel data.

Prerequisites

It is recommended that proposed delegates have attended a Microsoft Excel 2016 Intermediate course or have equivalent knowledge.

FUNCTIONS AND CALCULATIONS
Extending the scope of formulas by using functions such as SUMIF, COUNTIF, IFERROR
Creating multi-level formulas by “nesting” functions.
Naming cells or cell ranges
Using Names in formulas
Limiting the scope of Names and managing Names in a workbook.
Using Array formulas
Using a formula to control Conditional Formats

CONTROLLING CONTENT
Applying Data Validation
Using levels of password protection to control content in spreadsheet.

USING WHAT IF ANALYSIS
Setting up and Using Goal Seek
Creating and Saving Scenarios
Loading and Viewing Scenarios
Creating a Data Table of results

WORKING WITH SOLVER
Enabling Solver Add-in
Setting Constraints in Solver
Modifying Constraints
Setting Solver Options
Generating Reports and Scenarios with Solver

ADVANCED USE OF PIVOT TABLES
Using Data Tables in the creation of Pivot Table reports
Using the “summarize values by” & “show values as” to adjust and customise the Pivot Table.
Using calculated fields and items in a Pivot Table.
Creating a Pivot Chart and using it to pivot data in report.
Using drill-down features including new pivot chart drill down buttons.
Using slicers and timelines to filter data in the report

DATA MODELS AND POWER PIVOTS
Using the Data Model feature to pivot data from multiple Excel sources.
Adding to the Data Model
Creating pivot table reports from the Data Model
Using Power Pivot
Importing data from an external source (such as access)
Use of new automatic relationship detection.
Pivoting data in Power Pivot
An explanation of the new “get and transform” group on the data tab.

USING MACROS
Purpose of a Macro
Recording and naming a macro
Editing the recorded code in the visual basic editing window.
Running a macro
Assigning a macro to a button on the ribbon or the QAT