Microsoft Excel – Advanced
Course Description
In this 2-day advanced Microsoft Excel course, you will explore a wide range of Excel features designed to help you manage complex spreadsheets and enhance your productivity. You’ll work with text, date & time formulas, lookup and financial functions, and logical formulas such as IF, AND, and OR. The course includes practical techniques for troubleshooting formula errors, defining and managing named ranges, applying conditional formatting, and using data validation to control data input.
You’ll also learn to perform scenario analysis using tools like Goal Seek and Solver, and consolidate data from multiple ranges or categories. For reporting and analysis, the course covers building interactive PivotTables and PivotCharts, using slicers to filter data visually, and creating basic macros to automate recurring tasks.
By the end of this course, you will be able to apply a range of advanced Excel techniques to organize, validate, and analyse data more effectively. You’ll gain practical skills in using formulas and data tools, generating summary reports with PivotTables, and streamlining repetitive actions—improving your ability to work confidently with large datasets and support everyday business analysis tasks.
Prerequisites
To enrol in this course, you should possess the following:
- (≥ 16 years old); or
- 1 year working experience
Regularly use Excel to create, edit & format worksheets. Able to create formulas with relative & absolute references, use basic IF function & manage worksheets.
Course Objectives
Upon completion of the 2-day course, you will optimize and realised the great potential of Microsoft Excel. You will learn to:
- Use and Manage defined Names. Display & trace Formulas and understand Errors
- Use Advanced functions in Date, Time, Logical, Text and Lookups
- Use Data Validation to control the data being accepted such as controlled text length and drop list.
- Create Scenarios, use of Goal Seek & Solver
- Group and Outline Data & Using Subtotals
- Consolidate Data by Position or Category, and Use Formulas
- Use and format of PivotTable, PivotChart and Slicers
- Record, play and delete a Macro. Adding a Macro to the Quick Access Toolbar
Course Outline
Advanced Functions and Formulas
- Formulas with Multiple Operators
- Inserting and Editing a Function
- AutoCalculate and Manual Calculation
- Defining Names
- Using and Managing Defined Names
- Displaying and Tracing Formulas
- Understanding Formula Errors
- Using Financial Functions (PMT)
- Using Logical Functions (IF, AND, OR, SUMIF, COUNTIF & etc)
- Using Lookup & Reference Functions (VLOOKUP, HLOOKUP, MATCH & etc)
- Using Text Functions (PROPER, CONCATENATE, UPPER, LEFT & etc)
- Using Date & Time Functions
Organizing and Analyzing Data
- Creating Scenarios
- Creating a Scenario Report
- Working with Data Tables
- Using Goal Seek
- Using Solver
- Using Text to Columns
- Grouping and Outlining Data
- Using Subtotals
- Consolidating Data by Position or Category
- Consolidating Data Using Formulas
- Sharing a Workbook
- Track changes
- Compare and Merge Workbook
Working with the Web and External Data
- Inserting a Hyperlink
- Importing Data from an Access Database or Text File
- Importing Data from the Web and Other Sources
- Working with Existing Data Connections
Working with Data Ranges
- Creating a Custom AutoFill List
- Applying Conditional Formatting
- Creating & Managing Conditional Formatting Rules
- Sorting by a Custom List
- Filtering Data
- Creating a Custom AutoFilter
- Using an Advanced Filter
- Using Data Validation
Working with PivotTables
- Creating a PivotTable
- Specifying PivotTable Data
- Changing a PivotTable’s Calculation
- Filtering and Sorting a PivotTable
- Working with PivotTable Layout
- Grouping PivotTable Items
- Updating a PivotTable
- Formatting a PivotTable
- Creating a PivotChart
- Creating a Standalone PivotChart
- Use Slicers
- Sharing Slicers Between PivotTables
- Creating Relationships between Tables
Working with Macros
- Recording a Macro
- Playing and Deleting a Macro
- Adding a Macro to the Quick Access Toolbar
Related Courses
The ‘Microsoft Excel – Basic’ 2-day course equips you with fundamental skills for creating, editing, and utilizing basic formulas to extract data insights, along with instruction on page layout and spreadsheet formatting for printing.
Course Duration:
2 Days (9.00am – 5.00pm)
View course details here
Microsoft Excel – Intermediate
The ‘Microsoft Excel – Intermediate’ 2-day course enhances your Excel proficiency by covering data management, protection, table and chart creation, formula usage, error tracing, name management, sorting, filtering, as well as introducing VLOOKUP, IF functions, and basic Pivot Tables for impactful data reporting.
Course Duration:
2 Days (9.00am – 5.00pm)
View course details here
Microsoft Excel – Advanced
Course Duration:
2 Days (9.00am – 5.00pm)
Course Fee Details:
$436 (incl. of 9% GST)
UTAP Claimable
Course Code: ASKMEA
Nett Course Fee
$436
Course Schedule
| Course | Duration | Months | Available Date(s) | Venue | Remarks | ||||
|---|---|---|---|---|---|---|---|---|---|
| Microsoft Excel – Advanced ASKMEA | 2 Days (9:00am – 5:00pm) | August 2025 | 21-22 | JTC Summit | Weekdays | ||||
| September 2025 | 25-26 | Mountbatten Square | Weekdays | ||||||
| October 2025 | 30-31 | International Plaza | Weekdays | ||||||
| November 2025 | 24-25 | JTC Summit | Weekdays | ||||||
| December 2025 | 18-19 | Mountbatten Square | Weekdays |