Thurs Oct 18, 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