Chat with us, powered by LiveChat

Microsoft Excel Advanced Formulas

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.

1 day course
What you will learn:
  • Explore advanced statistical and complex logical functions
  • Understand and use array formulas
  • Use a range of different lookup functions to cover a variety of 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
Course prerequisites

Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Advanced course.

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
  • AND function
  • OR function
  • Combining logical functions
  • Nesting IF functions
  • Designing lists to lookup
  • VLOOKUP function
  • VLOOKUP with nested IF
  • 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 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