Mastering Excel Functions Training

This course is aimed at existing Excel users who need to further their knowledge. Delegates are assumed to have experience of the following:

• Create, edit, and format spreadsheets
• Navigate within worksheets and books
• Use Insert Function to create functions
• Work with absolute references (e.g. $A$1)
• Create formulas using functions such as IF or VLOOKUP
• Create named ranges
• Create Tables within Excel
• Sort and filter data

At the end of this course, you’ll be able to:

• Use fundamental Excel functions in advanced situations
• Combine and nest Excel functions
• Understand a wide variety of Excel functions and their use
• Understand Dynamic Array functions and the benefits of their use

Module 1: Function Writing Review

• What is a function?
• Reading function syntax
• Using range names
• Using tables
• Recap of common functions
  • SUM
  • AVERAGE
  • AVERAGEA
  • MEDIAN
  • MODE.SNGL and MODE
  • MIN and MAX
  • COUNT, COUNTA and COUNTBLANK

Module 2: Ranking and aggregate functions

• Functions for ranking data
  • SMALL and LARGE
  • MINA and MAXA
  • RANK, RANK.AVG and RANK.EQ
• Calculating quartiles and percentiles
  • QUARTILE, QUARTILE.INC and QUARTILE.EXC
  • PERCENTILE, PERCENTILE.INC and PERCENTILE.EXC
• Aggregating data
  • SUBTOTAL and AGGREGATE

Module 3: Rounding numbers

• Common rounding functions
  • ROUND, ROUNDUP and ROUNDDOWN
• Rounding to multiples
  • MROUND
  • CEILING.MATH and FLOOR.MATH
• Eliminating decimal places
  • INT and TRUNC

Module 4: Nesting functions

• Introducing nesting
• Nesting examples

Module 5: Calculate using selective data

• Criteria-based functions
  • COUNTIFS
  • SUMIFS, AVERAGEIFS, MINIFS and MAXIFS

Module 6: IF and related functions

• The IF functions
  • IF
  • IFS
  • IFERROR and IFNA
  • AND, OR and XOR
  • Popular related functions: CHOOSE and SWITCH

Module 7: Array functions

• Introducing arrays
• Legacy array formulas and functions
• Dynamic array formulas and functions
  • UNIQUE
  • SORT
  • SORTBY
  • FILTER
  • SEQUENCE
  • TRANSPOSE
  • MODE.MULT

Module 8: Lookup and reference functions

• Lookup functions
  • VLOOKUP, HLOOKUP and XLOOKUP
  • INDEX, MATCH and XMATCH
• Other lookup functions
  • GETPIVOTDATA
  • OFFSET
  • INDIRECT
  • FORMULATEXT

Module 9: Date and Time functions

• Excel and date and time data
• Date and time essentials
  • TODAY and NOW
  • DAY, MONTH and YEAR
  • HOUR, MINUTE and SECOND
  • DATE and TIME
  • WEEKDAY
• Calculating from a date
  • EDATE
  • WORKDAY and WORKDAY.INTL
  • DATEDIF
  • YEARFRAC
• Calculating differences between dates

Module 10: Text functions

• Introducing text functions
• Combining text strings
  • CONCAT and CONCATENATE
  • TEXTJOIN
• Extracting from within a text string
  • LEFT and RIGHT
  • MID
  • LEN
• Finding and replacing with functions
  • FIND and SEARCH
  • REPLACE and SUBSTITUTE
  • TRIM and CLEAN
• Changing case
  • UPPER, LOWER and PROPER
• Converting data types
  • VALUE
  • TEXT

Module 11: Custom functions

• Introducing LET and LAMBDA