Microsoft Excel 2016 Advanced (3 Days) (22.5 hrs)

Course Code: MO-EAT6-02

This course introduces the participants to the more advanced features of Microsoft Excel 2016. 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, Scenarios and Goal Seek; create macros to automate some repetitive tasks; and protect worksheets and workbooks.

  • Understand advanced features of Microsoft Excel 2016
  • 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 grasp of Microsoft Office Excel concepts and interested in learning more advanced functions to handle challenging work.

Prerequisite

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

Duration : 3 days (22.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $650.00
Nett Fee Incl. 7% GST : 695.50

(A course in partnership with COMAT)

Key Benefits
  • Understand advanced features of Microsoft Excel 2016
  • 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 grasp of Microsoft Office Excel concepts and interested in learning more advanced functions to handle challenging work.

Prerequisite

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

Course Details

Duration : 3 days (22.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $650.00
Nett Fee Incl. 7% GST : 695.50

(A course in partnership with COMAT)

Course Application

Details Registration
January 8, 2020 - January 10, 2020 (9:00 am - 5:30 pm) Closed
January 15, 2020 - January 17, 2020 (9:00 am - 5:30 pm) Closed
January 29, 2020 - January 31, 2020 (9:00 am - 5:30 pm) Closed
February 12, 2020 - February 14, 2020 (9:00 am - 5:30 pm) Closed
February 19, 2020 - February 21, 2020 (9:00 am - 5:30 pm) Closed
March 9, 2020 - March 11, 2020 (9:00 am - 5:30 pm) Closed
March 18, 2020 - March 20, 2020 (9:00 am - 5:30 pm) Closed
March 30, 2020 - April 1, 2020 (9:00 am - 5:30 pm) Closed
August 12, 2020 - August 14, 2020 (9:00 am - 5:30 pm) Closed
August 24, 2020 - August 26, 2020 (9:00 am - 5:30 pm) Closed
September 9, 2020 - September 11, 2020 (9:00 am - 5:30 pm) Closed
September 16, 2020 - September 18, 2020 (9:00 am - 5:30 pm) Closed
September 30, 2020 - October 2, 2020 (9:00 am - 5:30 pm) Closed
October 12, 2020 - October 14, 2020 (9:00 am - 5:30 pm) Register Now
October 28, 2020 - October 30, 2020 (9:00 am - 5:30 pm) Register Now
November 11, 2020 - November 13, 2020 (9:00 am - 5:30 pm) Register Now
November 18, 2020 - November 20, 2020 (9:00 am - 5:30 pm) Register Now
November 30, 2020 - December 2, 2020 (9:00 am - 5:30 pm) Register Now
December 14, 2020 - December 16, 2020 (9:00 am - 5:30 pm) Register Now
December 28, 2020 - December 30, 2020 (9:00 am - 5:30 pm) Register Now