Microsoft Excel 2016 Intermediate (1 Day) (7.5 hrs)

Course Code: CRS-N-0042666

This course introduces the participants to intermediate features of Microsoft Excel 2016. They will learn how to generate reports from Excel data. The participants will learn how to highlight important data using conditional formats, prevent date entry errors using Data Validation, and demonstrate the ability to manage data efficiently.

  • Learn about the intermediate features of Microsoft Excel 2016
  • Generate reports from Excel data
  • Demonstrate ability to manage data

Data management

  • Sorting
  • Sorting multiple columns
  • Finding data
  • Replacing data
  • Finding and replacing cell format

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

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

Creating charts

  • Inserting a column chart
  • Inserting a line chart
  • Inserting a bar chart
  • Inserting a pie chart
  • Resizing a chart
  • Deleting a chart
  • Adding a chart title
  • Changing the chart background
  • Changing a column, bar, line or pie slice colours
  • Changing the chart type
  • Adding a data table
  • Changing the chart layout
  • Copying and moving charts
  • Creating a 3-D chart
  • Adjusting 3-D view
  • Inserting Sparkline

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

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

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

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

This course is suitable for individuals with the basic understanding of Microsoft Excel and wishes to be equipped with intermediate skill sets in the programme, such as generating reports and data management.

Prerequisite

Participants must have a basic knowledge of Microsoft Excel 2007/2010/2013.

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

Course Fee : $180.00
Nett Fee Incl. 7% GST : $192.60
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
  • Learn about the intermediate features of Microsoft Excel 2016
  • Generate reports from Excel data
  • Demonstrate ability to manage data
Course Contents

Data management

  • Sorting
  • Sorting multiple columns
  • Finding data
  • Replacing data
  • Finding and replacing cell format

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

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

Creating charts

  • Inserting a column chart
  • Inserting a line chart
  • Inserting a bar chart
  • Inserting a pie chart
  • Resizing a chart
  • Deleting a chart
  • Adding a chart title
  • Changing the chart background
  • Changing a column, bar, line or pie slice colours
  • Changing the chart type
  • Adding a data table
  • Changing the chart layout
  • Copying and moving charts
  • Creating a 3-D chart
  • Adjusting 3-D view
  • Inserting Sparkline

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

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

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

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

Who Should Attend

This course is suitable for individuals with the basic understanding of Microsoft Excel and wishes to be equipped with intermediate skill sets in the programme, such as generating reports and data management.

Prerequisite

Participants must have a basic knowledge of Microsoft Excel 2007/2010/2013.

Course Details

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

Course Fee : $180.00
Nett Fee Incl. 7% GST : $192.60
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 23, 2020 - January 23, 2020 (9:00 am - 5:30 pm) Closed
February 18, 2020 - February 18, 2020 (9:00 am - 5:30 pm) Closed
March 10, 2020 - March 10, 2020 (9:00 am - 5:30 pm) Closed
August 13, 2020 - August 13, 2020 (9:00 am - 5:30 pm) Register Now
September 24, 2020 - September 24, 2020 (9:00 am - 5:30 pm) Register Now
October 27, 2020 - October 27, 2020 (9:00 am - 5:30 pm) Register Now
November 18, 2020 - November 18, 2020 (9:00 am - 5:30 pm) Register Now
December 28, 2020 - December 28, 2020 (9:00 am - 5:30 pm) Register Now