Dynamic Array Functions Are The Best New Excel Feature!

Microsoft just release a brand new feature for Excel called Dynamic Arrays. This is a set of functions, and new functionality, that allows us to create formulas that return multiple results to multiple cells.

🚀UPDATE: These new features are now available to all users on the free Office Insiders Fast channel for Office 365. See the link above for more details.

Dynamic arrays will eventually replace the Ctrl+Shift+Enter array formulas we use today. This will make is so much easier to write array formulas, and work with the output (spill range).

The cells that the dynamic arrays output to are known as the Spill Range. This range is dynamic and automatically updates (spills) as changes are made to the source range or its precedents.

The spill range can be referenced with the new Spill Ref notation (A4#). This is the first cell in the spill range, followed by the hashtag or pound symbol.

In the video I share the new UNIQUE function that removes duplicates and returns a list of unique values to the spill range.

I show how we can combine this with the new SORT function to sort any range.

The FILTER function allows us to apply multiple filters to a range and output the results into a spill range with multiple rows and columns.

This new functionality for Excel makes it much easier to write array formulas, and opens up a new world of possibilities.

These new functions and features are currently available to all users on Microsoft’s Office Insiders Fast (Monthly Targeted channel) program. The Insiders program is free for all Office 365 subscribers and gives you access to early release builds and features. See this link for more info.

Related Content:

00:00 Introduction
02:48 Sort Function
03:46 Sort Unique Function
04:20 Data Validation
08:17 Free Download

[teerthupadhyay]

This is such a neat implementation and faster way. Thanks for tutorial

[purepatrick]

Great video/audio quality, explanations and speed. Very helpful videos. Thanks and congrats on your channel.

[wayneedmondson]

Hi Jon.. as usual.. excellent video. These new dynamic array functions are very exciting. I can't wait to dive into them when they hit the general population. Thanks for the intro and insights as to what is on the horizon. Thumbs up!

[douglasgb]

Finally catching up to Google Sheets on dynamic arrays. One of the only reasons I keep going back to Google Sheets for misc tasks is that it's dead simple to manipulate data into dashboards compared to Excel, that and the docs are more accessible, and scripting is in JS instead of VBA.

[francismawukohadenyoh]

Amazing features indeed. Thanks Jon for sharing with us these features. We are most grateful!

[jonathanwillcox]

That has just helped me save hours on preparing a template spreadsheet. Thank you for a clear explanation and to excel for building such helpful tools

[sasavienne]

These new functions are changing the world of calculations.

[kenmcmillan]

Super Duper, Jon! And thanks so much for sharing these new functions. Can't wait!!!Also, thanks for the link to Bill's book.

[FabioGambaro]

Wish these were available years ago... will have saved a lot of work! :-)

[learnspreadsheets]

So excited!!! I can’t wait to test multi level dependent drop down lists with the filter function, have you tested it yet?

[sakibrahman]

Great video. Thanks a lot 😊 Saved it to my playlist.

[vapermike]

Thanks for the video, it's really helpful!

[oscaruns]

thanks for the video! array formulas will not be a problem anymore...

[johnborg]

Thanks Jon. I can't wait to have them in office 365 si i will start playing about with them. :)

[ExceliAdam]

New Dynamic Array Functions are awesome!

[KambizTazarv]

Great job of explaining it... Thank You! :)

[kironmona]

Really Very Helpful. Nice & Cool Functions...

[zijientseu]

This is very useful! Thank you for the video!

[GyanOnTube]

Really good function..i want to apply the same function in my sheet however i am not able to apply the same formula as i am not getting the option..how would i get this...

[Luciano_mp]

Thanks for the new tips, it will be of great help and will make it much easier!