Introduction
Learn to use the advanced features of Excel to their full extent.
Duration
1 Day
Pre-Requisites
We assume you have experience with the fundamental use and features of Microsoft Excel including the ability to:
- Create formulas and basic functions
- Use Absolute Cell references
- filtering and functions
This is an advanced course, in order that you get the most from this course we recommend you have attended our Intermediate Excel course first.
Course Contents
Defined Names
- Defining Names From Worksheet Labels
- Using Names in Typed Formulas
- Applying Names to Existing Formulas
- Creating Names Using the Name Box
- Using Names to Select Ranges
- Pasting Defined Names Into Formulas
- Defining Names for Constant Values
- Creating Names From a Selection
- Scoping Names to a Worksheet
- Using the Name Manager
- Documenting Defined Names
Logical Functions
- Using IF With Text and Numbers
- Nesting IF Functions
- Using IFERROR
- Using TRUE and FALSE
- Using AND / OR / NOT
Validating Data
- Creating a Number Range Validation
- Testing a Validation
- Creating an Input and Error Message
- Creating a Drop Down List
- Using Formulas as Validation Criteria
- Circling Invalid Data
- Removing Invalid Circles
- Copying Validation Settings
Lookup Functions
- Using CHOOSE
- Using XLOOKUP
- Using INDEX
- Using Match
- Understanding Reference Functions
- Using ROW and ROWS
- Using COLUMN and COLUMNS
- Using ADDRESS
- Using INDIRECT
- Using OFFSET
Summarising and Subtotalling
- Creating Subtotals
- Using a Subtotalled Worksheet
- Creating Nested Subtotals
- Copying Subtotals
- Using Subtotals With AutoFilter
- Creating Relative Names for Subtotals
- Using Relative Names for Subtotals
PivotTables
- Recommended Pivot Tables
- Creating Your Own PivotTable
- Defining the PivotTable Structure
- Filtering a PivotTable
- Clearing a Report Filter
- Switching PivotTable Fields
- Formatting a PivotTable
- Understanding Slicers
- Creating Slicers
- Inserting a Timeline Filter
PivotTable Features
- Using Compound Fields
- Counting in a PivotTable
- Formatting PivotTable Values
- Working With PivotTable Subtotals and Grand Totals
- Finding the Percentage of Total
- Finding the Difference From
- Grouping in PivotTable Reports
- Creating Running Totals
- Creating Calculated Fields
- Providing Custom Names
- Creating Calculated Items
- PivotTable Options
- Sorting in a PivotTable
Controls
- Preparing a Worksheet for Controls
- Adding a Combo Box Control
- Changing Control Properties
- Using the Cell Link to Display the Selection
- Adding a List Box Control
- Adding a Scroll Bar Control
- Adding Option Button Controls
- Adding a Group Box Control
- Adding a Check Box Control
- Protecting a Worksheet With Controls
Data Consolidation
- Consolidating With Identical Layouts
- Creating a Linked Consolidation
- Consolidating From Different Layouts
- Consolidating Data Using the SUM Function
Scenarios
- Creating a Default Scenario
- Creating Scenarios
- Using Names in Scenarios
- Displaying Scenarios
- Creating a Scenario Summary Report
- Merging Scenarios
Solver
- Installing the Solver Add
- Setting Solver Parameters
- Adding Solver Constraints
- Performing the Solver Operation
- Running Solver Reports
Importing and Exporting
- Understanding Text File Formats
- Importing Comma Delimited Text
- Exporting to Microsoft Word
Recorded Macros
- Setting Macro Security
- Recording a Simple Macro
- Relative Cell References
- Running a Macro With Relative References
- Viewing a Macro
- Editing a Macro
- Assigning a Macro to the Ribbon
- Assigning a Keyboard Shortcut to a Macro