pREREQUISITES:

• Must have AT LEAST 75% knowledge of the Excel Level 1 Outline OR Equivalent knowledge.

Excel Level 2 Outline

Lesson 1-Working with Templates

• Open an Existing Template

• Create a custom Template

Lesson 2-Defining Data

• Named Ranges

• Data Validation

• Remove Duplicates

Lesson 3-Functions

• VLOOKUP Function (Approximate and Exact)

• HLOOKUP Function (Approximate and Exact)

• LOOKUP Function

• MATCH Function

• Combine VLOOKUP/HLOOKUP with MATCH

• IF

• AND vs OR

• Nest IF with AND/OR Function

• Fuse IF with VLOOKUP

• Troubleshoot formula

• IFNA

• IFERROR

• Trace Precedents

• Trace Dependents

• Watch Window

Lesson 4-What-if Analysis

• Scenarios

• Goal Seek

• Data Table (1 or 2 variable table)

Lesson 5-Text Functions

• Data Manipulation

• Text to Column

• Change Case Functions

• UPPER

• LOWER

• PROPER

• Gel text together

• CONCATENATE or use Ampersand (&) Operator

• Confiscate unwanted character

• TRIM

• CLEAN

Lesson 6-Pivot Tables and Charts

• Interactive Pivot Table

• Calculated Field vs. Calculated Item

• Grouping

• Summarize Values By

• Show Values As

• Number Format

• Design Pivot Report

• Report Layout

• Interactive Pivot Chart

• Slicers

• Timeline

Lesson 7-Data Consolidation

• Consolidate across multiple worksheets/workbooks

• Consolidate data by position

• Consolidate data by category

• Create links to the source

Lesson 8-Intoduction to Macros

• Developer Tab

• Macro Security

• Macro Recorder

• Peek the codes in VBA

• Run, Edit and Delete a Macro

• Use a button to run a Macro

Lesson 9-Showing Formulas and Text

• Show me the equations

• Show formulas

• FORMULATEXT function