Microsoft Excel Advanced Formulas
1 Day Training Course
Venue – Brisbane
Times and Dates
28 May 2018
1 Day
9:00 am –
4:00 pm
Need your training approved?
Reviews
- Aimee, April, 2014. 4 out of 5.
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.
Downloads
Practice files BrochureSkills assessment
Take the test
This course is ideal for
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.
Course prerequisites
Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Advanced 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.
Topics covered in this training course
- Logical and statistical functions
- SUMIFS
- COUNTIFS
- AND function
- OR function
- Combining logical functions
- Nesting IF functions
- Use the VLOOKUP function
- 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
- Text, date and time functions
- 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
- Understand dates and times in Excel
- TIME function
- DATE function
- YEARFRAC function
- NETWORKDAYS function
- WORKDAY function
- DATEVALUE function
- Use arrays
- Understand arrays
- Complex array calculation
- Single cell array formulas
- Multi-cell array formulas
- Editing and deleting arrays
- Error check and audit
- Understanding error values
- Trace arrows
- Go To special
- Watch window
- ISERROR function
- IFERROR function
- Conditional Formatting with formulas
- Formula driven Conditional Formatting
- Rule precedence
- Applying formatting to an entire row
- Data Validation using formulas
- Custom Data Validation
- Custom validation criteria
- Prevent duplicates
- Limit the total of a list
