Dynamic Array Functions in Excel

Explore dynamic array functions in Excel, learn spills, unique vs distinct, multi-criteria unique values by column, sort by, sequence, filter, rand array and randbetween, Xlookup, and Zmax for advanced lookups.

Watch this video-based course module and download the downloadable exercise and instructor files, unzip them, and optionally follow along with HD playback and adjustable speed.

Master dynamic arrays in Excel with a single formula that returns multiple results and reduces errors, using the six new functions: sequence, rand array, unique, sort, sort by, and filter.

Explore spills and arrays in Excel, compare standard formulas with dynamic array formulas, and learn how filter and sort by produce spill ranges.

Explain the difference between distinct and unique in Excel's dynamic array functions: distinct returns each value once, while unique returns values that appear exactly once, demonstrated with country data.

Learn to extract multiple criteria with Excel's unique function, returning distinct rows for country, population, and region; build a dynamic table that updates as you add data.

Use Excel's unique function to extract data by column, identifying distinct crayon packs and removing duplicates across eight packs, including options to return items that appear exactly once.

Sort data horizontally in Excel with sort function, join actors with text join, and highlight duplicates via conditional formatting to reveal actors who appear in same movie more than once.

Explore dynamic array sorting with the sort by function, learn how to sort by another array or column, and apply multiple column sorts to reveal top items.

Learn to perform a horizontal sort with the sort by function, using an order array to rearrange columns into the sequence: title, first name, last name, department, salary.

Explore the sequence dynamic array function to generate grids with rows, columns, start, and step. Unstack records into four columns using countA and index with dynamic arrays, formatted as dates.

Use the dynamic array filter function in Excel 2021 to filter an exam results table by exam, date, and grade, with data validation and unique and sort lists.

Apply or logic in Excel's filter function using the plus operator to combine criteria, such as maths exam or a grade of C, returning rows with exam, grade and date.

Explore how the filter function uses the and logic with the asterisks operator to filter by multiple criteria, and name the table 'students' for dynamic updates.

Learn to use the equals operator with dynamic array filter in excel to return venues that meet both criteria or neither, such as capacity ≥ 400 and DJ = yes.

Learn to use the minus operator in Excel's filter to list venues with capacity at least 400 or a DJ, but not both, using cell references for dynamic criteria.

Discover how to use Xlookup to replace index and match and vlookup, leveraging dynamic array functions, optional arguments, and exact or approximate matches to return single or multiple results.

Learn xmatch, the dynamic array alternative to match, using wildcards and search to locate items and count those exceeding a threshold, then combine with index to return department and location.

practice dynamic array functions in Excel by building unique month lists, applying filter and sort, using XLOOKUP for team, revenue, and profit, and generating dummy data with RANDARRAY.

Congratulations on completing the course on dynamic array functions in Excel. Keep practicing and exploring to unlock exciting possibilities with Excel.