Excel Level 4: Advanced Functions & Formulas
Build practical, job-ready Excel skills with hands-on, instructor-led training focused on real business tasks.
Course objective
Excel is one of the most powerful applications ever created. Take your skills and knowledge beyond the basic and intermediate level functions. Learn to harness the power that Excel offers by using more advanced formula techniques. Once you've worked with these functions, you'll be able to make Excel do some pretty amazing things. In this course we'll cover up to 40 Excel functions in a single day! Office 365 Functions: Learn to use the powerful XLOOKUP and XMATCH, and you will never need to use any other lookup function. You will also learn to use IFS, MINIFS, and MAXIFS as well.
Prerequisites
Due to the content, this course is fast paced. You should be very comfortable with the use of functions in Excel prior to enrolling. To ensure your success, we highly recommend taking Excel Levels 1, 2, & 3 or have an advanced level knowledge. Before enrolling in this class, you should feel comfortable using the following: IF and nested IF functions
Course outline
- Introduces several lookup-related functions to find a value in a table, column or row, including the three primary lookup functions plus alternatives, and how to create flexible lookup formulas for frequently changing data.
- Covers important text functions and how to convert text-based numbers and dates into actual values.
- Explores several Date and Time functions and how Excel handles time-based data.
- Advanced and lesser-known math functions for cumulative sums, multi-condition averages, and counting specific records.
- Frequently used statistical and rounding functions applied to useful scenarios such as correlation, rounding to a value, and converting units.
- Several Financial-related functions and the different ways they can be used.
- Error messages (#DIV/0, #N/A, etc.) and the functions and tools to help handle them.
- Introduces the concept of array formulas with useful examples.
- Additional functions plus an introduction to using VBA to create User-Defined functions (built in the Level 5 & 6 courses).
- Functions covered include: IFERROR, CHOOSE, XLOOKUP, XMATCH, INDEX, MATCH, VLOOKUP, HLOOKUP, LOOKUP, DATE, DATEVALUE, EOMONTH, NETWORKDAYS, WEEKDAY, CONCATENATE, LEFT, RIGHT, MID, SEARCH, REPLACE, FLOOR, FORECAST, NPV, PMT, CORREL, CSE Array Functions, and UDF Functions.