Microsoft Excel 2019 Advanced (2 Days) (15 hrs)

Course Code: CRS-N-0050273

This course introduces the participants to the more advanced features of Microsoft Excel 2019. The participants will learn how to manage, filter and generate reports from Excel data; summarise and analyse data efficiently using tools such as Pivot Tables and Scenarios; create macros to automate some repetitive tasks; and protect worksheets and workbooks.

  • Understand advanced features of Microsoft Excel 2019
  • Demonstrate the ability to manage and analyse data efficiently
  • Automate repetitive tasks using macros

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

Using templates

  • Copying worksheets
  • Hiding columns and rows
  • Unhiding columns and rows
  • 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

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

Working with advanced filters

  • Creating a criteria range
  • Using a criteria range
  • Showing all records
  • Using an advanced and / or condition
  • Copying filtered records
  • Using database functions
  • Finding unique records
  • Removing duplicates from a table

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

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

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

Exporting and importing data

  • Importing data from text files
  • Changing external data range properties
  • Removing the query definition
  • Creating a hyperlink
  • Editing a hyperlink

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

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

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

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

Comments

  • Inserting comments
  • Viewing comments
  • Reviewing comments
  • Printing comments

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 : 2 days (15 hrs)
Time : 9:00am – 5:30pm

Course Fee : $450.00
Nett Fee Incl. 7% GST : $481.50
SDF Support* : $30.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
  • Understand advanced features of Microsoft Excel 2019
  • Demonstrate the ability to manage and analyse data efficiently
  • Automate repetitive tasks using macros
Course Contents

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

Using templates

  • Copying worksheets
  • Hiding columns and rows
  • Unhiding columns and rows
  • 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

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

Working with advanced filters

  • Creating a criteria range
  • Using a criteria range
  • Showing all records
  • Using an advanced and / or condition
  • Copying filtered records
  • Using database functions
  • Finding unique records
  • Removing duplicates from a table

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

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

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

Exporting and importing data

  • Importing data from text files
  • Changing external data range properties
  • Removing the query definition
  • Creating a hyperlink
  • Editing a hyperlink

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

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

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

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

Comments

  • Inserting comments
  • Viewing comments
  • Reviewing comments
  • Printing comments
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 : 2 days (15 hrs)
Time : 9:00am – 5:30pm

Course Fee : $450.00
Nett Fee Incl. 7% GST : $481.50
SDF Support* : $30.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 30, 2020 - January 31, 2020 (9:00 am - 5:30 pm) Closed
February 26, 2020 - February 27, 2020 (9:00 am - 5:30 pm) Closed
March 30, 2020 - March 31, 2020 (9:00 am - 5:30 pm) Closed
August 6, 2020 - August 7, 2020 (9:00 am - 5:30 pm) Closed
September 24, 2020 - September 25, 2020 (9:00 am - 5:30 pm) Closed
October 22, 2020 - October 23, 2020 (9:00 am - 5:30 pm) Register Now
November 19, 2020 - November 20, 2020 (9:00 am - 5:30 pm) Register Now
December 10, 2020 - December 11, 2020 (9:00 am - 5:30 pm) Register Now