Microsoft® Excel 2016 Level III Advanced Topics

Click Here For More Information


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.

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

Course Information
  • Prerequisite:Excel Introduction or equivalent knowledge
  • Credit:PDUs: 7.5 CPE: 8
  • Fee:$395
  • Length:1 day
  • Hours:8:30 a.m. - 4:30 p.m.
  • Delivery:Instructor Led Classroom
  • PMI_Activity_ID:ID: #000340

Related Courses