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
Booking / availability