Microsoft Excel 2016 – Level 3
-
Course Code:
INF1617 -
Course Duration:
1 Day -
Course Price:
$395 -
Availability:
Call/Email to register interest
-
Overview & ObjectivesOpen or Close
The skills and knowledge acquired in Microsoft Excel 2016 – Level 3 are sufficient to be able to protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and Pivot Tables, macros, and much more.
-
Skills GainedOpen or Close
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
-
Course ModulesOpen or Close
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
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
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
Data Linking
- Understanding Data Linking
- Linking Between Worksheets
- Linking Between Workbooks
- Updating Links Between Workbooks
Grouping and outlining
- Understanding Grouping and
- Outlining
- Creating an Automatic Outline
- Working With an Outline
- Creating a Manual Group
- Grouping by Columns
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
Data consolidation
- Understanding Data Consolidation
- Consolidating With Identical Layouts
- Creating a Linked Consolidation
- Consolidating From Different Layouts
- Consolidating Data Using the SUM Function
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
Scenarios
- Understanding Scenarios
- Creating a Default Scenario
- Creating Scenarios
- Using Names in Scenarios
- Displaying Scenarios
- Creating a Scenario Summary Report
- Merging Scenarios
Pivot Tables
- 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
- Challenge Exercise
- Challenge Exercise Sample
Pivot Table 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
Pivot Charts
- Inserting a PivotChart
- Defining the PivotChart Structure
- Changing the PivotChart Type
- Using the PivotChart Filter Field Buttons
- Moving PivotCharts to Chart Sheets
Advanced Filters
- 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
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
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
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
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
-
PrerequisitesOpen or Close
Microsoft Excel 2016 – Level 3 assumes some knowledge of the software as well as a general understanding of personal computers and the Windows operating system environment.