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

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

Public Training Programme

Explore our current selection of public courses, available as convenient and cost-effective virtual sessions.

Engage with expert instructors and a diverse peer group in an interactive, online learning environment.

Ideal for those valuing flexibility and affordability, our public courses are ideal where you only have small numbers requiring training and offer training from the comfort of your home or office.

View our full catalogue of Leadership, Personal Development, Systems, Office Applications and Project Management courses

Contact us

Do you have questions, suggestions, or feedback? We value your input and are here to assist you. Please don't hesitate to get in touch with us via email or phone:

• Email: [email protected]
• Phone: +44 (0)20 8152 6551

Our dedicated team is ready to respond to your enquiries and provide the support you need. Feel free to reach out, and we'll do our best to assist you.

Thank you for visiting our website, and we look forward to hearing from you!