Wed June 27, 2018 9am-4pm
189.00

Advanced Excel Level 2

Quantity:
Add To Cart

Thurs July 12, 2018 9am-4pm
189.00

Advanced Excel Level 2

Quantity:
Add To Cart

Sat July 21, 2018 9am-4pm
189.00

Advanced Excel Level 2

Quantity:
Add To Cart

Thurs July 26, 2018 9am-4pm
189.00

Advanced Excel Level 2

Quantity:
Add To Cart
 

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