Fri Sep 7, 2018 9am-4pm
189.00

Advanced Excel Level 3

Quantity:
Add To Cart
 

pREREQUISITES:

Excel Level 3 Outline

 

Lesson 1-Proficiency using What-IF Analysis Tools

  • Create many Scenarios
  • Goal Seek Tool vs. Solver Tool

Lesson 2-Clean up Data

  • Using LEFT, RIGHT and MID
  • SEARCH vs. FIND
  • SUBSTITUTE vs. REPLACE
  • LEN
  • TEXT

Lesson 3-More Functions

  • INDEX
  • MATCH
  • Combine INDEX & MATCH
  • OFFSET
    • Update Range Automatically
    • Fetch for data
    • Look up values vertically in a list of unknown size by using an exact match
  • Combine OFFSET & MATCH
  • INDIRECT
    • Creating indirect references from cell values
    • Creating indirect references from cell values and text
    • Using the INDIRECT function with named ranges
    • Practice INDIRECT function to dynamically refer to another worksheet
    • INDIRECT function with Data Validation in Excel

Lesson 4-Advanced Filters and Criteria

  • Advanced Filter
  • Copy to another location within active worksheet to another worksheet
  • Extract Unique Records
  • Create a criteria range
    • Use AND or OR criteria
    • Drop-down list in a criteria range

Lesson 5-And More Functions

  • Financial Functions
    • PMT
    • PPMT
    • IPMT
    • NPER
  • Date/Time Functions
    • TODAY
    • NOW
    • EOMONTH
    • EDATE
    • WEEKNUM
    • NETWORKDAYS
    • MONTH
    • YEAR
    • DAYS vs DAYS360
    • DAY
    • YEARFRAC
    • Integrate Text function with Today function
  • Math functions
    • ROUND/ROUNDUP/ROUNDDOWN
    • INT

Lesson 6-Workbook Protection

  • Protect particular range or cell
  • Protect the Structure
  • Encrypt file with password
  • Discourage further edits

Lesson 7-Conditional Formatting: Using Formula

  • Highlight a row based on a single value
  • Compare values
  • Compare lists

Lesson 8-Advanced Number Formatting

  • Custom Number Format
    • Control Positive/Negative number appearance
    • Control Text/Zero appearance
    • Leading zeros
    • Add text with numbers
    • Control large numbers
    • Hide cells

Lesson 9-Intro to PowerPivot

  • PowerPivot Add-in
  • Make Data Models with PowerPivot
  • Generate a Pivot table report based on data models
New York Computer Training Solutions LLC
(646)345-5713
info@nyctsolutions.com