Advanced Microsoft Excel
About the Course
Designed for
Aimed at participants who have experience of Microsoft Excel’s basic and intermediate principles, this course provides participants with practical experience of the topics listed in the course outline below. On completion, participants should be equipped with the necessary skills to produce complex documents and integrate them with output from other Windows applications.
Teaching Approach
The teaching approach contains a mix of lectures, practical and one-to-one work and varies according to the people attending. For the most part, participants are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.
Requirements
Those attending should have an understanding of Microsoft Excel to an intermediate level.
Course Material
A large part of each course is spent with a set of prepared exercises and projects so that those attending gain firm foundations through their own practical experience. Professionally presented course notes are provided and these may be retained for reference afterwards.
Duration
This training program is one day in duration.
Course Outline
Pivot Tables
Creating a Pivot Table
Filtering and Sorting Data
Grouping Data
Renaming Groups
Input Tables
One-Input Data Tables
Two-Input Data Tables
Charts
Creating a Combo Chart
Modifying Chart Axis Scales
Inserting Images into Columns
Creating a Waterfall Chart
Creating a Funnel Chart
Creating a Treemap or Sunburst Chart
Create a Histogram
Create a Pareto Chart
Create a Box & Whisker Chart
Hyperlinks
Inserting a Hyperlink
Editing a Hyperlink
Removing a Hyperlink
Linking & Embedding Data
Linking Data Within a Worksheet
Linking Data Between Workbooks
Linking Data to a Word Document
Updating and Breaking Links
Importing Text Files into Excel
Importing a Delimited Text File
Importing Other Data Types
Sorting, Filtering & Totaling Data
Sorting Data by Multiple Columns
Performing a Custom Sort
Using Autofilter
Top 10 Autofilter
Clearing Filters
Advanced Filter Criteria
Inserting Sub-Totals
Expanding and Collapsing Outlines
Excel Scenarios
Scenario Manager
Scenario Summary Reports
Validating Within Excel
Data Validation
Customising a Validation Error Alert
Removing Data Validation
Auditing Techniques in Excel
Tracing Precedent Cells
Tracing Dependent Cells
Showing All Formulas in a Worksheet
Inserting and Viewing Comments
Deleting Comments
Excel Passwords & Security Issues
Adding an Open Password
Password Protecting Cells &
Worksheets
Hiding and Un-Hiding Formulas
Protecting the Workbook Structure
Macros
Displaying the Developer tab
Recording a macro
Running a macro
Deleting a macro
Macro security levels
Creating a shortcut for a macro
Inserting a button to run a macro
Viewing the macro code (VBA)
Basic VBA code editing