Excel Dynamic Array Formulas Training Course
Table of Contents
Excel Dynamic Array Formulas Training Course Overview
Our Excel Dynamic Array Formulas training course will give you an in-depth appreciation of how this upgrade will fundamentally change the way we work with Excel formulas. We discover the new dynamic array functions and how all formulas are affected by the new Excel calculation engine.
Dynamic arrays and the new functions are only available Excel 365. Neither Excel 2016 or Excel 2019 support dynamic arrays.
Five Key Topics
- Use the new dynamic array functions, such as FILTER or UNIQUE
- Understand how dynamic array formulas interact with all Excel formulas
- Handle the #SPILL error message
- Refer to dynamic ranges using the # sign
- Simplify complex Excel formulas
Excel Dynamic Array Formulas Course Details
Course Duration : 1 day
Course Availability : Monday – Saturday
Course Types : Group booking, 1-2-1 sessions, Bespoke & Public Courses
Training Times : Our courses run run from 09:30 to16:30, course registration 09:00 – 09:30, refreshments available throughout the day. A buffet lunch is available – please discuss with the team.
Facilities : A relaxed environment, air conditioned training rooms, note pads, pens and unlimited access to our online course materials.
Excel Dynamic Array Formulas Training Course Details
Course Location : Face to face group / public & onsite training available. Virtual training courses available.
Class Size : As a guide we recommend class sizes to be no more than eight.
Virtual / Online training Format : Teams or Zoom.
Tailored Course Content : Our Excel Dynamic Array course can be tailored to your requirements – Get In Touch
Tailored Courses : We offer a free consultancy service to help tailor the course to meet your requirements.
Centre and Services
Comfortable & modern facilities
Unlimited access to our online training manuals
Complimentary lunch
Courses delivered by Certified Trainers
Microsoft & CPD courses available
Our Trainers
Steve, one of our lead Excel, has over 25 years of delivering Excel Dynamic from basics to advanced levels. All our trainers are encouraged to become Microsoft Accredited within their first year of working at Mouse Training.
Our instructors are very approachable, making the driest subject matter fun.
Next Scheduled Online Excel Dynamic Array Introduction Course
On Completion of Excel Dynamic Array Formulas
- Define the new dynamic array formulas
- Understand the use of spill ranges and handle SPILL errors
- Use the SORT, FILTER and UNIQUE functions in combination
- Generate sequential datasets
- Produce arrays of random numbers
- Manipulate ranges with Shaping and Arranging functions
- Understand the new XLOOKUP and XMATCH functions
- Appreciate how dynamic arrays can affect existing Excel formulas
- Use dynamic arrays with Excel Data Validation
Excel Dynamic Array Formulas Course Outline
- FILTER, filter a range or array
- RANDARRAY, generate array of random numbers
- SEQUENCE, generate array of sequential numbers
- SORT, sort a range or array
- SORTBY, sort range by another range or array
- UNIQUE, extract unique values from a list or range
- XLOOKUP, replacement for VLOOKUP
- XMATCH, replacement for MATCH
- CHOOSECOLS and CHOOSEROWS, returns the specified columns or rows from an array
- DROP, removes a certain number of rows or columns from an array
- EXPAND, grow an array to the specified number of rows and columns
- HSTACK and VSTACK, combines two arrays horizontally or vertically in sequence
- TAKE, extracts a specified number of contiguous rows or columns from the start or end of an array
- TOCOL and TOROW, converts an array or range to a single column or row
- WRAPCOLS and WRAPROWS, converts a row or column into a 2D array based on the specified number of values per column or row
- Spill ranges and handling SPILL errors
- Using the hash sign (#), the new array reference notation
- Using the @ sign, the implicit intersection operator
- Combining functions
- Referencing spilled data ranges in other Excel sheets
- Sorting with a single argument
- A sort based on two or more columns
- Horizontal sort
- A random sort using SORT and RANDARRAY
- Sort by something that is not in the results
- Performing a multi-column sort
- Using the FILTER function with one condition
- Using FILTER with multiple conditions
- FILTER for both criteria or either criteria
- Excel FILTER to exclude blanks, zeros and text
- Syntax of the UNIQUE Function
- Understanding unique versus distinct
- Nesting array functions: SORT, UNIQUE and FILTER
- Generating a range of sequential numbers
- Using SEQUENCE inside another function
- Using TODAY and SEQUENCE for a calendar
- Generating sequential letters with CHAR, SEQUENCE and TEXTJOIN
- Returning the N Largest Items using LARGE and SEQUENCE (Top Ten etc.)
- Generating an array of Random Numbers with RANDARRAY
- Using RANDARRAY values for Modelling and Simulation
- Extracting and removing data with TAKE, DROP, CHOOSEROWS and CHOOSECOLS
- Combining data with HSTACK and VSTACK
- Re-arranging data using TOROW, TOCOL, WRAPROWS and WRAPCOLS
- XLOOKUP versus VLOOKUP
- XMATCH versus MATCH
- Making approximate, exact or partial matches with XMATCH
- Returning 12 months of data with one XLOOKUP
- A Two-Way lookup with XLOOKUP
- How legacy formulas are affected by Excel’s new calculation engine
- Handling Absolute and Mixed Cell Referencing
- SUMIFS and COUNTIFS can have more than one criteria in the same column
- VLOOKUP with Excel Dynamic Arrays
- The Excel IF function and Excel Dynamic Arrays
- Calculating frequency distributions using FREQUENCY
- Transposing with a shorter Formula
- Creating a Crosstab Report (Pivot Table) with three formulas
- Replacing a What-If Data Table with one formula
- Showing formulas for a range with FORMULATEXT
- Summing the lengths of many cells
- Applying different icons using the SIGN function
- Using a formula to convert Text to Columns
- Returning the proper case of all names with one formula
- Data Validation List using a spill range
- Excel Drop-down List excluding blanks
- Using Dynamic Arrays for Dependent Validation Drop-down lists
- Conditional Formatting and Excel Dynamic Arrays
- Excel Charts with Dynamic Arrays
Is this Excel Dynamic Array Course for You?
Give us a call, our admin team will be delighted to assist in making sure you are on the correct course level.
