Skip to Content

Booking Enquiries: 0800 077 36 46 Customer Services: 0800 077 36 24

Microsoft Excel Course – Module 4 Advanced Formulas And Functions

Microsoft Excel Course – Module 4 Advanced Formulas And Functions

Microsoft Excel Course – Module 4 Advanced Formulas And Functions

Price: £420.00+VAT
Duration: 2 Days
Location:Crawley
Date(s):
Print

Is this course right for me?

Our Excel Module 4 – Advanced Formulas and Functions course enables existing users of Excel to develop their skills in writing formulas and in using advanced Excel functions and techniques.

This course is designed for experienced users of Excel. You should be confident using Excel and should be able to write basic formulas and functions.

Training course objectives:

  • Use an extensive range of logical, text and date functions
  • Create simple array formulas
  • Cross reference data from other worksheets using LOOKUP, INDEX and MATCH functions
  • Use IS functions to test cell values
  • Create nested conditional formulas linking data between worksheets
  • Set advanced conditional formatting and data validation
  • Use the What If analysis tools

Versions Available

2003, 2007. 2010

Related Training Courses in Our Range

Working with text

  • Splitting and merging
  • Extracting data using text functions
  • Formatting text using functions

Working with dates and times

  • Formatting dates and times
  • Date and time functions
  • Calculating date and time differences

Using functions to round numbers up and down

  • ROUND
  • ROUNDUP
  • ROUNDDOWN

Working with logical functions

  • Using logical statements to test a value (IF, AND, OR)
  • Combining more than one function

Calculating values based on given criteria

  • SUMIF
  • COUNTIF
  • Conditional Sum Wizard

Array formulas

  • Using array formulas
  • TRANSPOSE
  • Calculate a conditional average

Lookup and reference functions

  • Using VLOOKUP and HLOOKUP
  • Using INDEX and MATCH

Using IS functions to text value types in cells

  • ISBLANK
  • ISNUMBER
  • ISTEXT
  • ISERROR

Interactivity between worksheets

  • Creating data validation lists from ranges
  • Nested lookups
  • Conditional linking
  • Creating graphs on variable data

Nested conditional functions

  • The forms toolbar (2003) / tab (2007 and 2010)
  • Adding check boxes
  • Adding option buttons
  • Setting properties
  • Nesting logical and statistical functions

Advanced conditional formatting and data validation

  • Conditional formulas
  • Colour banding
  • Finding cells with conditional formatting
  • Finding and preventing duplicate values

Forecasting with What If analysis tools

  • Using Goal Seek
  • Using Solver

Using the Solver

  • Installing the Solver add in
  • Solver steps
  • Adding constraints
  • Running a Solver model

Scenarios

  • Creating scenarios
  • Producing scenario reports

Data tables

  • Creating one and two input data tables

Functions

  • PMT
  • MOD
  • ROW