Duration: 1 day / 7.5 hours
Time: 9am to 5.30pm
Course Code: CRS-N-0048597

Funding available for this course :

SkillsFuture Credit PSEA UTAP Broad based Funding

 

 

 

What Will Be Taught For This Microsoft Excel 2019 Advanced Course?

This course provides the participant to use the spreadsheet application to produce what are deemed to be advanced spreadsheet outputs. The participant shall be able to use functions such as those associated with logical, statistical or mathematical operations. New Formulas that are introduced in 2019 is also introduced here. Furthermore, the participant will be able to use available analysis and audit tools and to run and record simple macros.

Module 1. 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 2. Using Advanced Functions
  • Using the VLOOKUP Function
  • Using the HLOOKUP Function
  • Using the IF Function
  • Using the IFS Function
  • Using the ISERROR Function
  • Using an AND Condition with IF
  • Using an OR Condition with IF
  • Using the ROUND Function
  • Using COUNTIF Function
  • Using SUMIF Functions
  • Using SUMIFs Functions
  • Using MAXIFS Functions
  • Using MINIFS Functions
  • Using RANK Function
  • Using Text Functions (UPPER,LOWER, PROPER, TRIM, TEXTJOIN)
  • Labs
Module 3. Creating/Revising PivotTables
  • Use Recommended PivotTable
  • 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
  • Labs - Creating/Revising PivotTables
Module 4. PivotTable Enhancement
  • Introduction to Data model
  • Setting relationship for Table
  • Create PivotTable from Data Model
  • Insert Slicer and Timeline for PivotTable
  • Labs
Module 5. 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 6. 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 Microsoft Excel 2019 Advanced Training?

Ideal for anyone with at least basic or intermediate knowledge of Microsoft Excel and seeking to produce more advanced spreadsheet outputs.

Pre-requisite

A working knowledge of the following Excel skillset is required:

  • Working with Excel Tables
  • Usage of Basic formula (SUM, AVERAGE, COUNT, MAX, MIN)
  • Apply absolute reference in formula
  • Set Filtering options
  • Create Charts
  w/o GST w GST
Course Fee $200 $214

 


CANCELLATION/POSTPONEMENT / CHANGE of VENUE

  1. 2 to 4 weeks’ written notice from start date of training - 50% of course fee for cancellation

  2. Less than 2 weeks’ written notice from start date of training - 100% of course fees for postponement or cancellation

  3. ST Electronics (e-Services) Pte Ltd reserves the right to cancel or postpone any course or change the venue due to unforeseen circumstances.

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.