MS Excel Level 3 Advanced
Learning Outcomes: When you have completed this course, you will be able to create more protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and Pivot Tables, macros and much more.
Course Pre-Requisites: This level assumes the learner is already familiar with Excel features and operations and is now ready to explore more of the advanced analysis and automation tools in Excel. We recommend that users complete MS Excel level 2 prior to level 3.
Duration: 3 hours
Lookup Functions
The CHOOSE Function
The LOOKUP FUNCTION
Using Counting Functions
The ROUND Function
Rounding Up and Down
Manipulative Functions
The MOD & TODAY Function
NOW,DATE & PMT Function
Goal Seeking
Goal Seeking Components
Using Goal Seek
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 Protecting a Workbook
Removing a Password from a Workbook
Importing and Exporting Data
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
Modify an Inserted Picture
Data Linking Understanding Data Linking 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 Consolidations Consolidating From Different Layouts Consolidating Data Using SUM Function
Data Tables Understanding Data Tables And What If Models Using A Simple What If Model Creating A One-Variable Data Table Using A One-Variable Data Table 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 Pivot Table Clearing A Report Filter Switching Pivot Table Fields Formatting A Pivot Table Understanding Slicers Creating Slicers Inserting A Timeline Filter Challenge Exercise
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 Pivot Table Reports Creating Running Totals Creating Calculated Fields Providing Custom Names Creating Calculated Items Pivot Table options Sorting in A Pivot Table
Pivot Charts Inserting a PivotChart Defining the PivotChart Structure Changing the PivotChart Type Using the PivotChart Filter Field Buttons Moving Pivot Charts to Chart Sheets
Advanced Filters Understanding Advanced Filtering Using Advanced Filtering Extracting records with Advanced Filtering Using Formulas In Criteria Understanding Data Base Functions Using Data Base 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 Control
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 One Drive
Saving to OneDrive
Sharing Workbooks
Opening Shared Workbooks
Enabling Tracked 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 Cell References
Viewing a Macro
Editing a Macro
Assigning a Macro to the Tool Bar
Running a Macro from the Tool Bar
Assigning a Macro to the Ribbon
Assigning Key Board Short Cut to A Macro
Deleting a Macro
Copying a Macro
