COURSE INFORMATION

The aim of the course is to enable you to understand and apply Functions and use nested Functions where more than one function will be required.  A variety of other functions from the content list below will be covered from categories delegates require. As new functions are added by Microsoft these will be included where appropriate.

  • Functions types and the Insert Function tool
  • Using Nested functions to create more complex formulas
  • Formula Auditing Tools

Nesting Functions

  • Using Complex Functions
  • AND, OR, and NOT functions with IF
  • Using IFS for multiple conditions
  • SUMIF, COUNTIF, COUNTBLANK

Formula Auditing Tools

  • Trace Dependant and Precedent cells
  • Evaluate formula tool

Date and Time Category

  • TODAY, NOW, NETWORKDAYS, EOMONTH

Text Category

  • Extracting specific data with the MID, LEFT and RIGHT functions
  • Using the TRIM function to remove unwanted spaces in a cell
  • Using ampersands and CONCATENATE to combine data from different cells
  • Using TEXTSPLIT, TEXTBEFORE. TEXTAFTER to extract data

Information Category

  • Extracting information with the CELL and INFO functions
  • ISERR, ISERROR, IFERROR, and ISNA error-checking functions

Advanced Lookup and Reference Category

  • Using MATCH and INDEX functions together
  • Using the XLOOKUP function
  • Using VSTACK, HSTACK, TOCOL, TOROW, TAKE, DROP to append data into an array
  • UNIQUE and EXPAND functions

Statistical Category

  • Finding the middle value with MEDIAN and most common value with MODE
  • ROUND, ROUNDUP, and ROUNDDOWN functions

Reference Category and Advanced Data Validation

  • Getting data from remote cells with the OFFSET function
  • Using INDIRECT with Data Validation for two-tiered pick lists

A good knowledge of the following features is beneficial:

  • Formula structure and rules
  • Absolute cell references
  • Use the Insert Function tool
  • Aggregate Functions (SUM, COUNT, AVERAGE, MAX, MIN)
  • Range Names
  • £145
  • Half day trainer led or virtual