Microsoft® Excel 2016 Level III Advanced Topics

Course Outline:

Validating, Protecting, and Auditing Forms and Templates
Using Data Validation And Tracing Invalid Data
How To Validate Data Input
How To Compose A Data Input Message
How To Create An Error Alert Message
How To Circle Invalid Data

Creating A New Workbook Based On A Template
How To Create A New Workbook Base On A Template
How To Create A New Template
How To Create A New Workbook Base On Your Own Template
How To Edit The Template

Protecting Worksheet Styles, Contents And Elements
How To Protect The Worksheet
How To Unprotect The Worksheet
How To Unlock Cells For Editing In A Protected Sheet
How To Hide A Cell Formula
How To Set A Password To Edit A Specific Range
How To Grant Selected Users Permission To Edit A Range Without A Password

Protecting Workbook Contents
How To Protect And Unprotect The Workbook Structure

Protecting Your Workbook From Unauthorized Users Access
How To Set A Password To Open A Worksheet
How To Remove A Password From A Workbook
How To Set A Password To Modify A Worksheet

Auditing Worksheets And Tracing Cell References
How To Find Cells That Provide Data To An Active Cell (Precedents)
How To Find Cells That Are Referred To By An Active Cell (Dependents)
How To Correct Calculation Errors
How To Set Error Checking Options
How To Scan And Correct All Errors
How To Use The Watch Window

Working with Advanced Formatting, Styles and Outlining
Customizing The Appearance Of Data With Advanced Formatting
How To Use The Built-In Number Formats
How To Customize The Number Format
How To Customize The Date Format

Using Conditional Formatting
How To Use The Conditional Formatting To Highlight Cells
How To Use The Data Bars
How To Use The Color Scales
How To Use The Icon Sets
How To Edit The Rules
How To Change The Precedence Of The Rules
How To Stop A Rule
How To Clear The Rules

Maintaining Format Consistency With Styles And Themes
How To Apply A Built-in Cell Style
How To Create A Custom Cell Style
How To Apply A Custom Cell Style
How To Modify / Delete A Cell Style
How To Merge A Style From Another Workbook
How To Apply A Theme
How To Create New Theme Colors
How To Create New Theme Fonts
How To Select The Theme Effects
How To Save The Custom Theme

Using Data Outlining
How To Create An Outline Automatically
How To Remove An Outline
How To Create An Outline Manually
How To Use A Subtotal To Create An Outline
How To Show / Hide Details Using Outline Symbols

Working With Multiple Worksheets and Workbooks
Managing Data in Multiple Worksheets
How To Select Adjacent Worksheets
How To Select Non-Adjacent Worksheets
How To Enter Data Into Multiple Worksheets

Using A 3-D Formula Across Worksheets
How To Enter A 3-D Formula Across Worksheets
How To Enter A 3-D Formula Across Worksheets Using A Function

Using Range Names
How To Define Names Quickly
How To Define A Range Name Manually
How To Define A 3-D Range Using The Menu
How To Manage Names Using The Name Manager
How To Redefine The Cell References For A Range Name
How To Insert A Name In A Formula

Combining Worksheets And Data From Different Workbooks
How To Consolidate Data
How To Move Or Copy Worksheets to Another Workbook
Linking Information From Different Worksheets And Workbooks
How To Link Information Using Copy And Paste Link
How To Update A Link

Using Advanced Functions
Using Financial Functions
How To Use The PMT Function
How To Use The ABS Function

Using Logical Functions
How To Use A Logical Test
How To Use The IF Function To Return A Text
How To Use The IF Function To Return A Value
How To Use A Nested IF Function
How To Use The OR Function
How To Use The AND Function
How To Use the COUNTIF Function

Using Comments
How To Add A Comment Into A Cell
How To Show/Hide Comments Permanently
How To Show/Hide Comment Indicators

Understanding Macros
How To Record A New Macro
How To Run A Macro
How To Create A Run Macro Button On The Quick Access Toolbar