Microsoft Excel Formulas Expert

This course has been developed for users who need to clean up imported data, or build models for other users. A variety of different Excel functions will be explained and used in practical, real life examples to improve your Excel productivity. Secure your spot in our course in Brisbane by booking online today.

1 day course
4.83  (6 reviews)
What you will learn:
  • Explore advanced formulas using logical, text, data and time functions
  • Understand and use array formulas
  • Use a range of different lookup functions to extract data from lists
  • Apply complex conditional formatting using a formula
  • Establish data entry rules with advanced data validation
  • Write formulas to handle errors, and audit a workbook to find error sources
What you'll get:
Workbook
Refreshments
Capped class sizes
eCertificate
Post Course Support
Course prerequisites

Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Advanced course, including common functions such as SUM, IF, and VLOOKUP.

Do I need to bring my own laptop?

Participants are not required to bring their own laptops as our dedicated computer training labs are equipped with the programs and files you will need access to on your training day.

Topics covered in this training course
  • SUMIFS
  • COUNTIFS
  • Nesting IF functions
  • XLOOKUP
  • VLOOKUP and the MATCH
    function
  • INDEX and MATCH function
  • INDEX and MATCH on
    2 dimensions
  • INDIRECT function
  • Understand the following functions to clean up imported text
  • TRIM function
  • VALUE function
  • CONCATENATE function
  • TEXT function
  • SUBSTITUTE function
  • LEN function
  • TEXT function
  • FIND function
  • LEFT, MID, RIGHT functions
  • TIME function
  • DATE function
  • YEARFRAC function
  • NETWORKDAYS function
  • WORKDAY function
  • DATEVALUE function
  • Understand arrays
  • Complex array calculation
  • Single cell array formulas
  • Multi-cell array formulas
  • Editing and deleting arrays
  • Understanding circular references
  • Understanding error values
  • Trace arrows
  • Go To special
  • Watch window
  • ISERROR function
  • IFERROR function
  • Formula driven Conditional Formatting
  • Rule precedence
  • Applying formatting to an entire row
  • Custom Data Validation
  • Custom validation criteria
  • Prevent duplicates
  • Limit the total of a list
    Reset options
- +

Need your training approved? Get a quote

Need your training approved? Get a quote
4.83
4.83
from 6 reviews
5/5
“Learnt new ideas about short cuts to formulas.”
Jason, Aug 2020
5/5
“Jane is patient and brings it back to our roles.”
Leif, Jul 2020
5/5
“Jane was wonderful, she was so clear and I loved that she talked through the formulas as she as writing them.”
Amy-Louise, Jul 2020
4/5
“Very informative”
Kerry, Jul 2020