We can host this training at your preferred location. Contact us!

In previous Excel courses, you would have learnt to write formulas and functions to perform calculations using a variety of techniques. This course takes your use of functions to the next level and teaches you many more functions within Microsoft Excel.

The course examines the use of functions with real-world scenarios.

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

Upcoming Trainings

Join our public courses in our New Zealand facilities. Private class trainings will be organized at the location of your preference, according to your schedule.

Classroom / Virtual Classroom

11 July 2024