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

  • Automating Frequent Tasks

    • 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

 
New York Computer Training Solutions LLC
(646)345-5713
info@nyctsolutions.com