Course Overview Print

Advanced Excel skills are vital-but with Copilot, they become exponentially more powerful. This course helps participants harness advanced Excel features alongside with AI assistance to streamline analysis, model scenarios, and improve business decision-making. Through hands-on exercises, they will master data modelling, automation, and prompt engineering within Excel 365.

Learning Outcomes:

  • Analyse large datasets using advanced Excel formulas and tools

  • Build scenario models and use what-if analysis features

  • Automate workflows with macros and Excel Copilot prompts

Key Topics:

  • Data modelling with named ranges and advanced formulas

  • Scenario management and goal seek

  • Macros and automation tools

  • Copilot for data analysis and prompt-based tasks

Course Outline

Lesson 1: Conditional Formatting 

  • Applying Conditional Formats
  • Changing a Conditional Format
  • Adding a Conditional Format
  • Creating a Custom Conditional Format
  • Using Data Bars
  • Deleting a Conditional Format
  • Creating a Custom Number Format

Lesson 2: Advanced Chart

  • Formatting an Axis
  • Changing the Axis Scaling
  • Formatting the Data Series
  • Adding Data from Different Worksheets
  • Using a Secondary Axis
  • Changing Data Series Chart Types
  • Changing Source Data Range

Lesson 3: Naming Cells and Ranges

  • Assigning Names
  • Using Range Names in Formulas
  • Creating Range Names from Headings
  • Applying Range Names
  • Deleting Range Names
  • Using Range Names in 3-D Formulas

Lesson 4: Validation and Auditing

  • Sorting
  • Multiple Levels Sort
  • Using Custom Sort
  • Creating Subtotals in a List
  • Removing Subtotals from a List
  • Using Data Validation
  • Validating Data using a List
  • Creating a Custom Error Message
  • Removing Data Validation
  • Displaying/Removing Dependent Arrows
  • Displaying/Removing Precedent Arrows
  • Showing Formulas
  • Protecting a Worksheet
  • Unprotect a worksheet
  • Assigning a Password to a File
  • Removing a Password from a File

Lesson 5: Advanced Formulas and functions 

  • Using the VLOOKUP Function
  • Using the HLOOKUP Function
  • Using XLOOKUP Function
  • Using the IF Function
  • Using the IFS Functions
  • Using an AND Condition with IF
  • Using an OR Condition with IF
  • Using Date Function
  • Using COUNTIF / SUMIF Function
  • Using COUNTIFS / SUMIFS Function
  • Using Text Functions

Lesson 6: Advanced Filter

  • Creating a Criteria Range
  • Using a Criteria Range
  • Showing All Records
  • Using an Advanced And Condition
  • Using an Advanced Or Condition
  • Copying Filtered Records
  • Using Database Functions
  • Removing Duplicates from a Table

Lesson 7: Importing and Linking

  • Importing Data from Text Files
  • Use Text to Column 

Lesson 8: PivotTables

  • Creating a PivotTable Report
  • Adding PivotTable Report Fields
  • Selecting a Page Field Item
  • Refreshing a PivotTable Report
  • Changing the Summary Function
  • Adding New Fields to a PivotTable Report
  • Moving PivotTable Report Fields
  • Hiding/Unhiding PivotTable Report Items
  • Adding Slicer
  • Deleting PivotTable Report Fields
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report

Lesson 9: Scenario Analysis

  • Creating a Scenario
  • Displaying a Scenario
  • Editing a Scenario
  • Creating a Scenario Summary Report

Lesson 10: Macros

  • Recording a Macro
  • Saving a Macro-Enabled Workbook
  • Running a Macro
  • Assigning a Shortcut Key
  • Using a Shortcut Key
  • Deleting a Macro
  • Adding a Macro to Quick Access Toolbar
  • Deleting a Macro Button from QAT

Lesson 11: Introduction to Copilot

  • Overview of Copilot
  • How Copilot can assist with Office tasks

Lesson 12: Copilot and Excel 365

  • Using Copilot for complex formulas
  • Using Copilot for Data Analysis and visualization 

Target Audience and Pre-requisites

Highly recommended for any individuals with at least basic or intermediate grasp of Microsoft Office Excel concepts and wants to learn more advanced functions that is applicable in their daliy  work.

Pre-requisite

Knowledge and skills of Microsoft Excel basic and intermediate such as :

  • Data Entries in Excel
  • Apply Formula (SUM, AVERAGE, COUNT)
  • Usage of cell reference (A1, $A$1)
  • Create Simple Charts
*Important Note : Fees are subject to Singapore's prevailing Goods and Services Tax (GST).
Microsoft Excel 365 with Copilot (Advanced)
Fees Enquire Now

Have Question?

We’re here to help — reach out anytime.

By submitting this form, you consent to be contacted via email and/or your mobile number regarding your enquiry. You consent to the collection, use, disclosure and processing of your personal data in accordance with our Personal Data Policy.