Locked lesson.

Upgrade

About this lesson

An introduction to the new Dynamic Arrays capability added to Microsoft 365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Intro to DAs.xlsx
26.1 KB Intro to DAs - Complete.xlsx
26.7 KB

Quick reference

Introduction to Dynamic Arrays

Introducing Dynamic Arrays and how they can expand Excel’s capabilities.

When to use

When you need to return more than one item to a block of cells and need it to dynamically update in future.

Instructions

Availability

  • Dynamic arrays were released to Microsoft 365 AFTER Excel 2019 was released
  • This means that you must have a version of Excel newer than Excel 2019 to use them

Creating a Dynamic Array

  • Write a formula that refers to more than one cell  (i.e. =A5:A10 )
  • Write a formula that refers to a table column/row or the entire table

#SPILL Errors

  • If an array’s output is “blocked” it will return a #SPILL! Error
  • Select the cell to see the intended spill range
  • Select the flyout menu to locate the blocking cell
  • Clear the blocking cell in order to allow the array to spill into the worksheet

New Functions

  • =UNIQUE(array) will generate a list of unique values from the array input
  • =SORT(array,[sort_index],[sort_order],[by_col])
    • Will sort the array in ascending order by default
    • Can be used to sort by other arrays, in descending order or even by columns/rows
  • =TRANSPOSE(array) will rotate the array output by 90 degrees

Referring to Array outputs in other formulas

  • To refer to a Dynamic Array’s output:
    • Set your formula to refer to the address of the first cell, and
    • Add a # character at the end
    • i.e =A5# will refer to the dynamic array that spills from A5
Login to download
  • 00:04 We're now going to look at something very cool and very modern.
  • 00:07 This is called Dynamic Arrays.
  • 00:09 And these are exciting because they change the way that you can actually work
  • 00:13 with Excel.
  • 00:13 But before we jump right into this, I need to just call out whether or
  • 00:17 not you'll even have these.
  • 00:19 Because the reality is that Dynamic Arrays were actually added to
  • 00:24 Office 365 after Excel 2019 was already in market.
  • 00:28 And what that means is that if you're running Excel 2019 or
  • 00:32 earlier, you will not be able to use what I'm about to show you.
  • 00:36 On the other hand, if you're on a version that's newer than Excel 2019,
  • 00:41 or if you're working with Office 365, you will have this functionality.
  • 00:46 So what is this all about?
  • 00:47 Anyway, Dynamic Arrays.
  • 00:48 Well, here's how this works in old Excel, if I were to go and say equals and
  • 00:52 actually pick up a range of cells like this.
  • 00:55 You would hit Enter, and
  • 00:56 excel would run through this process called implicit intersection.
  • 00:59 And what it would do is it would say, well, you've selected one cell,
  • 01:04 but you said that you wanted to return four cells.
  • 01:08 So I'll give you the top cell of that list and
  • 01:09 it would return alcohol and that would be it.
  • 01:10 But today, something different happens.
  • 01:12 With Dynamic Arrays, when we hit Enter, we get back all four values,
  • 01:17 and it actually spills into the cells below.
  • 01:20 Now, that's an array, but why is it dynamic?
  • 01:24 You'll see that in a little bit.
  • 01:26 What I'm going to do right now is I'm just going to delete this.
  • 01:28 And I'm going to show you how we can actually use these to our advantage.
  • 01:31 And to do that, I'm actually going to make this easy by just layering on a table.
  • 01:35 So we have a table over here, it is an official Excel table.
  • 01:38 And we can see that by going to the Table tools, Design Tab, and here it is,
  • 01:43 it's called Sales.
  • 01:44 So what I'm going to do is I'm going to extract all of the groups from the sales
  • 01:49 table into let's build array.
  • 01:51 To do this, I'm going to say equals, I'll type in Sales,
  • 01:55 the name of the table, square brace Group,
  • 01:58 which is the name of the column I want and close the square brace.
  • 02:02 And when I hit Enter,
  • 02:03 you'll notice that it actually brings back every single value for the entire table.
  • 02:09 That's cool, it's interesting.
  • 02:11 But is it useful?
  • 02:13 It would actually be way more useful to me, if I could actually extract
  • 02:18 the unique values from that column, just alcohol and food.
  • 02:22 I could use that to drive say a validation list or a bunch of other things.
  • 02:26 So what I'm going to do is I'm going to go back to the actual array itself.
  • 02:28 And this is a tricky little thing you want to watch out for.
  • 02:31 Notice how the formula here is grayed out and this is in the spilled area.
  • 02:35 If I go and click on it, it wants me to write another formula.
  • 02:37 That's not cool.
  • 02:38 We're going to go back to the original one.
  • 02:40 And I'm going to wrap this in a brand new function called UNIQUE.
  • 02:46 And what UNIQUE does is it says give me an array and
  • 02:49 I will return you the UNIQUE values.
  • 02:51 So I now have just alcohol and food.
  • 02:53 And if somebody came in here and said, hey, you know what I'm going to
  • 02:57 add another one for I don't know, let's go and just type in whatever and hit Enter.
  • 03:01 Notice it is dynamic, so it actually spills out for us, which is really neat.
  • 03:06 Let me just control Z, undo that and bring it back.
  • 03:09 So that's cool.
  • 03:10 Let's go and get the unique categories list.
  • 03:13 So we know how to do this now, we can say equals UNIQUE, and
  • 03:17 we're going to go pick up the Sales table.
  • 03:19 And this time, I'll bring up the Category.
  • 03:22 I'll close the square brace to close the Category column, and
  • 03:26 close the parenthesis for my UNIQUE value, and I'll hit Enter.
  • 03:29 And what you'll see is that I get this #SPILL error.
  • 03:33 What is going on here?
  • 03:34 Well, if I click on the actual #SPILL itself, you'll notice that it
  • 03:38 actually puts a dotted line around the area that is trying to spill too.
  • 03:43 There's also a little flyout menu widget here, and it says, hey,
  • 03:47 one of the cells in our spill range isn't blank.
  • 03:50 We can actually go Select the abstracting cell and press the delete key.
  • 03:55 And at that point, everything will go and spill for us.
  • 03:58 So this is something that's really important to know when you're working with
  • 04:02 Dynamic Arrays, is that if the spill range is not empty, at that point,
  • 04:05 it will come back and give you that #SPILL error.
  • 04:08 So that's kind of cool.
  • 04:10 Let's go and grab Years now.
  • 04:12 And we're going to go equals UNIQUE again, and we're going to go with our Sales.
  • 04:16 And we'll choose Years, close the square braces on the call, close the square or
  • 04:21 the round parenthesis as well.
  • 04:23 And we now get our years, which are helpfully showing us 2011, 2012, 2013.
  • 04:28 But the problem that I've got here is that I actually want to turn this into
  • 04:31 the reverse order.
  • 04:32 I want 2013, 2012, 2011.
  • 04:35 Well, as it happens, there's another new formula for us to do that with as well.
  • 04:39 It's called SORT.
  • 04:41 So what I'm going to do is I'm going to come back up here,
  • 04:44 I'm going to take my UNIQUE values, I'm going to wrap them in the SORT function.
  • 04:48 Now SORT has a few different parameters.
  • 04:51 I'm going to skip the SORT index,
  • 04:53 that would actually allow me to sort it by something else like maybe food or alcohol,
  • 04:56 but that's not really going to be relevant in this case.
  • 04:59 So I'm just going to put in a comma to skip that one altogether and
  • 05:02 leave it with a default.
  • 05:03 What you'll notice when I get to SORT order though,
  • 05:06 is I now have the ability to declare a -1 to put it in descending order.
  • 05:11 So I can close the parenthesis on this, and that's going to switch it up to 2013,
  • 05:16 2012, 2011.
  • 05:17 What I'd really like to do now is actually build myself a nice little table that has
  • 05:22 2013, 2012, 2011 across the top, and then get the total sales by year.
  • 05:27 Well, in order to do that, I need these guys to show up across.
  • 05:31 There's another function we can use for this.
  • 05:33 Now, this isn't actually new, but
  • 05:36 it actually works really well with our Dynamic Arrays.
  • 05:39 It's called TRANSPOSE.
  • 05:41 And what I'd like to do now is I'd like to go and say, let's click on 2013.
  • 05:46 Now, it's not really showing me but
  • 05:47 it's actually given me just this one cell reference.
  • 05:49 And the challenge that I have here is that when I go and hit Enter,
  • 05:52 we can see it up in the top, TRANSPOSE(H7).
  • 05:54 When I hit Enter, you'll notice it just gives me 2013, but I want 2012 in 2011.
  • 06:00 So I'm going to show you what happens when we actually go and
  • 06:03 select the entire spill range for the array.
  • 06:06 Notice how we get H7#.
  • 06:08 That hash is a very special character that says give
  • 06:12 me the contents of the spilled array.
  • 06:14 And when I hit Enter, it now goes and spills out properly.
  • 06:18 At this point, all that's really left to do is actually write a SUMIF
  • 06:23 against the original table to say sum it if the year equals that.
  • 06:28 So we'll go with that equals SUMIF, and
  • 06:31 it says what range would you like to actually go and look in?
  • 06:35 So we'll look at sales, Years.
  • 06:38 The criteria that we're actually looking for is K6, and
  • 06:43 the sum range will be Sales, Sales, close that parenthesis.
  • 06:49 And away we go.
  • 06:50 We'll hit Enter. And it comes back with 1.1 million but
  • 06:54 it didn't fill in for the other cells in this table.
  • 06:57 Why not?
  • 06:58 And the answer is, we didn't target the Dynamic Array.
  • 07:01 So what I'm going to do is I'm going to come up to K6 here, I'm
  • 07:05 going to put a nice little # afterwards, that refers to the entire array.
  • 07:09 And now because the input is an array series,
  • 07:12 it will now spill into all of those individual cells.
  • 07:15 And here's where it gets really cool.
  • 07:17 Let's say that we just change one of these guys right here 2014.
  • 07:21 And notice, as soon as I do that, it updates everything live and we've now got
  • 07:26 a nice new set of data, that's spilling appropriately for what we need.
  • 07:30 This is some pretty awesome power that's going on here.

Lesson notes are only available for subscribers.

Gift this course