Excel Module 4 – Advanced Formulas and Functions Course

Book this course

Course description

There are a number of extremely useful formulas and functions within Excel. If you really want to get the best out of your data and boost efficiency, there are some advanced tools and techniques that can help you.

We show you how to create and use some of the more powerful formulas and functions within Excel, providing you with the skills and knowledge to perform complex tasks that will make your data work harder.

Is it right for me?

If you’re already an experienced Excel user, writing basic formulas and functions, this advanced Excel training course will enhance your current skill level.

What will I get out of it?

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


  • Use an extensive range of logical, text and date functions
  • Create simple array formulas
  • Cross-reference data from other worksheets using LOOKUP, INDEX and MATCH functions
  • Use IS functions to test cell values
  • Create nested conditional formulas linking data between worksheets
  • Set advanced conditional formatting and data validation
  • Use the What If analysis tools

The trainer was knowledgeable, able to communicate ideas effectively, and was open to feeback and flexible to what we wanted to get out of the session.

Regulatory Analyst, UK Power Networks

What does it cover?

  • Splitting and merging
  • Extracting data using text functions
  • Formatting text using functions
  • Formatting dates and times
  • Date and time functions
  • Calculating date and time differences
  • ROUND
  • ROUNDUP
  • ROUNDDOWN
  • CEILING
  • FLOOR
  • Using logical statements to test a value (IF, AND, OR)
  • Combining more than one function
  • SUMIF(S)
  • COUNTIF(S)
  • AVERAGEIF(S)
  • Using array formulas
  • TRANSPOSE
  • Calculate a conditional min and max

 

  • Using VLOOKUP and HLOOKUP
  • Using INDEX and MATCH
  • ISBLANK
  • ISNUMBER
  • ISTEXT
  • ISERROR
  • IFERROR
  • Creating data validation lists from ranges
  • Nested lookups
  • Conditional linking
  • Creating graphs on variable data
  • The forms tab
  • Adding check boxes
  • Adding option buttons
  • Setting properties
  • Nesting logical and statistical functions
  • Conditional formulas
  • Colour banding
  • Finding cells with conditional formatting
  • Linked data validation lists
  • The INDIRECT function
  • OFFSET
  • Create one and two way dynamic named ranges
  • Create self expanding charts
  • Create 12 month rolling charts
  • Create interactive scrolling charts

Book this course

Course leader

Richard Harker

Technical & Digital Learning Consultant

Need a bespoke solution?

Talk to us about setting up a course that is tailor made for you.

Recent blog posts

Developing Strategic Leadership

It doesn’t matter how long you’ve been in a leadership role, there is always something new to learn...

Tweets
Press Coverage

Supporting the agents of change

Change is scary, at least for a lot of people it is. It creates uncertainty and a sense of unease, especially when it’s sudden or not handled correctly...

Enhance Magazine