Excel Advanced
Course Duration: One Day
Target Audience:
Aimed at experienced Excel users who need to create and manipulate more complex models using the advanced features of Excel.
Course Objectives:
To create complex models involving multiple linked spreadsheets and files, analysing data using what ifs and scenarios, validating, and auditing data and automating common processes.
Course Outline:
Course Introduction
-
One Click Forecasting
-
The “Tell me what you want to do…” Tool
-
Smart Lookup
-
Quick Analysis Tools
Review of Intermediate Level
-
If Statement / Nested If
-
Absolute cell referencing
-
Named ranges
-
SUMIF / SUMIFS
Lookup and Information Functions
-
Vertical Lookup (VLOOKUP)
-
Horizontal Lookup (HLOOKUP)
-
Cross Lookup (XLOOKUP)
-
INDEX MATCH and OFFSET
-
ISTEXT, ISVALUE, ISNULL, ISERROR
-
ISNA, ISDATE, IFERROR
Summarising Data with Pivot Tables
-
Inserting calculated fields
-
Changing value field settings
-
Using report filters
-
Changing the scope of the data source
-
Pivot table options
-
Using slicers for effective filtering
-
Using timelines
-
Creating Pivot Charts
What If Analysis Tools
-
Scenarios
-
Custom Views
-
Goal Seek
-
Scenario Manager
-
Data Tables
Formula Auditing and Formula View
-
Tracing precedents
-
Tracing dependents
-
Using a Watch Window
-
Go To Special
Protecting and Sharing
-
Sharing a file
-
Tracking changes
-
Applying data validation rules
-
Protecting cells, sheets, files
-
Password-Protecting a file
Working with Tables
-
Advantages of Excel tables
-
Techniques
-
Structured references
Advanced Charts
-
Saving custom charts as templates
-
Applying trend lines
-
Formatting and editing series, plot area, data points
Introduction to Macros
-
Displaying the Developer Tab
-
Overview and Purpose of Macros
-
Where to save Macros
-
Absolute and relative recording
-
Running Macros
-
Assigning to the Quick Access Toolbar
-
Assigning to shapes or pictures
-
Keyboard shortcuts for Macros
Advanced Filtering and Sorting
-
Text filters
-
Date filters
-
Numeric filters
-
Advanced sub-totals