Microsoft Excel for Super Users: Advanced Functions and Formulas

Versions: All versions: 2007 to Office 365

Description

An advanced course beyond advanced! There is never time in an ordinary advanced course to go into many important advanced functions. And not everyone needs them. But, if you really want to take your Excel to super user level, then you need to know how to use these functions and how to combine them together.

Duration

1 day

Prerequisites

This is a specialist Excel course. It is for financial reporting. Knowledge to advanced level is a pre-requisite. You need to work on Excel daily, analysing and reporting on large amounts of data.

Module 1: Array formulas

  • Multiple calculations
  • Single calculations
  • One-dimensional array formulas
  • Two-dimensional array formulas

Module 2: Text functions

  • Concatenate vs & (ampersand)
  • Textjoin (Office 365)
  • Text
  • Dollar
  • Fixed
  • Value
  • Len
  • Replace
  • Search

Module 3: Date functions

  • Edate
  • Eomonth
  • Networkdays
  • Workday

Module 4: Logical functions

  • Not
  • IF(OR)
  • XOR (2013 onwards only)
  • IF(AND) for between values
  • Nested IF
  • IFERROR
  • IFNA

Module 5: Lookup & Ref functions

  • COLUMN
  • COLUMNS
  • ROW
  • ROWS
  • MATCH
  • VLOOKUP refresh
  • VLOOKUP with &
  • VLOOKUP + COUNTIF (match 2nd, 3rd etc values)
  • VLOOKUP WITH MATCH
  • VLOOKUP with IF
  • INDEX
  • INDEX and MATCH
  • OFFSET
  • INDIRECT
  • CHOOSE

Module 6: Maths & Trig functions

  • INT
  • MOD
  • ABS
  • SIGN
  • SUMIFS
  • MROUND
  • SUMPRODUCT
  • AGGREGATE

Module 7: Statistical functions

  • AVERAGEIF
  • AVERAGEIFS
  • COUNTIF
  • COUNTIFS
  • MEDIAN
  • LARGE
  • SMALL
  • CORREL/CORRELATIONS
  • FORECAST
  • FREQUENCY

Module 8: information functions

  • ISBLANK
  • ISERROR/ISERR
  • ISNA

Request a quotation

Please type your full name.
Invalid email address.
Invalid Input
Individual or company?
How many people require training?
Invalid Input