Duration: 2 days / 17 hours
Time: 9am to 6pm
Course Code: CRS-Q-0041193-ES

Funding available for this course:

Enhanced Training Support for SMEs  Post-Secondary Education Scheme (PSEA) Skills Development Fund (SDF) SkillsFuture Credit SkillsFuture Mid-Career Enhanced Subsidy

What Will Be Taught For This Advanced Microsoft Excel Course?

This course provides the basis for the practice-based test for ICDL Perform Advanced Spreadsheet Functions exam.

The exam requires the candidate to use the spreadsheet application to produce what are deemed to be advanced spreadsheet outputs. The candidate shall be able to operate effectively at more than a basic level of competence and be able to edit and enhance numeric, text and graphical data and to sort, query and link data. The candidate shall be able to carry out advanced formatting and presentation operations on charts and graphs. The candidate shall be able to use functions such as those associated with logical, statistical or mathematical operations. The candidate shall be able to use available analysis and audit tools and to run and record simple macros.

Module 1. Using Conditional and Custom Formats

  • 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
  • Labs

Module 2. Using Templates

  • Copying Worksheets
  • Hiding Columns and Rows
  • Unhiding Columns and Rows
  • Insert Comments
  • Saving a Workbook as a Template
  • Using a Template
  • Editing a Template
  • Inserting a New Worksheet
  • Hiding/Unhiding a Worksheet
  • Deleting a Template
  • Finding Online Templates
  • Labs

Module 3. Working with Databases

  • Creating Subtotals in a List
  • Removing Subtotals from a List
  • Sorting Data by Multiple Levels
  • Using Custom Sort
  • Using Data Validation
  • Validating Data using a List
  • Creating a Custom Error Message
  • Removing Data Validation
  • Labs

Module 4. Working with Advanced Filters

  • 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
  • Labs

Module 5. Revising Charting Features

  • 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
  • Labs

Module 6. Creating/Revising 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
  • Deleting PivotTable Report Fields
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report
  • Grouping Data Manually
  • Labs - Creating/Revising PivotTables

Module 7. Using Auditing Tools and Range Names

  • Jumping to a Named Range
  • 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
  • Displaying/Removing Dependent Arrows
  • Displaying/Removing Precedent Arrows
  • Showing Formulas
  • Labs

Module 8. Exporting and Importing Data

  • Importing Data from Text Files
  • Changing External Data Range Properties
  • Removing the Query Definition
  • Creating a Hyperlink
  • Editing a Hyperlink
  • Labs

Module 9. Using Advanced Functions

  • Using the VLOOKUP Function
  • Using the HLOOKUP Function
  • Using the IF Function
  • Using Nested IF Functions
  • Using the ISERROR Function
  • Using an AND Condition with IF
  • Using an OR Condition with IF
  • Using the ROUND Function
  • Using COUNTIF Function
  • Using RANK Function
  • Using Financial Functions
  • Using Text Functions
  • Labs

Module 10. Using Scenarios

  • Creating a Scenario
  • Displaying a Scenario
  • Editing a Scenario
  • Creating a Scenario Summary Report
  • Working with Data Tables
  • Placing Formulas in Data Tables
  • Creating a One-Variable Table
  • Creating a Two-Variable Table
  • Labs

Module 11. Using Worksheet Protection

  • Unlocking Cells in a Worksheet
  • Protecting a Worksheet
  • Unprotecting a Worksheet
  • Creating Allow-Editing Ranges
  • Deleting Allow-Editing Ranges
  • Protecting Workbook Windows
  • Unprotecting Workbook Windows
  • Assigning a Password
  • Opening a Password-protected File
  • Removing a Password
  • Labs - Using Worksheet Protection

Module 12. Tracking and Merging Workbooks

  • Saving a Shared Workbook
  • Viewing Users Sharing a Workbook
  • Viewing Shared Workbook Changes
  • Changing the Update Frequency
  • Highlighting Changes
  • Managing Conflicting Changes
  • Resolving Conflicting Changes
  • Setting Change History Options
  • Adding a History Worksheet
  • Reviewing Tracked Changes
  • Merging Shared Workbook Files
  • Labs

Module 13. Recording 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
  • Labs

Who Should Attend This Advanced Microsoft Excel Course Training?

Highly recommended for any individuals with at least basic or intermediate grasp of Microsoft Office Excel concepts and interested in learning more advanced functions for more challenging work.

Pre-requisite

An understanding of MS Excel 2013/Excel 2016/2019 basic and intermediate level is required.

  w/o GST w GST
Course Fee $470 $502.90
Singapore Citizen & PR aged ≥ 21 years $181 $213.90
Singapore Citizen aged ≥ 40 years
(SkillsFuture Mid-Career Enhancement Funding)
$47 $79.90

 

  w/o GST w GST
Course Fee $470 $502.90
Singapore Citizen & PR aged ≥ 21 years $181 $213.90
Singapore Citizen aged ≥ 40 years
(SkillsFuture Mid-Career Enhancement Funding)
$47 $79.90

 

  w/o GST w GST
Course Fee $470 $502.90
Singapore Citizen & PR aged ≥ 21 years $47 $79.90
Singapore Citizen aged ≥ 40 years
(SkillsFuture Mid-Career Enhancement Funding)
$47 $79.90

 


Trainee shall be bound by the Terms and Conditions of any applicable funding scheme.

Please ensure that you have read our Terms and Conditions before submitting the enrolment form.






Please click on the course date to enrol.


  • CL: Classroom Learning
  • VILT: Virtual Instructor-Led Training
  • GTR: Guaranteed To Run
  • Sat: Saturday
  • Wkn: Weekend
Note: Courses are conducted via classroom unless stated otherwise beside the course dates.