mentor training
Excel Level 2

This class is designed for people who are familiar with Excel who need to go beyond the basics.  Participants will learn to create and use complex formulas, define and name tables and ranges, and to link multiple worksheets and workbooks to each other.  The class covers grouping datasets, subtotaling and showing and hiding detail data in grouped datasets. 6 hours.

 
Course prerequisites: Excel Level 1 or equivalent experience. 

Customizing Excel
Understanding Excel Options
Navigating through the options categories Customizing the Quick Access Toolbar
Showing Mini Toolbars and Screen TipsEnabling Live Preview

Working with Dates
Interpreting two-digit years
Date functions
Date formatting

Working with Cell Ranges
Using the Name box to create a range name Naming cells by using existing row and column labels

Working with Tables
Understanding good table layout concept
Creating a Table
Using auto filters and advanced filters
Sorting a Table
Removing duplicates
Resizing a Table
Using the Name Manager
Formatting tables with Themes and Styles

Outlining and Grouping
Creating outline levels manually
Creating outline levels automatically
Showing and hiding data
Working with subtotals

Worksheet Functions
Understanding Formula AutoComplete
Use SUM to Add Nonadjacent Ranges
Using the Insert Function Command
Nesting Functions
Simple vs. Complex Functions
Using the PMT() Function
Using the IF() Logical Function
Using the AND() Operator within an IF()Statement
Nested IF() Statements
Using the VLOOKUP() Function

Linking
Linking Cells on the Same Worksheet
Linking Values between Worksheets
Linking Cells in Different Workbooks
Changing the Link Source
Changing from a Link to a Value
Breaking Links

Using Database Functions with Tables
Using the DSUM function
Using the DCOUNT function
Using the SUMIF and SUMIFS functions     
© 2017 Mentor Training, Inc.