This course builds on topics covered in Level I and Level II. Users will learn to validate data, work with Validation Lists to protect data (including protecting files and cells with passwords and creating read-only files). You will begin to explore working with multiple worksheets to create a workbook which allows you to enter data across a group of worksheets and move and copy data from one sheet to another. Advanced topics include linking and consolidating data and updating workbooks, creating workbook templates, using Numeric and Logical Functions, and creating basic Macros.
This one-day advanced Excel course is Level III of our Microsoft Excel series and runs from 8:30 a.m. to 4:30 p.m. with an instructor. You learn to validate data with value limits, error alerts and validation lists, and to protect your work with file passwords, read-only files, locked cells, hidden formulas and a protected workbook structure. The class then moves on to grouping worksheets, building formulas across worksheets and workbooks, and linking and consolidating data from multiple sources.
You also use auditing tools such as Trace Precedents, Evaluate Formula and the Watch Window, apply conditional formatting with data bars, color scales, icon sets and formula-based rules, and outline data with subtotals. The course covers workbook templates, named ranges, internal hyperlinks and styles, numeric functions including ROUND, INT, TRUNC, ABS and PMT, and logical functions including IF, AND, OR and nested IF formulas. It finishes with recording basic macros and adding a macro button to the Quick Access Toolbar.
This advanced Excel training suits people who have completed Level I and Level II or already use Excel confidently every day. The course fee is $345, and the class provides 8 CPE hours and 7.5 PDUs. Organizations can also book these advanced Excel classes as private training for a team, and the form on this page lets you ask about upcoming dates or a group session.
Course Outline:
Validating Data
Creating Data value limits
Creating Error Alerts
Stop, Information, and Warning Messages
Working with Validation Lists
Levels of Protection
File Access Password Setting
Write Reservation Password
Read-Only Files
Protecting Worksheets
Locking and Unlocking Cells
Hiding Cell Formulas
Password Protecting Specific Ranges
Protecting Workbook Structure
Working with Formulas Across Worksheets and Workbooks
Grouping Worksheets
Consecutive Worksheets
Non-Consecutive Worksheets
All Worksheets
Entering and Formatting Data Across Grouped Worksheets
Multi-Worksheet Formulas without Uniform Structure
Multi-Worksheet Formulas with Uniform Structure
Moving or Copying Worksheets into Another Workbook
Linking and Consolidating Data
Creating Formula Links
Multi-Generational Links
Working with External Links
Options for Updating Workbook Links
Consolidating Data
Combining Multiple Data Ranges
Using Aggregate Functions
Consolidation Links
Using Worksheets Auditing Tools
Tracing Precedents and Dependents
Setting Error Checking Options
Using the Evaluate Formula Tool
Using the Watch Window
Conditional Formatting
Using Conditional Formatting Presets
Data Bars
Color Scales
Icon Sets
Editing Conditional Formatting Rules
Creating Multiple Rules for A Data Range
Using a Formula for Conditional Formatting
Using Data Outlining
Creating an Outline Using Subtotaling of Data Lists
Single-Level Subtotals
Multi-Level Subtotals
Grouping Columns and Rows to Expand/Collapse Data
Creating A Workbook Template
Reusing Information
Creating Named Ranges
Creating Internal Hyperlinks
Using Cell Comments
Using Styles
Using Numeric Functions
Using the ROUND Function
Using the INT Function
Using the TRUNC Function
Using the ABS Function
Using the PMT Financial Function
Using Logical Functions
Using the IF Function
Using the OR and AND Functions
Creating a Nested IF Formula
Creating Basic Macros
Using the Macro Recorder
Executing a Macro by Keystroke
Creating a Macro Button on the Quick Access Toolbar
Cancellation Policy: 5 working days for full refund. Cancellations after that time are charged full tuition for the course.
For more information regarding refund, concerns, and/or program cancellation policies please contact our offices at 919-878-7100 ext. 22
