 |
$440 | 1 Day Group discounts available |
|
 |
Times | 9:00am - 4:30pm | |
 |
Reference | Hosted manual included or Purchase printed book |
|
 |
Certificate | Attendance or Completion upon assessment |
|
 |
Classroom | Upon request | |
 |
Live Online |
Hands-on, Live Instructor Dedicated VILT Platform |
|
 |
On Site | Get a Quote |
Advanced Excel Courses
Learn advanced formulas, functions and types of financial analysis to be an Excel power user. Develop the skills to cleanse imported data from external sources, perform what-if analysis using Goal Seek, Solver and advanced Data Tables. Automate repetitive tasks using Macros and VBA. For a more comprehensive use of VBA, refer to our Excel Visual Basic Course outline.
We have 3 advanced level courses - each with a specific business focus.
Advanced Excel covers a broad range of advanced tools.
Advanced Excel Functions focuses on a variety of advanced Excel Functions
Excel VBA is a comprehensive course for those who are serious about automation of spreadsheets in Excel and across the Office applications
Advanced Excel
4.4 (2720 reviews)
$440
Brochure
Advanced Excel
This one-day advanced Microsoft Excel course is perfect for experienced Excel users who want to further develop their Excel skills to the peak of the software’s ability. This interactive course includes plenty of hands-on exercises to help you retain and start applying what you learn straight away in the workplace. You will learn advanced Excel skills like how to use advanced functions, PivotTables, Goal Seek & Scenarios, and basic automation with macros.
Improve your efficiency with Excel using a variety of advanced features
Use advanced functions to easily summarise data
Master functions to cleanse data to enable it be used with filters, functions and PivotTables
Create a range of PivotTables to aggregate data and provide different views of the data
Perform What-if analysis with a variety of tools
Automate repetitive task using a macro.
Course Details
Audience:
The course has been designed for experienced users of Excel who wish to used the advanced features of Excel.
Prerequisites:
Participants must be confident users of Excel who are able to create and manage multiple sheet workbooks.
Course Content
Advanced Functions
- SUMIF/SUMIFS, COUNTIF/COUNTIFS
- VLOOKUP, MATCH and INDEX
- SUMPRODUCT
- Array formulas
Auditing Tools
- Overview of Auditing Tools
- Tracer, Dependent and Precedent Arrows
- Removing Tracer Arrows
- Tracing Cells Causing Errors
- Understanding Error Messages
- #DIV/0; #NA; #Name; #Ref!, #Value
- Overcoming Error Messages
Data Cleaning Tools
- Importing data from external sources
- Remove duplicates
- Remove formatting
- Remove leading or trailing spaces with TRIM()
- Convert numbers stored as text
- Convert numbers to text TEXT()
What If Analysis
- Using Scenario Manager
- Creating a Scenario
- Displaying and Editing a Scenario
- Creating a Scenario Summary Report
- Using Goal Seek
- Data Tables
- One Variable
- Two Variables
- Using Solver
- Creating a Solver Report
Table References
- Understanding Structured References
- Using Table References with Data Validation
- Creating dynamic charts with tables
Advanced Sorting
- Sorting on Multiple Columns
- Performing Custom Sorts
- Sorting Left to Right
Advanced Filters
- Using a Criteria Range
- Clearing the Filter
- Using Comparison Criteria
- Using AND OR conditions
- Extracting Filtered records
- Using Database functions
- Dsum, Daverage, Dget
Pivot Table Reports
- Creating a PivotTable
- Adding PivotTable Report Fields
- Editing and Refreshing a PivotTable Report
- Changing The Summary Function
- Moving Pivottable Report Fields
- Hiding And Unhiding Items
- Adding Multiple Data Fields
- Custom Calculations In Pivot Tables
- Creating Calculated Fields and Calculated Item
- Grouping/Ungrouping Items
- Sorting A Pivot Table
- Filtering using Slicers
- Using PivotTable Styles, Themes and Colours
- PivotTable Layout Tools
- Creating PivotCharts
Macros
- Defining Macros
- Recording and running a Macro
- Personal Workbook
- Visual Basic Editor Understanding Your Code
- Running Code In VBA Environment
- Recording a Relative Record
- Debugging a Macro
- Correcting Errors
- Assigning A Macro To A Menu
- Deleting A Macro From A Menu
- Assigning A Macro To a Button
Excel Advanced Functions
Excel Advanced Functions
| Event | Event Date | Location | Individual Price | Register |
|---|---|---|---|---|
| Excel Functions & Formulas Advanced | 05-03-2025 | Live Online | $475.00 |
Excel VBA Automation
Available Excel VBA courses
Excel VBA courses are conducted one day per week over 3 weeks to allow participants to practice the content before continuing. Practice activities will be assigned at the completion of each session.
| Event | Event Date | Location | Individual Price | Register |
|---|---|---|---|---|
| Excel Programming & Automation VBA | 23-01-2025 | Live Online | $1,495.00 | |
| Excel Programming & Automation VBA | 17-03-2025 | Live Online | $1,495.00 | |
| Excel Programming & Automation VBA | 26-05-2025 | Live Online | $1,495.00 |