Microsoft Excel Training – Level 3


CREATING TEMPLATES

Working with Templates

  • Creating & customizing your own templates

  • Opening & modifying a customized template

  • Creating & saving new workbooks from your customized template

ADVANCED FUNCTIONS

Lookup Functions

  • Using Choose or Index function

  • Combining Vlookup with Data Validation

Math & Trig Functions

  • Using SumIF or SumIFS

  • Using a Round function

  • Using a Rand function

Statistical Functions

  • Using CountA, CountBlank, CountIF, or CountIFS functions

  • Using AverageIF or AverageIFS functions

Conditional Logical Functions

  • Using IF function with AND/ OR function

  • Using False or True

Text Functions

  • Using a Concatenate function

  • Using Left or Right functions

  • Using Lower or Upper functions

FORMULA AUDITING

Auditing & Tracing Cell References

  • Evaluating formulas

  • Tracing cells that contain formulas that refer to other cells (dependents)

  • Tracing cells that are referred to by a formula in another cell (precedents)

  • Hiding all the auditing arrows

  • Opening the watch window & adding cell watches

Tracing Errors

  • Circling invalid data & clearing validation circles

  • Correcting calculation errors

  • Setting error checking options

  • Scanning & correcting all errors

WHAT IF ANALYZIS TOOLS

Exploring Data Tables

  • Working with single variable data tables

  • Working with a two-variable data table

  • Using multiple formulas in a data table

Working with Goal Seek

  • Using trial & error

  • Using goal seek

Working with Scenarios

  • Creating scenarios to analyse possible situations

  • Saving current data as a scenario

  • Adding data for new scenarios

  • Applying a scenario

  • Editing & deleting scenarios

Working with the Solver

  • Installing the solver

  • Using the solver to solve complex problems

  • Working with the solver options

  • Viewing & printing reports

GETTING EXTERNAL DATA

Importing Data

  • Using text import wizard

  • Importing data from Access

  • Importing from the Web

  • Copying data from other applications