9 Exciting NEW Excel Functions for Shaping Arrays - Incredible!

Microsoft recently released 11 new Excel functions for shaping arrays to 365 users. Now, I already covered VStack and HStack, which was super popular. And in this video, I'm going to cover the remaining nine new functions. And at the end of the video, I'll use them to rearrange data that used to require a ninja level function wrangling. But now, it's super easy. The first two functions are toCol and toRow. They return an array in a single column or row and they're handy for combining data that spans multiple columns or multiple rows. You can see the syntax takes the array or range. Then we have an optional argument that enables us to ignore blanks and or errors. And lastly, we can specify whether to arrange the data in order of column or row, which is the default. We'll look at toCol first. Here we have a list of postcodes and their respective suburbs. I can extract the suburbs into a single column using toCol. All I need to do is select the range, close parentheses, and it spills the results. But notice there are zeros where there are blanks for postcodes that have less than four suburbs. We can handle those blanks using the ignore argument. So again, I'll select the range. And this time, I'm going to ignore blanks by entering one. And now we have a more succinct list. The last argument for toCol is whether we scan by column or row. Now by default, it scans by row. But if we scan by column, and let's ignore blanks again, and we'll do true for column, you can see it changes the order that the data is stacked in. The toRow function is the same, except it puts the data into a row. Let's have a look. Just select the data here. And again, when we have blanks, it enters a zero. So we can handle that by using the second argument and specifying one to ignore blanks. And just like the toCol, toRow also has the scan by column argument, which is going to change the order that the data is stacked in. The wrap functions enable you to wrap a row or column of values after a specified number of elements. The first argument is the vector or reference to wrap. Then we have the wrapCount, which is the maximum number of values in each column or row. And lastly, we can specify a value in any empty cells. Starting with wrapCols, the vector is this list of postcodes. The wrapCount, I'm going to specify three. That is, after three rows, I want to wrap the results into the next column. And I'll close parentheses and let's take a look. Now notice we have an NA where I have a blank result. So I only have five postcodes. So the last postcode returns an error. I can handle that with the pad argument. So let's do wrapCols again. And we're going to wrap it into two columns, so three rows each. And we're just going to pad with two double quotes, which is a blank. And now we don't see the hash NA. As you'd expect, wrapRows is similar. So let's select the range of postcodes. This time, I need to specify how many values in each row before wrapping. I want two columns of data, so I'm going to specify two here. And we can pad with to hide errors. So let's put blanks in there. And there's my results. The next two functions enable you to extract a specified number of contiguous rows or columns from the start or end of an array. The first argument is the array. Then we have the number of rows to take or drop. And optionally, the number of columns to take or drop. We can specify negative values to start from the end of an array. We'll start with take. And this is my data here. Let's say I just want to get the first row. That is the list of postcodes. So it's dropped the second two or just taken the first, if you like. Let's say we just wanted the last postcode. So we're going to take, select the range, and I want to take the first row and minus one for the last column in that first row. And that returns the last postcode. Let's say we want the first three columns. So again, I'm going to take and I want all rows. So I'm going to skip that argument. And that's going to return all rows. And I want the first three columns. So even though the rows argument is required, by skipping it, Excel will return all rows in the array. The drop function is the opposite of the take function. So let's select the array. And here, let's say I want to drop the first row. Just return the suburbs. Or perhaps I want to drop the last two columns. So let's select all the data and then I'm going to skip the rows. I want to return all rows, but drop the last two columns. So minus two to work from the right-hand side. Or we can drop the last row and first two columns. So again, select the array. And this time I want to drop the last row and the first two columns. So you can see, depending on whether you use a positive or a negative value, drop is going to work from either the left or the right. The choose functions enable you to extract data from the specified columns or rows in an array. The first argument is the array. Then we have the first row or column number, followed by the next and so on. It's similar to the take function, except you can rearrange the order of rows or columns and repeat rows or columns. And you'll see why that might be useful later in this video. Starting with choose columns. And I'll select all of the data. And let's say I just want the first column. Now I can rearrange the order of the columns. So let's say I want the third column and then the second column. So I'm going to select all the data and then I want the third column and then the second. And now I've got this column and then this column. I can use negative column references to start with the last column and then the first column as well. So let's take a look at that. I'll select the array. And this time I want minus one. So starting with the last column. And then I want the first column. Now you can also specify the column numbers in an array. So in the formula bar, for example, I could surround this in curly braces. And that's now an array of my column numbers. Returns the same results. It's just another way to enter the column numbers that you want. And it's handy to know if you're using other functions nested in here that return arrays. The choose rows function is similar. Let's take a look. So here I'm going to select the data and then I can get the first row. Or let's look at a variation. Here I can get the third row and then the second row. So I rearrange the order of them. And like choose cols, I can use negative values. So let's take a look at that. Select my array and then here I want to get the second last row. And just like with choose cols, you can also specify the row numbers in an array. So let's say we want minus one, the last row and then the first row. Close my curly brace and now you can see it's rearranged the order. The expand function expands or pads an array to a specified number of rows or columns. To be honest, I can't think of the need for this function, but I'm sure it's useful for something. The arguments are the array, then the number of rows in the expanded array and the number of columns in the expanded array. I can pad any empty cells with some text or a number. So let's say I want this range of cells, but I want it four rows high and three columns wide. And rather than having an error in the empty cells, I'm just going to pad with TBA. Close parentheses and there you go. So there you have nine of the new shaping array functions. Individually, they're amazing, but team them up and you can create some super useful formulas. Let's take a look. Let's say I want to pivot the list of postcodes and the corresponding suburbs here so that the postcode is in a column. We can use two row to select all the data. Close parentheses and now we have one long row of data. But if we wrap that in wrap cols and then say that we want to wrap after five rows of data. Let's close parentheses and now we have the data pivoted. Now we have postcodes in a column and then two columns of suburbs. But what if I want the data in a tabular layout with a column for the postcodes and then a column for the suburbs? For this, I need to get the postcodes and suburbs into separate arrays and I can use to col to do that. I'm just going to to col the postcodes. Let's close parentheses. There they are there. And I can to col the suburbs. Let's close parentheses on that. But notice that I actually need the postcodes repeated again because I've got two rows of suburbs. And remember, I can do that with choose rows. So to col and then let's insert choose rows in here. And I want to repeat the first row twice. So I just enter it twice. Close my parentheses and now we have the postcodes listed twice. So now I have my data in a tabular layout, but I've used two formulas to get there. Let's put them all into one formula. So let me copy this formula and I'll delete it. And then we're going to use HStack, which we looked at a little while ago, a couple of weeks ago now. And we're going to stack the suburbs in with the postcodes. Press enter. Now I have one formula that converts my data into a proper tabular layout. But what if we have a table of postcodes and suburbs with blanks? Well, I can use the same approach. This time I need to repeat the first row four times because I have four rows of suburb data. So we're going to HStack and then to col and then choose rows. We're selecting the postcodes first and I need these four times. So it's the first row four times. Close choose rows. Close to col. And then I need to to col the suburbs. Close parentheses on to col. Close parentheses on HStack. Now let's see what it gives me. So I've got some blanks in there because I've got some postcodes with only two or three suburbs. So let's handle those. We can do that by wrapping the whole thing in filter. And then what do we want to filter on? We want to filter on the suburbs which are in this formula here where they are not blank. Close parentheses on filter. Press enter. And there's our postcodes and suburbs without any blanks. So you can see the real power of these functions is unleashed when you use them together. I hope you have fun with these new functions. You can download the Excel file for this lesson from the link here. And if you like this video, please give it a thumbs up and subscribe to our channel for more. And why not share it with your friends who might also find it useful. Thanks for watching.