Microsoft Excel 365 Advanced (2 Days)

This course is designed for experienced Excel users who have an excellent working knowledge of the software and wish to take their skills to another level with advanced features.

Learning Outcomes

By the end of this course students will be able to work confidently with advanced nested functions, use features like the scenario manager, consolidation, advanced filtering options as well as pivot tables and Macros.

Prerequisites

This course is designed for students with solid Excel knowledge and Experience.

Auditing Tools Usage

• Overview of Auditing Tools
• Tracer, Dependent and Precedent Arrows
• Removing Tracer Arrows
• Tracing Cells Causing Errors
• Understanding Error Messages
• #DIV/0; #N/A; #NAME; #Ref!; #Value
• Overcoming Error Messages

Using Advanced Functions

• Creating Nested If Functions
• The IFS Function
• AND and OR Functions
• Using an AND condition with IF
• Lookup Functions – VLOOKUP, HLOOKUP, Match and INDEX
• The SUMPRODUCT Function
• String Functions
• Rounding Functions
• The TRIM Function
• Removing spaces with SUBSTITUTE ()

What If Analysis

• Using the Goal Seeker
• Using Scenario Manager
• Creating a Scenario
• Displaying and Editing a Scenario
• Creating a Scenario Summary Reports
• One-Variable and Two-Variable Data Tables
• Using the Solver
• Setting Solver Constraints
• Creating Solver Reports

Worksheet Consolidation

• Consolidating Worksheets
• Consolidating by Position
• Consolidating by Category

Using Custom Views

• Creating a Custom View
• Switching between Views
• Editing a Custom View

Working with Lists and Tables

• Working with Filters
• Adding a Data Filter
• Guidelines for Creating Lists
• Applying Filters to a List
• Creating Custom Filters
• Filtering by Dates
• Sorting data with either Single Key or Multiple Sort Keys
• Working with Excel Tables
• Using Dynamic Headings
• Table Tools and Table Formats
• Creating a Custom Table Style
• Sorting & Filtering in a Table
• Sorting & Filtering by Colour
• Performing a Custom Sort
• Analysing Data in a Table
• Understanding the Table Reference
• 10 Reasons to use Tables

Advanced Filtering

• Creating a Criteria Range
• Clearing the Filter
• Using Comparison Criteria
• Using AND/OR Criteria
• Extracting Filtered Records
• Using Database Functions – DSUM, DAVERAGE, DGET

Working with PivotTables

• What exactly is a PivotTable?
• When should I use a PivotTable?
• Using an Excel List or Table
• Using Advanced Filter to Extract Data
• PivotTable Layouts
• Understanding the PivotTable Cache
• PivotTable Fields
• Select a Report Filter Item

Managing PivotTable Data

• Adding Data to the List
• Excel Tables as a Data Source
• Changing the Summary Function
• Comparing Values
• Creating Filter Reports

Modifying Appearance & Detail

• Using PivotTable Styles
• PivotTable Layout Tools
• Sorting PivotTable Items
• Subtotals and Grand Totals
• Report Layout Options

PivotTable Calculations

• Creating Calculated Fields
• Using Calculated Items
• Grouping Items
• Label/Value/Data Filters

Creating Pivot Charts

• Creating PivotChart Reports
• PivotChart Terms
• Using PivotChart Tools

Working with Slicers

• What are Slicers
• Adding Sliders
• Formatting Slicers
• Creating a Custom Style

Generating Macros

• Defining Macros
• Setting Macro Security
• Recording Macros
• Using the VB Editor
• Understanding your Code
• Setting Breakpoints
• Assigning Macros to Buttons
• Assigning Macros to Objects and Graphics
• Testing Macros