Microsoft Excel Formulas Expert
1 day course
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.
In-Person Training
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
- Understanding a dynamic array
- Handling a #SPILL Error
- FILTER Function
- UNIQUE Function
- SORT and SORTBY Functions
- Classic array formulas
- 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
