Hello and welcome to this video tutorial on 8 things that you need to know about
dynamic array formulas in Excel.
So first of all, we need to know how do you write an array formula. So I'm going
to use a demonstration with the IF function, a very popular Excel function.
And in column C here, I simply want to display the words yes or no depending
whether the due date has passed or not. So typically you would write the IF
function like this, we would ask if the value of the cell to the left,
B2, was less than today's date. And I'll put in the TODAY function to make that side
of things relative. Comma. Then I'm gonna display the word yes, comma,
otherwise, I'll display the word no. And we can run that formula and pull that
function to the bottom. And it works absolutely fine. But let's look at the
dynamic array formula approach. If I delete those cells. This time when we
write the IF function, we don't just refer to that single cell. Not just B2
I select the range of cells B2 to B13. I then test if that is less than the
TODAY function. I then put in my yes and I then put in my no. And this isn't
necessarily new to Excel. You've always been able to refer to an
array. But previously you'd have to use your Control + Shift + Enter at the end of
it, because it's an array formula. We don't have to worry about that anymore.
We can reference an array in a normal function, like IF, or LEFT, or VLOOKUP.
Whereas previously, we reference that single-cell. If I press ENTER on this formula. It
spills to the bottom. The bottom being the last cell of the array. And I said B2
to B13, so it stops at 13.
When using dynamic array formulas, we need to understand the spill range, and
the Spill error. And with that formula that I just wrote, at the moment I am in
cell C3, which is not the cell that I wrote it in. That was cell C2. And you can
see in the formula bar, that the formula, I can see it, but it's in grey. It does
not reside in that cell. Whereas if I move to the original cell C2. Now it's
editable. I can click in there, I can make changes just like I would any formula.
But if I escape that. And if I try to change the one in C3. So I click in the
formula bar, and I can see there's nothing there. And if I was to enter
something. Like no. Then I get the Spill error. Because the formula can't
spill into the area because it's blocked by some content. So this is great because
you know if anything is interfering with that range. And it will not let anybody
edit the formulas in any other cell apart from the original cell that it was
written in. Now it was easy to fix if I delete the thing that's blocking it it
then spills into that array again and you notice that the spill range has a
blue border around it so it's nice and visual which is very helpful when you're
working on spreadsheets that other people have built
when you're working with dynamic array formulas you will need to know how to
reference that array from other formulas so for example in cell e2 I want to know
how many are due so I just want to count how many yes is there but I'm not going
to count the range c2 to c-13 because by entering it that way it's not dynamic I
want to be able to count this when this list grows or shrinks in size so in cell
e2 I am going to use the count if function but when it Crump's for the
range I'm going to reference the first cell c2 and then put in a hash tag or a
pound sign and you can see visually with the blue that references the entire
spill range so I'm saying from c2 to the end of the spill range the end of the
array and as that array changes dynamically then so with my countif
function I can now just finish this off by putting a comma writing yes in my
quotes close bracket and run it and I get my answer of 8
now a big reason to use dynamic arrays is that their dynamic so let's look at
add into what we've done so far if I added a new delivery ID in row 14 let's
say I had one four five five and let me enter a date this in the past but a
second of March twenty twenty and it does not dynamically increase in size it
hasn't accepted this new road and that's because of the way I reference the array
I said let's have a look B two to be thirteen that's what I said
I could have selected a larger range and then that would have been included but I
didn't now it's not wrong what I've done but by taking that approach I haven't
really got that dynamism so ideally we would be using tables right now because
tables are amazing and they would work in that way so let's put this data in
the table so here we are if I select a cell in that range ctrl T to create the
table and I click OK I'm then going up to the table design
tab I'm changing the style I want to clear the style and I'll remove those
filters for a moment so it's now in a table and let me change the name of the
table two days jus and let's go again so I'll type my if function in here
wonderful the logical test that we reference the range beautiful table
reference there less than today comma yes comma no but oh dear we have a spill
range because big thing to note here guys you cannot use dynamic array
formulas in tables and that's a big thing if you use tables as much as I do
it really changes the way that you work
now dynamic arrays a brilliant for reference in data in tables because
tables are already dynamic dynamic arrays are dynamic it's a wonderful
relationship but you can't put a dynamic rate in the table so if I just select
arranging my table I grab the corner where I can resize it and just exclude
column C from it so now the table is only columns a MB you can see the ants
are spilling outside of the range here image to come and delete the data of God
in column C and let's do it again if function reference the table range using
that structured reference less than today in goes yes in goes no excellent
so now you can see it spill to the end of the table which is what we wanted so
it's referencing a table but it's not within the table and if I add something
at the bottom one four five five immediately you can see it grow because
the table was going to grow automatically with a new data the
dynamic array form is using the table therefore it grows let me finish this
off and wonderful and they're now countif function which I've sent delete
since deleted up here would also immediately update let me just quickly
write that one back in referencing with the hash tag the esses and now
everything's going to be fully dynamic I can add one more in here let's do the
second of March again just to quickly get this going and you can see it's all
updating both column C and column E fully dynamic
with the dynamic array formulas we now have six new functions and these
functions are really useful they are sought sort by sequence filter unique
and Rand array and arguably the creamer crop is the filter function that one is
incredibly useful so I'm going to do a quick example of that now with the same
data that we were working with previously it's already in a table and
that table is called deliveries so rather than displaying the text yes or
no and then being able to filter or net or counts on it with the filter function
we can dynamically produce the list of deliveries that are at a date for us to
chase up so in cell e2 I can begin with the filter function and the array to
filter and to return for me is going to be the entire table so I'm just going to
click the corner of the table here in the top left of it to select the entire
table I can input in a comma so what ones to include well that's the ones
that I do so I'm going to reference the due dates column and ask if it's less
than the today function there is my criteria comma what to do if it's empty
what if nothing is found I'll just display an empty string close
bracket enter and that has returned all of the do deliveries now I haven't done
any formatting here so we still need to format our cells in the same way that we
do already with our formulas so I can just select that range and apply other
formatting now it's quite common to select a few cells more than necessary
because the big thing about using dynamic array
formulas is that their dynamic but at the moment my formatting is not dynamic
it stops at row 11 so it's not uncommon to see people going a little bit past
their point and formatting in advance just like you probably already do with
the formulas that you write
now another big thing to know about these new functions and our dynamic
arrays is that we cannot use them directly inside Excel features so for
example I want a unique drop-down list from the list of countries in column a
where we can see at the moment that I've got two from the UK got three from
France but that needs to be unique and there is a new dynamic array function
called unique which if I just quickly demonstrate in cell c2 I can run the
unique function tell it to use this array which is already formatted as a
table and I won't worry about the other two questions I'm going to close bracket
and press ENTER and that simply delivers a unique list dynamically so if another
country was to appear in this list such as India then it's automatically picked
up I could even take that a step further and throw one of the other new functions
around it called sort and that list is now automatically sorted as well so even
if a new country came in such as China not only is it automatically making it
unique is automatically sorting it ready for our drop-down list so let's imagine
in column a we want that drop-down list now what we can't do is where I can't
take a copy of this formula select my range from my data validation list come
in to data validation choose list paste it in there and click OK it's not going
to like those new functions being used in data validation now this is a big
shame because I use formulas in my conditional formatting rules and my data
validation rules quite a lot and you can see plenty of my videos on YouTube doing
that so that's a shame however we can reference the list that's
been generating column C so it's not the end of the world but it's a shame we
can't use them directly inside of the Excel features just like earlier in this
video I can reference the first one that's the only one that a formula
resides in then put in that hashtag where that pound sign to reference the
spilled array so when I click OK in each of these sales we have all of the
countries so I have a unique and dynamic drop-down list we're more added to the
list it just picks them up and resourced them pretty awesome it was never that
easy before these dynamic array formulas came out but a big thing to know you
can't use the formulas directly inside Excel features you can just use them in
a sale and then reference that range
the final thing to know about dynamic array formulas is their compatibility
between other versions of Excel such as 2016 and 2019 and those using 365 and
having the dynamic arrays so let's look at some of the formulas we wrote in this
video in another version so this is the if function that we wrote as an array
opened in Excel 2016 that doesn't have the dynamic arrays and you can see in
the formula bar that it has the curly braces around the formula indicating
that it's an array so it still works but it's functioning as an old-school array
formula would however we can also see that in cell e2 the count if function is
not working we have that hashname error because it does not recognize the
spilled reference that we did with the hashtag in there we can also say with
the filter function that was written that we have some hashname errors here
also because Excel 2016 is not going to recognize these new dynamic array
functions you can see in the formula bar what it looks like and the mention of
filter up there with the curly braces for the array but it is not working so
no surprise there that the new functions won't work in other versions finally
here we have an array formula written in Excel 2016
but opened in 365 where we have the dynamic arrays so somebody has written
the if function as a normal array formula but this is how it looks when
opened in 365 and it uses that act symbol to indicate implicit intersection
which is always what Excel does but we never really saw that symbol before so
it may come as a surprise when you start seeing that in formulas in 365 you may
recognize that at symbol when writing formulas based on data in tables so
there are some things to bear in mind if you're going to collaborate across
different versions of desktop Excel there and you're using dynamic arrays so
eight things that I think people need to know about dynamic arrays I hope you
found this video useful please check out some of our other videos on our youtube
channel and come check us out at computer Garg our calm
you