Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Excel Dynamic Array: 8 Must-Know Formulas

Excel Dynamic Array: 8 Must-Know Formulas

This Excel Dynamic Array tutorial is suitable for users of Excel for Microsoft 365.  

Objective 

Explore Microsoft’s NEW Excel Dynamic Array Functions and use them effectively to solve problems.  

This guide covers:

Table Of Contents
  1. Objective 
  2. Excel Dynamic Array Explained 
  3. VIDEO TUTORIAL – Excel Dynamic Array Functions
  4. 8 Must Know Excel Dynamic array Functions 
  5. Let’s Conclude
  6. FAQS


Excel Dynamic Array Explained 

Dealing with an Excel array is quite complicated in itself, don’t you think? 

What if there is a way to handle an Excel array easily,  and get your desired results without any hassle?  

Let’s say, you are managing your school records and you want to search only for the marks of students from the east block and have taken only the English exam. 

Is there any direct Excel array formula or method that can accomplish this without making life difficult?

Enter Excel Dynamic array formulas. It does many complicated things the easy way. We at Simonsezit simply love it. 

Related: 

Easily Make a Bullet Chart in Excel—1 Bonus Video Included

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Easily Create an Excel Histogram?—2 Best Methods

Excel Dynamic Array Formulas were introduced by Microsoft in 2018 and are the most significant change to Excel formulas in years. Dynamic Arrays allow you to work with multiple values at the same time in a formula.

This feature enables Excel formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.  

The process of returning multiple values from one Excel array formula is referred to as ‘spilling’ because the results appear to spill across the worksheet. 

This makes this new Excel Dynamic Array functions very powerful and flexible with various possible applications. They are definitely a very important addition to the Excel formulas toolbox.

If you are worried that these Excel dynamic array functions might be very complicated and hard to implement in your project, don’t fret. We are here to help. 

In this guide, we have elucidated all the 8 Excel formulas related to Excel dynamic array with examples for your convenience. So grab your coffee, crack your knuckles, and get ready to sharpen your Excel skills. 

VIDEO TUTORIAL – Excel Dynamic Array Functions

Excel Dynamic Array Functions – Free Excel course

8 Must Know Excel Dynamic array Functions 

Microsoft has added these  8 new Excel formulas that use dynamic arrays to solve problems.

Function Purpose 
1. FILTER Filter data and return matching records 
2. RANDARRAY Generate an array of random numbers 
3. SEQUENCE Generate an array of sequential numbers 
4. SORT Sort range by column 
5. SORTBY Sort range by another range or array 
6. UNIQUE Extract unique values from a list or range 
7. XLOOKUP Modern replacement for VLOOKUP 
8. XMATCH Modern replacement for the MATCH function

In this guide, we are going to look at an example of each Excel dynamic array function along with their respective syntaxes. We’ll also see how each Excel array formula behaves with and without optional arguments. 

FILTER 

The  FILTER is an Excel array formula that filters data based on criteria that you specify. It returns all matching records, and if it cannot find any records, it will return a value that you specify in the formula.  

=FILTER (array, include, [if_empty]) 

In the example below, Excel uses the FILTER function to look in the Excel array B4:E23 for the ‘Block’ cell range and pull out the value located in cell G4 (East).

This is our first criteria. It then looks in the ‘Exam’ cell range and pulls out the value located in G7 (English). The two quotation marks tell Excel to display nothing if it cannot find any matches.  

Filter Function Example - Excel Dynamic Array functions
Filter Function Example

This is the result: 

Filter function result
Filter function result

RANDARRAY 

The RANDARRAY function generates random numbers. The size of the Excel array is specified by the rows and columns argument.

You can also select the range in which the number will fall and if you want them to be decimals or integers (whole numbers). It is like a more powerful version of the RANDBETWEEN function.  

=RANDARRAY ([rows], [columns], [min], [max], [integer]) 

In the example below, Excel has used the RANDARRAY function to output a table of random decimal numbers, eight rows high, and four columns wide.  

RANDARRAY Example
RANDARRAY Example

Now let us look at an example that utilizes the optional arguments.  

In this example, Excel has used the RANDARRAY function to output a table of random numbers six rows high, six columns wide, with values between 10 and 50. The TRUE argument tells Excel to output integer (whole) numbers as opposed to decimals.  

RANDARRAY RESULT
RANDARRAY RESULT

Also Read:

Excel Quick Analysis Tool – The Best Guide (5 Examples)

How to Lock Cells in Excel?— 4 Best Methods with Examples

How To Find Duplicates in Excel? (3 Easy Methods)

SEQUENCE 

The SEQUENCE function generates a list of sequential numbers. The Excel array can be one-dimensional or two-dimensional, and you can specify the start value and the step value. If only the rows argument is defined, Excel will output a list of values starting at one by default.  

=SEQUENCE (rows, [columns], [start], [step]) 

In this example, Excel has used the SEQUENCE function with just the rows argument to output a list that starts at one.  

SEQUENCE FUNCTION
SEQUENCE FUNCTION

In this example, we are adding a value for the optional column’s argument. The result is a list of sequential values seven rows high and two columns wide, starting at one.  

SEQUENCE Result 1
SEQUENCE Result 1

In this example, Excel has used the SEQUENCE function to output a list of sequential numbers ten rows high, eight columns wide, starting at ten and increasing in steps of five.  

SEQUENCE Result 2

SORT 

The SORT function in Excel sorts the contents of a range using one or more columns.  

=SORT (array, [sort_index], [sort_order], [by_col]) 

By default, the SORT function will sort values in ascending order using the first column. You can utilize the optional arguments to control which column to sort by and in what order.  

In this example, Excel has used the SORT function to sort Marks in the left-hand table in descending order and output the results to the table on the right.  

The SORT looks in the array B4:E23 for the 4th column and sorts the results in descending order.  

SORT Function
SORT Function

If no [sort_order] argument is specified, the default sort is in ascending order.  

SORT Result
SORT Result

SORTBY 

The Excel SORTBY function sorts the contents of a range based on the values from another range. You can do multiple sorts on the same data.  

=SORTBY (array, by_array, [sort_order], [array/order], …) 

In this example, Excel uses the SORTBY function to sort the information in the table on the left by the Mark in descending order. It outputs the results to the table on the right.  

SORTBY Function
SORTBY Function

In this example, Excel uses the SORTBY function to sort the information in the table on the left-hand side by the Mark in descending order and then by the Student Name in ascending order.  

SORTBY Result
SORTBY Result

UNIQUE 

The UNIQUE function returns a list of unique values from a range.  

=UNIQUE (array, [by_col], [exactly_once]) 

In this example, Excel uses the UNIQUE function to output a list of unique values in a specified range.  

UNIQUE Function
UNIQUE Function

In this example, Excel uses the UNIQUE function to return a list of all unique items in a horizontal range.  

UNIQUE Result
UNIQUE Result

XLOOKUP 

The XLOOKUP function is a modern replacement for functions like VLOOKUP, INDEX, and MATCH. It can lookup in vertical and horizontal ranges and supports approximate and exact matches.  

=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) 

In the example below, Excel has used the XLOOKUP function to lookup the App listed in cell H4 in the App cell range and return the corresponding result from the Revenue column.  

XLOOKUP Function
XLOOKUP Function

XMATCH 

The XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is the latest iteration of the MATCH function and supports approximate and exact matching. 

=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode]) 

In this example, Excel uses the XMATCH function to look up the value in cell F3, in the App named range. It is doing an exact match for the word ‘Slack,’ and it starts the lookup from the first item in the list.   

XMATCH Function
XMATCH Function

Let’s Conclude

We are nearing the end of this guide on Excel Dynamic array formulas.  Let’s wrap this up with the key takeaway of Dynamic arrays in Excel. 

Dynamic arrays are used whenever we need to dynamically derive multiple values from an array of data using a single formula. Additionally, Dynamic Arrays in Excel are a great addition to the Excel formulas catalogue.

We’ll cover more similarly complex Excel formulas with interesting use-cases in our upcoming guides in great detail. 

Learn more about dynamic arrays and other advanced Excel formulas from our Excel courses

Suggested Reads: 

Sumif With Multiple Columns – The Sumifs Function In Excel

The Best Excel Project Management Template in 2021

How to Use Excel COUNTIFS: The Best Guide

FAQS

What are dynamic arrays in Excel?

Dynamic arrays are nothing but a special case of an Excel array that dynamically calculates and returns values through a single Excel array formula.

Which version of Excel has dynamic arrays?

Only Excel versions of Office 365 subscriptions are dynamic arrays enabled. 

What is a Spilled array?

A spilled array is nothing but an Excel dynamic array that has yielded multiple values for the Excel array formula.

 

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

Other Excel classes you might like:

  • Protecting WorkBooks in Excel
  • Logical Functions in Excel
  • Designing Better Spreadsheets in Excel
  • Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses available.

Jenny Ann Valenciano

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Start Co-Authoring Excel Workbooks in 6 Easy Steps

Create and Update an Index in Word

How to Set Up Customers and Jobs in QuickBooks Pro 2017 Part 1

How to Add Error Bars in Excel? 7 Best Methods

How to Create Amazing Presentations in Microsoft PowerPoint

Tips and Tricks for Microsoft Word 2010

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)