Description

Enhance your skills and learn how to use a range of advanced formulas and functions to manage and extract data more efficiently. There are some great ones here that will save a lot of time.

Course overview

Duration: 1 day (6.5 hours)

Our Excel Module 4 – Advanced Formulas and Functions course looks at some of the more advanced functions of Excel that can be used to manipulate and extract data.

This is an intensive one-day course and designed for experienced users of Excel. Participants should be confident in creating and using Excel and be able to build simple formula before attending.

Objectives

By the end of the course you will be able to:

Use a range of Text functions to manage text data
Create and use different types of date and time functions
Work with rounding functions
Use logical statements
Build nested formula
Use SUMIF, COUNTIF and AVERAGEIF
Cross reference data from other worksheets using LOOKUP, INDEX and MATCH functions
Use IS functions and IFERROR to test cell values

course image

Content

Working with text

Splitting text into multiple cells
Merging data into one cell
Extracting data using text functions
Tidying data

Working with dates and times

Formatting dates and times
Date and time functions
Calculating date and time differences

Using functions to round numbers up and down

ROUND
ROUNDUP
ROUNDDOWN
CEILING
FLOOR

Use lookup functions

Lookup functions
Creating a lookup table
Range naming
Using VLOOKUP
Using HLOOKUP
Using INDEX and MATCH

Working with logical statements

Creating an IF statement
Using the AND plus OR functions
Nesting functions

Calculating values based on given criteria

SUMIF(S)
COUNTIF(S)
AVERAGEIF(S)

Using IS and IFERROR and test cell values

ISBLANK
ISNUMBER
ISTEXT
ISERROR
IFERROR

Course Images

Excel Module 4 Advanced Formulas and Functions

By Underscore Group

Booking options

Highlights