Microsoft Excel 2016 Level 3 (with Challenge Exercises)

LoanNew

Microsoft Excel 2016 - Level 3 enables users to protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and PivotTables, macros, and much more.

Microsoft Excel 2016 - Level 3 assumes the learner has some knowledge of the software and it would help if level 1 & 2 have been completed first.

• Completion of Microsoft Excel 2016 - Level 1 & 2.
• It would also be beneficial to have a general understanding of personal computers and the operating system environment, especially in regard to working with files and folders.

Setting Excel Options

• Understanding Excel Options
• Personalising Excel
• Setting The Default Font
• Setting Formula Options
• Understanding Save Options
• Setting Save Options
• Setting The Default File Location
• Setting Advanced Options
• Practice Exercise
• Practice Exercise Sample

Protecting Data

• Understanding Data Protection
• Providing Total Access To Cells
• Protecting A Worksheet
• Working With A Protected Worksheet
• Disabling Worksheet Protection
• Providing Restricted Access To Cells
• Password Protecting A Workbook
• Opening A Password Protected Workbook
• Removing A Password From A Workbook
• Practice Exercise
• Practice Exercise Sample

Importing And Exporting

• Understanding Data Importing
• Importing From An Earlier Version
• Understanding Text File Formats
• Importing Tab Delimited Text
• Importing Comma Delimited Text
• Importing Space Delimited Text
• Importing Access Data
• Working With Connected Data
• Unlinking Connections
• Exporting To Microsoft Word
• Exporting Data As Text
• Inserting A Picture
• Modifying An Inserted Picture
• Practice Exercise
• Practice Exercise Sample

Data Linking

• Understanding Data Linking
• Linking Between Worksheets
• Linking Between Workbooks
• Updating Links Between Workbooks
• Practice Exercise
• Practice Exercise Sample

Grouping And Outlining

• Understanding Grouping And Outlining
• Creating An Automatic Outline
• Working With An Outline
• Creating A Manual Group
• Grouping By Columns
• Practice Exercise
• Practice Exercise Sample

Summarising And Subtotalling

• Creating Subtotals
• Using A Subtotalled Worksheet
• Creating Nested Subtotals
• Copying Subtotals
• Using Subtotals With AutoFilter
• Creating Relative Names For Subtotals
• Using Relative Names For Subtotals
• Practice Exercise
• Practice Exercise Sample

Data Consolidation

• Understanding Data Consolidation
• Consolidating With Identical Layouts
• Creating A Linked Consolidation
• Consolidating From Different Layouts
• Consolidating Data Using The SUM Function
• Practice Exercise
• Practice Exercise Sample

Data Tables

• Understanding Data Tables And What-If Models
• Using A Simple What-If Model
• Creating A One-Variable Table
• Using One-Variable Data Tables
• Creating A Two-Variable Data Table
• Practice Exercise
• Practice Exercise Sample

Scenarios

• Understanding Scenarios
• Creating A Default Scenario
• Creating Scenarios
• Using Names In Scenarios
• Displaying Scenarios
• Creating A Scenario Summary Report
• Merging Scenarios
• Practice Exercise
• Practice Exercise Sample

PivotTables

• Understanding PivotTables
• Recommended PivotTables
• Creating Your Own PivotTable
• Defining The PivotTable Structure
• Filtering A PivotTable
• Clearing A Report Filter
• Switching PivotTable Fields
• Formatting A PivotTable
• Understanding Slicers
• Creating Slicers
• Inserting A Timeline Filter
• Practice Exercise
• Practice Exercise Sample

PivotTable Features

• Using Compound Fields
• Counting In A PivotTable
• Formatting PivotTable Values
• Working With PivotTable Grand Totals
• Working With PivotTable Subtotals
• Finding The Percentage Of Total
• Finding The Difference From
• Grouping In PivotTable Reports
• Creating Running Totals
• Creating Calculated Fields
• Providing Custom Names
• Creating Calculated Items
• PivotTable Options
• Sorting In A PivotTable
• Practice Exercise
• Practice Exercise Sample

PivotCharts

• Inserting A PivotChart
• Defining The PivotChart Structure
• Changing The PivotChart Type
• Using The PivotChart Filter Field Buttons
• Moving PivotCharts To Chart Sheets
• Practice Exercise
• Practice Exercise Sample

Advanced Filters

• Understanding Advanced Filtering
• Using An Advanced Filter
• Extracting Records With Advanced Filter
• Using Formulas In Criteria
• Understanding Database Functions
• Using Database Functions
• Using DSUM
• Using The DMIN Function
• Using The DMAX Function
• Using The DCOUNT Function
• Practice Exercise
• Practice Exercise Sample

Validating Data

• Understanding Data Validation
• Creating A Number Range Validation
• Testing A Validation
• Creating An Input Message
• Creating An Error Message
• Creating A Drop Down List
• Using Formulas As Validation Criteria
• Circling Invalid Data
• Removing Invalid Circles
• Copying Validation Settings
• Practice Exercise
• Practice Exercise Sample

Controls

• Understanding Types Of Controls
• Understanding How Controls Work
• Preparing A Worksheet For Controls
• Adding A Combo Box Control
• Changing Control Properties
• Using The Cell Link To Display The Selection
• Adding A List Box Control
• Adding A Scroll Bar Control
• Adding A Spin Button Control
• Adding Option Button Controls
• Adding A Group Box Control
• Adding A Check Box Control
• Protecting A Worksheet With Controls
• Practice Exercise
• Practice Exercise Sample

Sharing Workbooks

• Sharing Workbooks Via The Network
• Sharing Workbooks Via OneDrive
• Saving To OneDrive
• Sharing Workbooks
• Opening Shared Workbooks
• Enabling Tracked Changes
• Accepting Or Rejecting Changes
• Disabling Tracked Changes
• Adding Worksheet Comments
• Navigating Worksheet Comments
• Editing Worksheet Comments
• Deleting Comments
• Practice Exercise
• Practice Exercise Sample

Recorded Macros

• Understanding Excel Macros
• Setting Macro Security
• Saving A Document As Macro Enabled
• Recording A Simple Macro
• Running A Recorded Macro
• Relative Cell References
• Running A Macro With Relative References
• Viewing A Macro
• Editing A Macro
• Assigning A Macro To The Toolbar
• Running A Macro From The Toolbar
• Assigning A Macro To The Ribbon
• Assigning A Keyboard Shortcut To A Macro
• Deleting A Macro
• Copying A Macro
• Practice Exercise
• Practice Exercise Sample

At the completion of this course you should be able to:

• Modify Excel options
• Protect data in worksheets and workbooks
• Import data into Excel and export data from Excel
• Use data linking to create more efficient workbooks
• group cells and use outlines to manipulate the worksheet
• create summaries in your spreadsheets using subtotals
• Use the Data Consolidation feature to combine data from several workbooks into one
• create, use and modify data tables
• Create and work with scenarios and the Scenario Manager
• Understand and create simple PivotTables
• Construct and operate PivotTables using some of the more advanced techniques
• Create and edit a PivotChart
• Use advanced filters to analyse data in a list
• Use a variety of data validation techniques
• Create and use a range of controls in a worksheet
• Share workbooks with other users
• Create recorded macros in Excel

Watsonia workbook with 210 topics. Files for the exercises are provided on a CD or can be downloaded here

Approximately 20 hours

• Excel 2016: Analysing and Summarising Data
• Excel 2016: Functions
• Excel 2016: Introduction
• Excel 2016: Introduction (Self-paced)
• Excel 2016: Managing Data & Lists
• Excel 2016: Recorded Macros
• Office 2016: Excel, Word and PowerPoint Top Ten Tips
• Office 365, Office Online and OneDrive: An introduction