Excel Advanced Course
(4.82 out of 5) 7707 Student Reviews
About the Excel Advanced Course
This course covers aspects of Microsoft Excel beyond building, summarising and charting everyday workbooks, and deals with advanced calculation, data control, modelling and automation. Instructors teach advanced techniques in areas such as reference and text functions, advanced dynamic array formulas, data validation, what-if analysis and Solver, linking and consolidation, protection, advanced PivotTable features and macros.
The emphasis is on building workbooks that are accurate, controlled and repeatable, on modelling outcomes before committing to them, and on producing reporting that can be refreshed rather than rebuilt. Training is delivered through business scenarios with hands-on practical work throughout.
Who should do this course?
This course is intended for experienced Microsoft Excel users who wish to extend their knowledge and skills beyond summarising and presenting data.
Upon successful completion of this course, students will be able to:
- ▪Nest functions to create complex formulas
- ▪Test budget and pricing scenarios before deciding (Goal Seek and Solver)
- ▪Set up drop-down lists so data is entered correctly (data validation)
- ▪Cross-reference large datasets to match information in one step (INDEX and MATCH)
- ▪Build advanced dynamic array formulas and simplify them with LET
- ▪Extend PivotTables with calculated fields, grouping and custom calculations
- ▪Combine data in separate worksheets or workbooks using consolidation
- ▪Clean up messy data exported from other systems (text functions)
- ▪Automate repetitive tasks (macros) and protect workbooks from changes
Prerequisites
Participants should have already completed both the Excel Beginner and Excel Intermediate courses before attending this course, or have equivalent skills. Familiarity with cell referencing, Excel Tables, lookup functions, dynamic arrays and PivotTables is assumed.
Excel Advanced Course Details
$385 incl GST
- Duration:1 Day
- Max. Class Size:10
- Avg. Class Size:5
-
Study Mode:
Classroom Online Live
- Level:Advanced
- CPD Hours:6 hours
- Course Times: Classroom: 9.00am - 5.00pm approx(Local Time) Online Live: 9.00am - 5.00pm approx(AEST or AEDT)
- Download Course PDF
Excel Advanced Course Dates
Looking for Excel Advanced dates?
There are currently no scheduled dates matching your selected filter, but we'd love to assist. Get in touch with our team to discuss your individual training needs or corporate group requirements, and we'll explore available options with you.
Excel Advanced Course Content
⊕ Expand full topic list
Unit 1: Setting Excel Options
- Understanding Excel Options
- Personalising Excel
- Setting Formula Options
- Understanding and Setting Save Options
- Setting the Default File Location
- Setting Advanced Options
⊕ See more
Unit 2: Advanced Lookup and Reference Functions
- Reviewing XLOOKUP and XMATCH
- Nesting XLOOKUP for Two Way Lookups
- Using INDEX
- Using MATCH
- Combining INDEX and MATCH
- Performing a Two Way Lookup with INDEX and MATCH
- Understanding Reference Functions
- Using ROW, ROWS, COLUMN and COLUMNS
- Using INDIRECT
- Using OFFSET
- Building Dynamic Ranges with OFFSET and INDIRECT
⊕ See more
Unit 3: Advanced Dynamic Array Techniques
- Reviewing Dynamic Array Behaviour and Spill Ranges
- Nesting FILTER, SORT and UNIQUE
- Filtering Against a List of Values
- Filtering with Criteria from Another Table
- Handling Empty Results in Nested Formulas
- Using SEQUENCE to Build Calculated Series
- Simplifying Formulas with the LET Function
- Creating Charts That Update from a Spill Range
- Troubleshooting Spill and Reference Errors
⊕ See more
Unit 4: Text Functions
- Understanding Text Functions
- Using the PROPER, UPPER and LOWER Functions
- Joining Text with CONCAT and the Ampersand Operator
- Using the TEXTJOIN Function
- Using the LEFT and RIGHT Functions
- Using the MID Function
- Using the LEN Function
- Using the FIND and SEARCH Functions
- Using the SUBSTITUTE Function
- Using the TRIM and CLEAN Functions
- Using the TEXT Function
- Using the VALUE Function
⊕ See more
Unit 5: Information Functions
- Understanding Information Functions
- Using the ISBLANK Function
- Using the ISERROR and ISNA Functions
- Using the ISNUMBER and ISTEXT Functions
- Using the CELL Function
⊕ See more
Unit 6: Financial Functions
- Understanding Financial Functions
- Using PMT
- Using FV
- Using PV
- Using NPV
- Using RATE
⊕ See more
Unit 7: Complex Formulas
- Scoping a Formula
- Preparing for Complex Formulas
- Creating the Base Formula
- Adding More Operations
- Editing a Complex Formula
- Copying Nested Functions
- Tracing Precedents and Dependents
- Using Evaluate Formula
- Using the Watch Window
- Switching to Manual Recalculation
- Pasting Values from Formulas
- Documenting Formulas
⊕ See more
Unit 8: Data Validation
- Understanding Data Validation
- Creating a Number Range Validation
- Creating a Date and Time Validation
- Creating a Text Length Validation
- Creating a Drop Down List
- Creating a Drop Down List from a Table
- Creating Dependent Drop Down Lists
- Building a Drop Down List from a Dynamic Array
- Using a Formula as a Validation Rule
- Creating Input Messages and Error Alerts
- Circling Invalid Data
- Copying and Removing Validation
⊕ See more
Unit 9: What If Analysis
- Understanding What If Analysis
- Understanding Goal Seeking
- Using Goal Seek
- Goal Seeking with a Loan Calculation
- Creating a One Variable Data Table
- Creating a Two Variable Data Table
- Understanding Scenarios
- Creating a Scenario
- Displaying and Editing Scenarios
- Creating a Scenario Summary Report
- Understanding Solver
- Installing and Starting Solver
- Setting Solver Constraints
- Running Solver and Generating Reports
⊕ See more
Unit 10: Grouping and Outlining
- Understanding Grouping and Outlining
- Creating an Automatic Outline
- Working with an Outline
- Creating a Manual Group
- Grouping by Columns
- Clearing an Outline
⊕ See more
Unit 11: Data Linking and Consolidation
- Understanding Data Linking
- Linking Between Worksheets
- Linking Between Workbooks
- Updating Links Between Workbooks
- Managing and Breaking Links
- Using 3D References Across Worksheets
- Understanding Data Consolidation
- Consolidating with Identical Layouts
- Creating a Linked Consolidation
- Consolidating from Different Layouts
⊕ See more
Unit 12: Protecting Data
- Understanding Data Protection
- Protecting a Worksheet
- Working with a Protected Worksheet
- Disabling Worksheet Protection
- Providing Restricted Access to Cells
- Protecting Workbook Structure
- Password Protecting a Workbook
- Removing a Password from a Workbook
⊕ See more
Unit 13: Advanced PivotTable Features
- Working with PivotTable Grand Totals and Subtotals
- Finding the Percentage of Total
- Finding the Difference From
- Creating Running Totals
- Grouping in PivotTable Reports
- Grouping by Dates and Numeric Ranges
- Creating Calculated Fields
- Creating Calculated Items
- Sorting in a PivotTable
- Working with PivotTable Options
- Using GETPIVOTDATA
⊕ See more
Unit 14: Importing and Exporting Data
- Understanding Data Importing
- Working with Text File Formats
- Importing Comma Delimited Text
- Importing Tab and Space Delimited Text
- Splitting Data with Text to Columns
- Introducing Get and Transform
- Refreshing and Unlinking Connections
- Exporting Data as Text
- Exporting to Microsoft Word
- Exporting to PDF
⊕ See more
Unit 15: Recorded Macros
- Understanding Excel Macros
- Setting Macro Security
- Saving a Document as Macro Enabled
- Recording a Simple Macro
- Running a Recorded Macro
- Understanding Relative Cell References
- Running a Macro with Relative References
- Viewing and Editing a Macro
- Assigning a Macro to the Ribbon or Toolbar
- Assigning a Keyboard Shortcut to a Macro
- Copying and Deleting Macros
⊕ See more
Training Packages
Microsoft Training Package
Book any two 1-day Microsoft Office courses and receive a third 1-day Microsoft Office course for FREE!
Related Courses
Student Reviews
I found Sean a fantastic teacher and has the perfect voice for this. Also he really went out of his way to answer everyone's questions and made me feel so comfortable.
The course & instructor were fantastic, if there is any criticism, is the session after lunch of 2 hours felt a bit long.
I was nervous that this would have too much unnecessary information for my needs. On the contrary, Duane made it very interesting and I am taking away a lot that I will be able to use. Thank you
This session has been very helpful. Most of my work is with pivot tables. I feel very comfortable to now go to work and complete my tasks at a high level. Loved learning about slicers too.
The course was fantastic and very informative. Adam was an excellent instructor who explained the concepts clearly and provided helpful examples. I learned a lot and really enjoyed the course.
The tutor is good and very patient. but I am not quite keeping up with all the course. maybe short of the pareparation. May be apply another chance to attend the course next month.
Excellent, I was able to learn new formulas to assists in my data management. Sean was a fantastic facilitaor
Really useful course, instructor Jill was engaging. We learned alot especially in areas of Pivot tables and Power pivot in particular.
Adam is a great trainer. He is very clear in explaining the functions and details. I learned a lot from this course. Truly recommended.
I found this course extremely informative. The instructor was extremely personable and easy to understand and explained/demonstrated solutions to participants query's in excellent detail. I have gained invaluable knowledge in excel that I will be able to utilise in my every work. Skills gained from todays course has definitely given me some extra tools that will assist me compile some of the regulatory reporting we are required to produce for our industry.
loved the course (except macros - that lost me). Suggest breaking it up over 2 half days though. Jay was very easy to understand.
Excellent course content and well delivered. Learnt a lot which I can put into practice within my role.
Godd experienced teacher who explained things very thoroughly. I found the course the best Excel Advanced course I have taken part in. I have been in courses where there was that much content that you couldn't absorb it all and the trainer rushed through the content and left half of the class behind.
This course was very valuable. It covered a lot of content that I would use practically in my workplace. Chris was a great instructor.
Wayne is a tremendous trainer. He is not only informative, but lively, which really helps during a long day at the screen. One of the best I've had!
Enquire Now
Fill in your details to have a training consultant contact you to discuss your training needs.
Note: Form fields marked with * are required.
Save $385
Book any two 1-day Microsoft Office courses and receive a third 1-day Microsoft Office course for
FREE!
For more info please
Call 1300 888 724