Excel Dynamic Arrays 3 Years On - Have They 'Changed The Game'?
So, it’s now a few years since the release of Excel’s dynamic array formulae. Their arrival was enthusiastically welcomed by many Excel experts, with a promise they would ‘change the game’. Three years on – have they?
In this video, I talk you through the five main Excel dynamic array formulae in the original release – UNIQUE, FILTER, SORT / SORTBY, SEQUENCE and RANDARRAY.
💻DOWNLOAD THE EXCEL FILES
https://tinyurl.com/5654z7sy
00:00 INTRODUCTION
00:36 UNIQUE
Let me get straight to the point, I’m very happy with this formula! In my opinion it should be part of your Excel toolkit, too. It’s a welcome addition.
Why? Because it does a job many have been doing for years with multiple mouse clicks or VBA routines – de-duplicating data to give a ‘unique’ list of values.
It’s particularly useful for creating dropdown menus from large datasets. In the video, I explain how to use UNIQUE to quickly create a unique value list in Excel to drive a dropdown menu.
Now, ideally, I would like to be able to insert the UNIQUE formula directly into the data validation dialogue box; the fact this isn’t possible (why?) necessitates using a supporting worksheet.
02:48 FILTER
Of all Excel’s dynamic array formulae, FILTER was the one I was looking forward to most. Why? Because over a decade of teaching and training people to use Excel, I’ve understood one thing: people spend a huge amount of time filtering data.
My experience, however has been somewhat mixed. The basic function – to list data according to cell entries which act as ‘filters’ - is certainly there. But there are some drawbacks: first, it takes a long time to put a filter formula together, particularly if using multiple filters. Second, once multiple filters are set up, it’s difficult to get Excel to ‘ignore’ one of them – it seems all filters have to be active for the formula to function. Finally, it’s not easy to analyse the FILTER formula output without using fixed ranges in formulae – the # notation which allows you to reference a dynamic range does not seem to work with FILTER.
So, for me, FILTER has high potential. It’s certainly useful for displaying filtered values for one criteria. If you’re looking to analyse that data, however, you should probably reach for a pivot table …
04:30 SORT / SORTBY
This formula, once again, automates a task that requires a lot of ‘clicking around’ in Excel: sorting data. So, it’s a helpful addition. For me, its main role is as a companion to UNIQUE – if you are going to use dynamic array formulae to create dropdown menus, for example, you may as well use two as much as one!
05:58 SEQUENCE
I find SEQUENCE a bit obscure because it doesn’t have a clear time-saving function. It simple logic, however, means it’s a good way to understand Excel dynamic arrays for the first time. There’s obvious the application of inserting ‘serial numbers’ to cells too – though ROW() and COLUMN() do that, albeit without the dynamic array part! Perhaps one to experiment with initially but one you can safely ignore in the longer term.
07:38 RANDARRAY
This is an impressive niche option. It’s curious it was included in the first release of dynamic array formula, since only stochastic modellers use random numbers in Excel. A notable difference compared to FILTER is the # reference works with RANDARRAY meaning you can, for example, aggregate an array of random numbers. That might not sound impressive – but the download file enclosed with this video, created by channel viewer David Cooper, certainly is. If you’re doing simulation modelling, this is an important formula.
09:49 VERDICT: WHAT ROLE SHOULD DYNAMIC ARRAY FORMULAE PLAY IN YOUR EXCEL PRACTICE?
The benefits of the formulae are clear. But, are they ‘game-changing’? I’m not sure. Yes, the formulae can be useful for automating ‘single step’ operations such as sorting data. But, If you want to take things further, for example to analyse data in a dynamic array, things get more difficult, and the awkward workarounds make other options (such as pivot tables) more attractive.
It begs the question, if dynamic array formulae won’t take my Excel to the next level, what might? Well, I would aim for a change in mindset. Away from quick solutions, Google searches and ‘game-changing’ social media content; towards long-term, incremental improvement built on an understanding of fundamentals – which never change in Excel. This is the way to make data skills and Excel impact your career - and your life. If you would like to learn Excel differently with me and a like-minded group of individuals, check out our unique member community.
📺Learn Excel Formulae And Functions In 10 Minutes
https://tinyurl.com/4ceekebs
📺Applying Excel Simulation Modelling
https://tinyurl.com/49sn4mnu
📺Learn Excel VBA In 30 30-Minute Sessions (30 For 30!)
https://tinyurl.com/yyt8h7d4
📺Our Member Community
https://tinyurl.com/2p8wy5dr
Watch video Excel Dynamic Arrays 3 Years On - Have They 'Changed The Game'? online without registration, duration hours minute second in high quality. This video was added by user Tiger Spreadsheet Solutions 21 April 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,585 once and liked it 53 people.