Sometimes Excel dynamic arrays start to win

I reckon Excel's newer class of 'dynamic array' functions have a few emerging rock stars amongst them (If you want to find out more about how Excel's dynamic array functions work, see here: Excel's dynamic arrays change the world of financial modelling). In niche applications (maybe not every day) dynamic arrays can rise to the specific challenges they're designed for nice and elegantly.

The dynamic array functions can sometimes start to come into their own at the moment you're hit with one of those challenges which sounds-really-easy-to-describe but is not-always-really-easy-to-pull-off using Excel:

• "Could you just get rid of the duplicates?". That's the new Unique. Otherwise it's pretty jolly hard to do if you want that repeatable, with continuously-refreshing data sets, using old-school Excel functions (instead of finding yourself forced to use the manual "remove duplicates" feature you see under "Data" > "Data Tools" > "Remove Duplicates" - which could be fine if it's a once-in-a-blue-moon job you need done)
• "Could you get rid of the blank rows?". That's the new Filter
• "Could you pull out the top 10 customers?". That's Unique, combined with Sortby.

There definitely are occasions, say in response to particular sounds-simple-but-it-really-isn't challenges of the past, where these emerging rock stars start to strut their stuff. Their sometimes-elegance compares favourably to the combination of functions or Excel-manoeuvres you might have needed to employ to get the same result in older traditional Excel.

Today's example where dynamic arrays start winning

Here's another sounds-simple challenge of the moment: "Could you get rid of the graph categories that have no values?" (imagine column E's values are all about to be stripped out).

Of course the simplest thing of all is just to delete the part of the data set on the times it contains no values (so delete column E above). But we're imagining a model here that in some runs will show zero, in other runs will show values. Sometimes you want that category displaying, sometimes you don't. Deleting out the unused categories after a run of the model becomes a bit dangerous, because the categories might need to be reinstated in the next run.

Another really simple solution would see you grouping up the categories you don't want displayed (select the column you want to remove from the graph and then go "Data" > "Outline" . "Group"). Once they are hidden the categories you want removed magically disappear from the graph.

I reckon the grouping solution is OK. It's a bit of a manual process (that doesn't bother me) but it's clear when the categories are on/ off. To reinstate the missing category you just expand the group again. I'm fine with all that. Where this one becomes more challenging is where you've got a bunch of graphs on one tab (entirely conceivable) and grouping up only applies to a row or a column at a time. So for the 'grouping' plan to work, all graphs might have to be on different tabs (painful) or, with lots of graphs plotting different things, all graphs would need exactly the same layout so you're always grouping the same columns or the same rows. Oh, and this one comes with the (could be irritating when you e.g. want to copy and paste things into a board paper regularly) side-effect that the plot size of the graph changes depending on what you group up.

Plotting the outputs of a dynamic array through a graph

Today, with lots of different graphs running down the page, let's imagine we want a 'proper' technical solution that sees unused graph categories disappear. It sounds simple but previously it just wasn't. If you start searching online you'll find solutions that have been around for a while. Those solutions involve creating names in Excel, with those names formulas, with those formulas adapting and eliminating say the zero-value categories. The solution sees the formula filtering out the zero values, and then the name (for the adjusted data set) gets plotted by the graph. If it sounds complicated and fiddly that's because it is complicated and fiddly.

Imagine my delight the other day though when this not-every-day plot-particular-values-only request came in. And I started messing around trying to remind myself of how to set up my graph so only particular categories display, and while messing around online I saw the word "filter" somewhere which lead me to try this. Instead of running a name through my graph so that what's plotted adapts, what about pointing my graph towards the results of a dynamic array (because I know those results adapt)? Lo and behold: it really works! And quite elegantly. The plot adapts, and the only results that arrive are those that arrive courtesy of the dynamic array filter function.

Simple is always good

Simple, of course, is great. And, in the case of getting rid of unwanted graph categories, simple qualifies as just deleting out the parts of the data set that you don't want. Or hiding the parts of the data set you don't want. Those are straight-forward solutions that might get the job done nicely. But if, for whatever reason, you need something a bit more techie than that (e.g. because you need to re-run the model regularly, and you've got lots of different graphs on one page so hiding rows/ columns becomes impractical) you can quickly put the "Filter" dynamic array function to good use. At that point "Filter" really starts to feel like it's living up to its promise as an emerging little rock star that deserves an occasional moment in the spotlight. It really does.