Microsoft Excel Advanced Functions

Course Overview

The Excel Advanced course is aimed at those who would like to learn more of the functions in Excel. There are over 80 functions covered in this course

Detailed Course Content

Data Information

  • Text length: LEN
  • Range info: AREAS
  • Cell info: CELL
  • Column info: COLUMN, COLUMNS
  • Row info: ROW, ROWS

Data Cleansing

  • Text case: LOWER, UPPER, PROPER
  • Extract text: LEFT, MID, RIGHT
  • Remove characters: CLEAN, TRIM
  • Replace text: REPLACE, SUBSTITUTE
  • Format: TEXT, VALUE
  • Join text: CONCAT, TEXTJOIN
  • TRANSPOSE

Date Functions

  • System date: TODAY, NOW
  • Days: DAY, DAYS, WEEKDAY
  • Workdays: WORKDAY, NETWORKDAYS
  • Week: WEEKNUM
  • Month: MONTH
  • Year: YEAR

Adding Numbers

  • Add range: SUM
  • Add using criteria: SUMIF, SUMIFS, DSUM
  • Add multiples: SUMPRODUCT

Counting Values

  • Count numbers: COUNT
  • Count non-empty cells: COUNTA
  • Count blank cells: COUNTBLANK
  • Count using criteria: COUNTIF, COUNTIFS, DCOUNT, DOUNTA

Average Values

  • Average range: AVERAGE, AVERAGEA
  • Average using criteria: AVERAGEIF, AVERAGEIFS, DAVERAGE
  • MEDIAN
  • MODE

Displaying Numbers

  • Rounding: ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, CEILING.PRECISE, EVEN, ODD
  • Integers: INT
  • Absolute values: ABS
  • Random numbers: RAND, RANDBETWEEN

Lookup Functions

  • Search column: VLOOKUP
  • Search row: HLOOKUP
  • Search range: LOOKUP, CHOOSE, MATCH

Logical Functions

  • Return true or false value: IF, IFS
  • Multiple conditions true: AND
  • One condition true: OR
  • Error alternatives: IFERROR, IFNA
  • Cell status: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISPMT, ISREF, ISTEXT

Printable course information
View Course Calendar

Pre-requisites