New Array Manipulation Functions

Locked lesson.

Upgrade

Quick reference

New Array Manipulation Functions (Microsoft 365, Excel 2021 only)

Use the new Excel functions, VSTACK and HSTACK to manipulate and shape arrays. 

When to use

We use VSTACK and HSTACK whenever we want to stack data arrays horizontally or vertically. 

Instructions

VSTACK and HSTACK are two new functions available to users of Excel for Microsoft 365 that help us stack data horizontally and vertically. 

VSTACK

VSTACK stands for vertical stack. We can use it to stack arrays vertically. 

In this example, we need to stack just the countries from each region on top of each other to create a global sales list. 

We can use VSTACK to do this. 

The only arguments required are the arrays we want to stack. 

Remember to hold down CTRL when selecting multiple arrays. 

The VSTACK formula will stack the arrays on top of each other to produce one list. VSTACK is a dynamic array formula that spills its result to other cells. It's worth noting that dynamic array formulas can only be edited in the cell where the formula was originally typed. 

We can combine VSTACK with other formulas to produce a more meaningful result. For example, maybe we want to sort the list of countries in alphabetical order (A-Z). 

HSTACK

HSTACK is similar to VSTACK but it stacks arrays horizontally. 

This is the result. 

Use VSTACK Across Worksheets

We can use the VSTACK function to stack data from different worksheets. 

In this example, we have sales data for North America, Asia, and Europe stored in three separate worksheets. We need to combine and stack the data on the Global Sales worksheet. 

The data is NOT consistent across all worksheets. Some worksheets have more cities listed than others. 

  • Go to the Global_Sales worksheet. 
  • Click in cell B4. 

We type the VSTACK formula in the usual way and when we specify our arrays to stack, we just need to ensure we are clicked on the correct worksheet. 

Notice the VSTACK formula include the worksheet name and the cell range selected. 

This is the result. 

This method works well if we just have a few worksheets of data to stack. However, if we have lots of worksheets it's more efficient to use 3D referencing. 

3D Referencing allows us to select the worksheets first, then select the cell range once to use in the formula. The same cell range will be used across all worksheets in the formula. 

As our arrays are different lengths on each worksheet, we need to select a cell range that is large enough to accommodate the longest array. 

  • Go to the Global_Sales worksheet. 
  • Click in cell B4. 
  • Type =VSTACK(
  • Click on the first worksheet, NA_Sales.
  • Hold down SHIFT and click on the last worksheet, Europe_Sales. 

The formula selects both worksheets and all worksheets in between. 

  • Select the longest cell range, for us that is on the NA_Sales worksheet. 

Notice the zeros in some of the cells. This is because we are selecting the cell range B4:C18 on every worksheet and as the lists are different lengths, some cells don't contain any values. 

We can fix this by using the FILTER function to filter out the zeros. 

  • Edit the formula. 
  • Add the FILTER function to the front of the formula. 
  • Click on the array argument and press CTRL+C to copy the VSTACK part of the formula. 

  • Press CTRL+V to paste the copied formula. 
  • Change the last cell reference from C18 to B18. 

  • Add <>"" to the end of the formula. 

The zeros will now be filtered out of the stacked list.

Login to download
  • 00:04 In the final lesson of this section, we're going to continue to delve
  • 00:08 into a couple of the brand-new array manipulation functions in Excel.
  • 00:13 Now, I don't want to spend a lot of time looking at all of them,
  • 00:18 because they are only available to users of Excel for Microsoft 365.
  • 00:23 But there are two in particular which I think could be pretty useful,
  • 00:26 and we're going to take a look at those in this lesson.
  • 00:29 Now the two array manipulation functions that I'm talking about are VSTACK and
  • 00:34 HSTACK.
  • 00:35 And what these allow you to do is basically vertically or
  • 00:39 horizontally stacked data on top of each other.
  • 00:42 Now what I have on the worksheet just here is some data for
  • 00:46 sales across different regions in the world.
  • 00:49 So maybe this is a company that has global offices.
  • 00:52 Now what I want to do is instead of having these broken down into their different
  • 00:57 regions, maybe I just want one big long-list of the cities and
  • 01:01 their sales totals.
  • 01:02 So what I can do here is use VSTACK to combine all of these together.
  • 01:07 And VSTACK, as you might imagine, stands for Vertical Stack.
  • 01:12 And this is such a simple formula.
  • 01:14 If we type in VSTACK, the only arguments that we have are the arrays of the cell
  • 01:19 ranges that we want to stack on top of each other.
  • 01:22 So our first array that we want to stack is this array just here,
  • 01:28 comma, this is our second array, comma,
  • 01:32 third array, and then our final array at the bottom.
  • 01:37 Let's close the bracket, hit Enter.
  • 01:40 And now everything is stacked together.
  • 01:41 We just need to apply some cell formatting.
  • 01:44 Let's just put comma formatting in there like so.
  • 01:47 And we have everything stacked nicely.
  • 01:50 Now, as I mentioned, because this is a dynamic array function,
  • 01:54 we can combine it with other functions.
  • 01:56 So maybe I want to sort this list into alphabetical order, so
  • 01:59 that we have the city beginning with A at the top going all the way down to Z.
  • 02:04 Now, one thing to remember with these dynamic array functions is that you can
  • 02:08 only edit them in the cell that you type them.
  • 02:11 So if I click in the Tokyo cell, I can edit the formula.
  • 02:15 If I click somewhere else, you can see it's grayed out.
  • 02:18 So that's just something to note, you can only edit them in that original cell.
  • 02:23 So what I'm going to do here, is jump up to the formula bar and
  • 02:28 I'm just going to add the sort function onto the front there.
  • 02:32 Array is generated by the VSTACK formula.
  • 02:35 Now I could utilize some of these other sort arguments if I wanted to sort by
  • 02:40 a different column.
  • 02:41 But I want to sort by the first column, and
  • 02:44 I want to sort A to Z, which is the default.
  • 02:46 So let's close the bracket, we're not going to add anything here, hit Enter.
  • 02:51 And now I have all of those cities sorted alphabetically.
  • 02:55 So that is the most simple use of VSTACK and
  • 02:57 how you can combine it with other functions.
  • 03:00 Now let's take a quick look at HSTACK.
  • 03:02 As you would imagine, this works in pretty much the same way,
  • 03:06 except we're stacking our data horizontally as opposed to vertically.
  • 03:10 So maybe I want these stacked in a different layout.
  • 03:13 Maybe I want Asia and then the sales, Europe and then the sales, so on and
  • 03:17 so forth.
  • 03:17 We're going to do pretty much the same thing.
  • 03:20 We're going to choose HSTACK, and we just have our arrays that we want to stack.
  • 03:24 So let's go comma, select the next one,
  • 03:28 comma, the next one, and then the final array.
  • 03:32 Close the bracket, hit Enter, and we now have our data stacked horizontally.
  • 03:37 So now that we understand the basics of VSTAC and HSTACK,
  • 03:40 let's take a look at something a little bit more complex.
  • 03:43 Now maybe I have my sales data on different worksheets for the different
  • 03:47 regions, and I've only included three regions here, so you get the idea.
  • 03:51 So on the first tab here, we have all of the North America sales.
  • 03:55 So we have the cities and the sales.
  • 03:57 On the next tab, we have all of the sales data for Asia.
  • 04:01 And then on the third tab, we have the sales data for Europe.
  • 04:04 And what I effectively want to do is,
  • 04:06 I want to combine all of this data onto one global sales spreadsheet.
  • 04:11 So I want to do a similar thing.
  • 04:12 I want to stack this data on top of each other.
  • 04:16 However, this scenario is slightly different because the length of our
  • 04:21 data isn't consistent across the worksheets.
  • 04:24 So on the North America worksheet,
  • 04:26 we have quite a few cities there going down to row 18.
  • 04:29 If we go to Asia, we only go down to row 16 here.
  • 04:33 Europe is even shorter, we only go down to row 9.
  • 04:36 Now, this is going to throw up a bit of an issue.
  • 04:39 So let's take a look at it.
  • 04:40 I'm going to click in global sales, and we're going to type in equals VSTACK.
  • 04:45 Now I could go across to the first worksheet and select the first array,
  • 04:50 like so, comma, move to the second one, and select the second array,
  • 04:55 comma, move to the third one and select the third array.
  • 04:59 I could do it like that, close the bracket and hit Enter.
  • 05:03 And I pretty much get exactly what I want.
  • 05:05 Now, that's fine if you just have a few tabs like I have here.
  • 05:09 But what about if you are combining data from 10, 20, 30 different tabs?
  • 05:14 A much quicker way of doing this would be to employ 3D referencing.
  • 05:18 So, let me show you how you would do this.
  • 05:20 I'm going to delete out formula, and we're going to do VSTACK again, but
  • 05:24 this time we're going to select the worksheets first of all.
  • 05:28 So, I'm going to click on the first worksheet, NA sales North America sales,
  • 05:33 then I'm going to hold down shift and click on the last worksheet.
  • 05:37 So it's basically picking up all of the worksheets in between my selections,
  • 05:42 and you can see that up in the formula bar.
  • 05:45 It says, NA sales, that's the name of the tab, colon Europe sales, so
  • 05:49 it's going to select everything in between.
  • 05:52 Now I have to specify the cell range that I want to stack.
  • 05:56 Now, this is where the problem occurs because all of our cell ranges
  • 06:00 are different lengths.
  • 06:01 So if I choose the longest cell range, so
  • 06:04 that would be the North America sales, I'm going to select this one just here.
  • 06:09 So we're selecting cells B4 to C18 on every single worksheet.
  • 06:15 Let's close the bracket and hit Enter and check out what we get.
  • 06:18 We get some zeros in here, and
  • 06:20 that is because the data isn't the exact same length on all of the worksheets.
  • 06:26 So if we have a scenario like this where we've stacked our data on top of each
  • 06:30 other, but we have these weird zeros, we can then effectively combine it with
  • 06:34 the filter function to get rid of those zeros.
  • 06:37 So let's click in the first cell again, I'm working up in the formula bar and
  • 06:42 we're going to add filter on the front here.
  • 06:44 Array is being generated by the VSTACK formula.
  • 06:48 And what we're going to do here is, we're going to Ctrl+C to copy this,
  • 06:53 because the next argument of filter is what we want to include.
  • 06:57 So we're going to Ctrl+V to paste this formula in,
  • 07:01 we want to include the results of the VSTACK.
  • 07:05 But we're going to change this last cell reference from C18 to B18.
  • 07:11 So effectively, we're only looking in that cities column.
  • 07:16 We want to include all of the cities that are not equal to zero, let's hit Enter.
  • 07:23 And now when we scroll down,
  • 07:25 it's basically filtered out all of those zeros for us.
  • 07:28 So those are different ways that you can combine data.
  • 07:31 And the good thing about this is that everything is dynamic.
  • 07:35 So if we were to go back to the Asia sales and add another city on the bottom here.
  • 07:40 When we jump back to our global sale sheet we should find that that has
  • 07:45 automatically updated to include that new entry, which I can see that it has.
  • 07:51 So VSTACK and HSTACK, really powerful, really useful,
  • 07:55 particularly when you combine them with other dynamic functions.

Lesson notes are only available for subscribers.

Gift this course