Microsoft Excel 2019 Advanced (1 Day) (7.5 hrs)

Course Code: CRS-N-0048597

This course equips the participants with the skills to use the spreadsheet application to produce advanced spreadsheet outputs. The participants will gain the competence to sort, query and link data; use functions that are associated with logical, statistical or mathematical operations; and use available analysis and audit tools. They will also be able to run and record simple macros.

  • Produce advanced spreadsheet outputs
  • Demonstrate the ability to sort, query and link data
  • Perform logical, statistical or mathematical operations

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

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)

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

PivotTable enhancement

  • Introduction to data model
  • Setting relationship for table
  • Create PivotTable from data model
  • Insert slicer and timeline for PivotTable

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

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

Participants will benefit from the hands-on practical sessions during the course.

This course is suitable for anyone with at least intermediate knowledge of Microsoft Excel and seeking to produce more advanced spreadsheet outputs.

Prerequisite

Participants must have knowledge of MS Excel 2019 at the intermediate level.

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $200.00
Nett Fee Incl. 7% GST : $214.00
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Key Benefits
  • Produce advanced spreadsheet outputs
  • Demonstrate the ability to sort, query and link data
  • Perform logical, statistical or mathematical operations
Course Contents

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

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)

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

PivotTable enhancement

  • Introduction to data model
  • Setting relationship for table
  • Create PivotTable from data model
  • Insert slicer and timeline for PivotTable

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

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
Learning Methodology

Participants will benefit from the hands-on practical sessions during the course.

Who Should Attend

This course is suitable for anyone with at least intermediate knowledge of Microsoft Excel and seeking to produce more advanced spreadsheet outputs.

Prerequisite

Participants must have knowledge of MS Excel 2019 at the intermediate level.

Course Details

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $200.00
Nett Fee Incl. 7% GST : $214.00
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Course Application

Details Registration
January 29, 2020 - January 29, 2020 (9:00 am - 5:30 pm) Register Now
February 25, 2020 - February 25, 2020 (9:00 am - 5:30 pm) Register Now
March 25, 2020 - March 25, 2020 (9:00 am - 5:30 pm) Register Now
April 30, 2020 - April 30, 2020 (9:00 am - 5:30 pm) Register Now
May 29, 2020 - May 29, 2020 (9:00 am - 5:30 pm) Register Now
June 19, 2020 - June 19, 2020 (9:00 am - 5:30 pm) Register Now
July 14, 2020 - July 14, 2020 (9:00 am - 5:30 pm) Register Now
August 5, 2020 - August 5, 2020 (9:00 am - 5:30 pm) Register Now
September 11, 2020 - September 11, 2020 (9:00 am - 5:30 pm) Register Now
October 16, 2020 - October 16, 2020 (9:00 am - 5:30 pm) Register Now
November 17, 2020 - November 17, 2020 (9:00 am - 5:30 pm) Register Now
December 4, 2020 - December 4, 2020 (9:00 am - 5:30 pm) Register Now