Description
You’re fluent in Excel and know the most common Excel functions. Still, it’s important to keep your knowledge up to date. In addition to our standard Formulas and Functions course, you can now enroll in this must-have training. Developed by our expert Digital Coaches, this brand new course will ensure that your Excel skills help you solve complex problems more efficiently.
Objective
After this training, you can:
- apply the latest Excel features;
- perform accurate and consistent data analysis;
- solve more complex issues by combining functions.
Target group
This Excel course is perfect for anyone who uses Excel regularly and wants to learn the latest and most useful features.
Prerequisites
You have a good working knowledge of Excel in general, as well as the most important basic functions.
Content
Introduction
Overview of the course content and objectives.
Brief introduction to the evolution of Excel and the need for new features.
Part 1: Getting started with dynamic arrays
Dynamic arrays are ideal for quickly extracting relevant data from a large dataset. They become even more interesting when combined with other advanced features.
- UNIQUE(): extracts all unique values from a dataset. Perform additional calculations on it.
- FILTER(): Filter specific data from a wide range of data.
- SORT() / SORTBY(): add a dynamic sorting by, for example, customer name or sales figures without having to adjust, modify, duplicate the original data.
- SEQUENCE(): automatically create a series of dates or a timeline.
- RANDARRAY(): Create a string of random numbers, useful for simulations or generating test data.
- #SPILL!: Error messages you may encounter with dynamic arrays.
Part 2: Text functions
Text functions in Excel are essential for combining, splitting, searching, validating, and more on text data to promote accuracy and consistency.
- TEXTSPLIT(): no longer split denormalized data with ‘Text To Column’ but use this new, dynamic version that allows you to split vertically as well as horizontally!
- TEXTJOIN(): discover the added value of TEXTJOIN() compared to CONCAT() thanks to the definition of one or more separators as well as the ignoring of empty cells.
- TEXTBEFORE() and TEXTAFTER(): Use these functions as a simplified and more efficient version of LEFT(), RIGHT(), or MID().
Part 3: Logical and search features
Logic and search features in Excel help you quickly find the right information and make smart decisions, even in a sea of data.
- IFS(): done to nest multiple conditions with IF. Use this more extensive and simpler version.
- SWITCH(): e.g. automatically generate region-specific percentages.
- XLOOKUP(): learn why it’s the most powerful alternative to VLOOKUP.
Part 4: Other new features
- LET(): Increase readability for complex and longer functions by naming calculation results.
- LAMBDA(): Create reusable functions with personal naming, without VBA programming.
- FORMULATEXT(): This function allows you to better document the content of calculations, make them more transparent and check them more accurately.
