MOS Excel 2016 Core (24 hrs)

Course Code: MOS-ECT6-I-01

In-Company Training

This course prepares the participants for the Microsoft Office Specialist exams. Participants will learn about the basic concepts of spreadsheets, demonstrate the ability to use the spreadsheet application on personal computers, accomplish basic operations associated with developing, formatting and using a spreadsheet, and adopt basic formulas and functions to solve standard mathematical operations. They will also gain competence in using some of the Excel features such as filtering, managing data and creating charts.

  • Apply basic formula in Excel
  • Create charts
  • Apply conditional formatting to highlight critical data
  • Import external data to Excel
  • Use page setup for printing

Exploring Microsoft Excel 2016

  • Starting Excel 2016
  • The User Interface
  • The Backstage View
  • Excel options
  • Working with worksheets
  • Using the ribbon
  • Hiding the ribbon
  • Customising the Quick Access Toolbar
  • Customising tabs
  • Customising the ribbon
  • Customising the status bar
  • Exiting Excel

Getting help

  • Using Microsoft Excel Help and Resources
  • Working with Excel Help

Basic workbook skills

  • Using the keyboard to select cells
  • Using KeyTips
  • Using GoTo
  • Entering text
  • Entering numbers
  • Saving the workbook
  • Closing the workbook
  • Creating a workbook
  • Opening a workbook
  • Inserting a worksheet
  • Shortcuts for data entry
  • Editing data
  • Spell check
  • Creating a new folder when saving
  • Saving the workbook with another name

Selecting cells and ranges

  • Selecting a cell
  • Using the name box to select a cell
  • Selecting a range of contiguous cells
  • Selecting a range of non-contiguous cells
  • Selecting the entire worksheet
  • Selecting a row
  • Selecting a range of contiguous rows
  • Selecting a range of non- contiguous rows
  • Selecting an entire column
  • Selecting a range of columns
  • Selecting a range of non- contiguous columns
  • Using AutoFill

Working with columns and rows

  • Adjusting the column width
  • Adjusting the row height
  • Automatically adjusting columns
  • Hiding and un-hiding rows and columns
  • Inserting columns and rows
  • Deleting columns and rows
  • Freezing and unfreezing columns and rows

Managing worksheets

  • Increasing / decreasing the magnification
  • Changing the magnification of a range
  • Splitting the window
  • Removing split windows
  • Creating a hyperlink

Formatting text and numbers

  • Changing the font
  • Changing font size
  • Bold and italic
  • Underlining text
  • Font colour
  • Rotating text
  • Text wrapping
  • Shrinking text
  • Cell alignment
  • Indenting Text
  • About number formats
  • Accounting number style
  • Percent style
  • Comma style
  • Decimal places

Cell formatting

  • Merging cells
  • Vertical alignment
  • Splitting cells
  • Adding borders
  • Drawing borders
  • Adding fill colour to cells
  • Pasting formats
  • Format painter
  • Copying formats using Auto Fill
  • Clearing formats
  • Inserting selected cells
  • Inserting cut or copied cells
  • Deleting cells

Using styles

  • Applying a predefined style
  • Creating a style by example
  • Applying a style
  • Creating a new style
  • Editing an existing style

Cut, copy and paste

  • Copying and pasting data
  • Cutting data
  • Copying formulas
  • Paste options
  • Paste list
  • Filling cells
  • Drag-and-drop editing
  • Undo and redo

Essential formulas

  • Using basic formulas
  • Entering formulas
  • Basic functions
  • Using the AutoSum button
  • Using the AutoSum list
  • Using formula autocomplete
  • Editing functions
  • Using auto calculate
  • Modifying formulas using the range border
  • Using NOW, TODAY function
  • Using IF function
  • Using COUNTIF function
  • Using SUMIF function
  • Using CONCATENATE function
  • Using link formula

Creating charts

  • Inserting a chart
  • Deleting a chart
  • Adding a chart title
  • Changing the chart background
  • 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

Working with comments

  • Creating comments
  • Viewing a comment
  • Reviewing comments
  • Printing comments
  • Responding to discussion comments

Arranging and finding data

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

Working with table

  • Creating a table from existing data
  • Changing the table name
  • Changing the table style
  • Changing table style options
  • Creating a total row
  • Adding table rows and columns
  • Inserting / deleting table rows / columns
  • Creating a calculated column
  • Filtering data
  • Creating custom filter
  • Sorting data by multiple levels
  • Using data validation
  • Validating data using a list
  • Creating a custom error message
  • Removing data validation
  • Creating subtotals in a list
  • Removing subtotals from a list

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

  • Worksheet margins
  • Worksheet orientation
  • Worksheet page size
  • Headers and footers
  • Header and footer fields
  • Scaling your worksheet to fit a page(s)
  • Repeating row and column labels
  • Changing page breaks
  • Setting / removing a print area
  • Changing sheet options

Printing

  • Print preview
  • Printing the current worksheet
  • Printing a selected range
  • Printing a page range
  • Printing multiple copies

Importing data

  • Import delimited data
  • Text to columns function

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

This course is ideal for beginners in Microsoft Office Excel to develop a solid foundation on the spreadsheet concepts and applications.

Prerequisite

Participants must have a basic working knowledge of Windows / MS Excel 2010/2013.

Duration : 3 days (24 hrs)
Time : 9:00am – 6:00pm

Course Fee : $675.00
Nett Fee Incl. 7% GST : $722.25

 for more information.

(A course in partnership with COMAT)

Key Benefits
  • Apply basic formula in Excel
  • Create charts
  • Apply conditional formatting to highlight critical data
  • Import external data to Excel
  • Use page setup for printing
Course Contents

Exploring Microsoft Excel 2016

  • Starting Excel 2016
  • The User Interface
  • The Backstage View
  • Excel options
  • Working with worksheets
  • Using the ribbon
  • Hiding the ribbon
  • Customising the Quick Access Toolbar
  • Customising tabs
  • Customising the ribbon
  • Customising the status bar
  • Exiting Excel

Getting help

  • Using Microsoft Excel Help and Resources
  • Working with Excel Help

Basic workbook skills

  • Using the keyboard to select cells
  • Using KeyTips
  • Using GoTo
  • Entering text
  • Entering numbers
  • Saving the workbook
  • Closing the workbook
  • Creating a workbook
  • Opening a workbook
  • Inserting a worksheet
  • Shortcuts for data entry
  • Editing data
  • Spell check
  • Creating a new folder when saving
  • Saving the workbook with another name

Selecting cells and ranges

  • Selecting a cell
  • Using the name box to select a cell
  • Selecting a range of contiguous cells
  • Selecting a range of non-contiguous cells
  • Selecting the entire worksheet
  • Selecting a row
  • Selecting a range of contiguous rows
  • Selecting a range of non- contiguous rows
  • Selecting an entire column
  • Selecting a range of columns
  • Selecting a range of non- contiguous columns
  • Using AutoFill

Working with columns and rows

  • Adjusting the column width
  • Adjusting the row height
  • Automatically adjusting columns
  • Hiding and un-hiding rows and columns
  • Inserting columns and rows
  • Deleting columns and rows
  • Freezing and unfreezing columns and rows

Managing worksheets

  • Increasing / decreasing the magnification
  • Changing the magnification of a range
  • Splitting the window
  • Removing split windows
  • Creating a hyperlink

Formatting text and numbers

  • Changing the font
  • Changing font size
  • Bold and italic
  • Underlining text
  • Font colour
  • Rotating text
  • Text wrapping
  • Shrinking text
  • Cell alignment
  • Indenting Text
  • About number formats
  • Accounting number style
  • Percent style
  • Comma style
  • Decimal places

Cell formatting

  • Merging cells
  • Vertical alignment
  • Splitting cells
  • Adding borders
  • Drawing borders
  • Adding fill colour to cells
  • Pasting formats
  • Format painter
  • Copying formats using Auto Fill
  • Clearing formats
  • Inserting selected cells
  • Inserting cut or copied cells
  • Deleting cells

Using styles

  • Applying a predefined style
  • Creating a style by example
  • Applying a style
  • Creating a new style
  • Editing an existing style

Cut, copy and paste

  • Copying and pasting data
  • Cutting data
  • Copying formulas
  • Paste options
  • Paste list
  • Filling cells
  • Drag-and-drop editing
  • Undo and redo

Essential formulas

  • Using basic formulas
  • Entering formulas
  • Basic functions
  • Using the AutoSum button
  • Using the AutoSum list
  • Using formula autocomplete
  • Editing functions
  • Using auto calculate
  • Modifying formulas using the range border
  • Using NOW, TODAY function
  • Using IF function
  • Using COUNTIF function
  • Using SUMIF function
  • Using CONCATENATE function
  • Using link formula

Creating charts

  • Inserting a chart
  • Deleting a chart
  • Adding a chart title
  • Changing the chart background
  • 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

Working with comments

  • Creating comments
  • Viewing a comment
  • Reviewing comments
  • Printing comments
  • Responding to discussion comments

Arranging and finding data

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

Working with table

  • Creating a table from existing data
  • Changing the table name
  • Changing the table style
  • Changing table style options
  • Creating a total row
  • Adding table rows and columns
  • Inserting / deleting table rows / columns
  • Creating a calculated column
  • Filtering data
  • Creating custom filter
  • Sorting data by multiple levels
  • Using data validation
  • Validating data using a list
  • Creating a custom error message
  • Removing data validation
  • Creating subtotals in a list
  • Removing subtotals from a list

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

  • Worksheet margins
  • Worksheet orientation
  • Worksheet page size
  • Headers and footers
  • Header and footer fields
  • Scaling your worksheet to fit a page(s)
  • Repeating row and column labels
  • Changing page breaks
  • Setting / removing a print area
  • Changing sheet options

Printing

  • Print preview
  • Printing the current worksheet
  • Printing a selected range
  • Printing a page range
  • Printing multiple copies

Importing data

  • Import delimited data
  • Text to columns function
Learning Methodology

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

Who Should Attend

This course is ideal for beginners in Microsoft Office Excel to develop a solid foundation on the spreadsheet concepts and applications.

Prerequisite

Participants must have a basic working knowledge of Windows / MS Excel 2010/2013.

Course Details

Duration : 3 days (24 hrs)
Time : 9:00am – 6:00pm

Course Fee : $675.00
Nett Fee Incl. 7% GST : $722.25

 for more information.

(A course in partnership with COMAT)