Introduction to Dynamic Arrays

Quick reference

• 00:04 In this video, we're going to look at a game changing change that Microsoft
• 00:09 actually made to the way Excel interacts with multiple cells in a reference.
• 00:14 This is called Dynamic Arrays and
• 00:16 it was introduced to Office 365 after Excel 2019 was already out in market.
• 00:22 What that means is that this feature will only work in Excel 2021 or
• 00:26 higher or in an Office 365 subscription.
• 00:30 Now let's just demonstrate exactly what this is all about.
• 00:32 So if I come over here to cell F7 and I say equals and I pick up cell A7 and
• 00:37 hit Enter, we get the value back that was in A7, in other words alcohol.
• 00:42 So this hopefully makes a lot of sense.
• 00:45 But here's the thing, here's where the difference in behavior happens between
• 00:50 a dynamic array versus a non-dynamic array version.
• 00:53 If I select multiple cells here, in Excel 2019 and earlier, the response
• 00:58 that I would get from Excel is it would give me the cell in the top left and
• 01:02 it would return that, so I'd get alcohol showing up in F7 and that would be it.
• 01:08 But in a dynamic array enabled Excel, when we hit Enter,
• 01:11 what we get is we get all of those values that we've selected.
• 01:14 Boom, they all drop in there.
• 01:16 Whether you've referred to an individual column or an entire range of data,
• 01:19 you'll get all of those multiple rows and columns coming back to the cell.
• 01:23 So this is really interesting and yet, you might look at it going well, but
• 01:27 I've already got the data, how would I use this?
• 01:29 Let me show you where this starts to get really, really powerful because it's not
• 01:34 just reconstituting data that makes this useful, it's when you combine
• 01:38 it with the functions that Microsoft introduced at the same time.
• 01:41 So in this case, I'm going to use the unique function and
• 01:44 we're going to feed the unique function and array.
• 01:46 What is an array is any two-dimensional range of data whether it has one or
• 01:51 more columns and or rows.
• 01:53 In this case, I'm going to go and pick up the group column from my sales
• 01:58 table which has about 45 rows in it, and it's a single column.
• 02:02 I'm going to close the parenthesis, and what you'll see is that we get back just
• 02:06 the two unique values that are in that column, alcohol and food, so
• 02:10 that's pretty cool.
• 02:11 Now let's do another one, let's go and take a look at this one here,
• 02:14 we're going to go and see equals unique.
• 02:16 And I'm going to grab the sales category, there's a lot more of these.
• 02:21 And when I hit Enter, you'll see that we get, well,
• 02:24 we get a new error message that's been introduced to Excel as well.
• 02:28 This is called a spill error.
• 02:30 What is it about?
• 02:31 Well, let's just go and select the cell and see what's going on.
• 02:34 Notice the dotted lines around this block.
• 02:37 This is the required amount of cells in order to output all of the unique values
• 02:42 into the grid and the problem is, there's something in the middle.
• 02:46 We can even see this on the little yellow message here tells me that my
• 02:50 spill range isn't blank.
• 02:52 And if I need help, maybe this is 1000 rows off the side of the sheet here,
• 02:56 I can click select obstructing cell, and it will take me right to it.
• 03:00 So the challenge here is that the data can't spill because Excel knows that it
• 03:03 can't overwrite something you've deliberately put there but in this case,
• 03:06 this is just garbage.
• 03:07 So I'm going to press the Delete key and boom, look at that,
• 03:11 here come all of my unique sales categories, pretty cool.
• 03:14 Let's go and look at another one.
• 03:15 We are going to go and look at unique and I'm going to pick up my sales years.
• 03:20 I'm going to close the parenthesis on this and hit Enter, and
• 03:23 it comes back with 2011, 2012, 2013.
• 03:26 So far so good, except I actually want to see this in reverse order,
• 03:30 I want 2013 at the top.
• 03:32 So can I do that?
• 03:34 And the answer is, yes, I can, providing that I use another formula.
• 03:37 So in this one here,
• 03:38 what's happened is Microsoft has introduced these sort function.
• 03:42 So I'm going to sort my array, which is the unique sales years and
• 03:46 then it asks me for my sort index, so this is basically column number.
• 03:50 But while we can put in a one or
• 03:51 we could leave this blank because there's only one column.
• 03:54 When I moved to the next parameter,
• 03:56 what we actually have here is I've got the ability to sort in ascending or
• 04:00 descending order, so I'm going to go with descending, close my parenthesis and
• 04:04 when I hit Enter, you can see it's 2013, 2012, 2011.
• 04:08 Now so far, everything I've shown you is all about arrays but
• 04:13 it's not really dynamic, where does that come in?
• 04:16 Well, these are all formula based and all tied to the calc engine, so if I go and
• 04:20 change C7 to 2014 which doesn't exist in this table already, look at that output,
• 04:25 there's 2014, 2013, 2012, 2011, so that's pretty amazing.
• 04:29 It automatically returns what's happening in the cap chain.
• 04:33 Let me go and press Ctrl Z, I'm going to set that back, and
• 04:35 I'm going to show you where we can actually use this to our advantage.
• 04:38 So I'm going to copy this formula, I'm going to come up here, and
• 04:42 I'm going to paste it as follows, and you can see this spills down.
• 04:47 Okay, that's great, but I actually wanted to go across.
• 04:51 We have another function for this one here and
• 04:53 I'm just going to wrap it around my sword unique here, this one is called transpose.
• 04:58 And what transpose does is it actually rotates your data 90 degrees.
• 05:01 So if you've got a column, it'll turn it into a row.
• 05:03 If you got a row, it'll turn it into a column.
• 05:06 We're going to go to the end, close my parenthesis, and
• 05:09 what you can see now is I've got 2013, 2012, 2011.
• 05:13 Now there's one more thing to know about dynamic arrays, it's super important,
• 05:17 check this out.
• 05:17 We're going to use this, we're going to say equals sum if we're going to pick up
• 05:22 the years column from my table, and we're going to set the criteria that I
• 05:27 want the sum equal to case six, so in this case, 2013.
• 05:31 And then we're going to choose the sum range,
• 05:33 which is equal to my sales column, right?
• 05:36 So a nice sum F for all 2013 sales and we get back $1.1 million, fantastic.
• 05:43 But I really want to point this at the array, so
• 05:46 it automatically spills out as yours are added or subtracted.
• 05:49 To do that, in our cell reference here where we have case six,
• 05:53 what we need to do is we need to add a special character,
• 05:56 we add the hash character after the reference.
• 05:58 And you'll notice immediately that the entire range is picked up now, so this is
• 06:03 pretty cool because when we now hit Enter because we've referred to a dynamic range
• 06:08 it has multiple values, it will then create an array that has multiple results.
• 06:12 And there we go, we've now got our sum F built for 2013,
• 06:17 2012, and 2011, and even better than that.
• 06:21 Again, remember, this is all dynamic.
• 06:23 So if I make this 2014 and hit Enter, boom, check that out.
• 06:28 Here's the 2014 sales of 22,576 and my other ones have all been adjusted and
• 06:34 spilled in order to show everything that's there.
• 06:37 Dynamic arrays are a game changing thing in Excel and are really worth exploring.

Lesson notes are only available for subscribers.