In this Excel tutorial, we will be continuing on with our Dynamic Arrays tutorial and addressing some of the older functions in Excel that allow you to take advantage of Dynamic Arrays including Text functions, the VStack function, and the Choosecols function.
Transcription
0:00 - 0:39: Hi everyone, in this video we’re going to be moving onto part 2 of our series on Dynamic Array Functions on Excel. So continuing on with our series on Dynamic Arrays in Excel and functions that can take advantage of dynamic arrays. If you didn't see the previous video, you may want to take a look at it and introduce yourself to unique function, sort function, and filter function options that allow you to create dynamic arrays. But now we're going to do is really explore some of the long-existing functions in Excel that can now take advantage of the concept of dynamic arrays.
0:40 - 1:18: We're going to start off with a couple of the text functions, so the first one we're going to look at is the left function.So if I wanted to for example grab the the leftmost, say 5 letters of the customer name, and I don't want to do this in the table itself, I wanted to be in another area of the sheet or maybe even on a different tab. As we know I can take the left function simply point to the text, in this case the point to the name. And let's say 5 comma five and nothing revolutionary about this. And of course, if I wanted to do that for all of the items in this table, I could simply copy this down and there you go.
1:19 - 1:48: But now what happens is if I go and add another record, let's just add another record to this table.In order to get the left function to extend to the newly added row, I'm going to have to be manually key in the formula or again copy it down manually. If I had another 10 rows every time I change, I'd have to do something. Similarly, if one of these rows were to be removed one of these rows would actually lose the reference as well, particularly if it was on a different sheet.Now, there's a much better way of doing this, so let's take a look at that.
1:49 - 2:32: So we'll start off exactly the same as before. We'll start using the LEFT function, but now instead of Simply put pointing to this field with this cell, which is effectively B2, part of the table, we will point to the entire array. In this case it is the entire column B of this table, and then will put in again comma and five.And now what we get is we get a spilled dynamic array result set that includes all of the records or all of the items in the spreadsheet in the table and it is dynamic, it will update dynamically. So for example if I were to manually change east of Chicago to, you know, West of.Chicago.And you'll notice that immediately our array has updated.
2:33 - 3:13: But more interestingly, because that would happen with the other formula as well, if I add a new record at the bottom here. So let's call it: Maracana Stadium....You'll notice that it immediately added that new record. Similarly, if this was linked to a dynamic data source, this was linked to, let's say a SQL Server table, maybe from your ERP system, and you would right click and refresh this. If it resulted in more rows or fewer rows, it doesn't matter, you're array would basically faithfully represent in the right sequence, the first.Five characters of the customer name for the object of the entire table.
3:14 - 4:01: Now, this same concept applies to pretty much all of the other topical text and string functions that you may be accustomed to that allow you to manipulate the contents of a cell or a column in terms of text. So I'm just going to show you. For example, you'll notice that some of the customer names or company names are in all caps, most of them are not. Most of them are a mix of.The capital letters and lowercase letters which is called proper case, but there are a few of them that are for the upper case. If we wanted to get to a point where we could change those and maybe put them in a separate table or copy the table where none of them are in all caps or maybe all of them are in all caps, we could do something like this. We could say equals proper which is another text functions.And once again, just select the entire column instead of one individual cell and have all of this.
4:02 - 4:48: Similarly, we could change that, and let's say we will. We’ll make them all uppercase.Do the same thing and now they're all uppercase. And of course we could copy this and paste values into somewhere else to permanently save these as improper case. And yes, before you start wondering. In case you start wondering, you can mix and match again these formula with some of the other dynamic array formulas. So for example.We could potentially sort these alphabetically if we wanted to. We could simply .Edit the formula.And wrap the sort.Function around it. There was only one column, so the sort index is going to be column one, and now we have uppercase customer names sorted alphabetically.
4:50 - 5:35: Next, let's take a look at an interesting function called V Stack. The stack allows us to literally stack different ranges from different sources on the spreadsheet, or even from different workbooks, even into what looks like a single table or a single array of data. Now.I'm going to use this opportunity to illustrate the power of using tables, one of the oldest functions in Excel. It's been around forever. And these two.These two pieces of data are not actually tables. They're simply data in a tabular format, but I’ve not converted these ranges into tables so, it's just basically a range. I’m gonna first use V stack with these and I'm going to show you some of the limitations and explain why it would be better to actually use tables for these dynamic arrays.
5:36 - 6:43: So let's use the V stack function.I'm going to assume that this table was for our Canadian sales and it came out of one ERP system. And this table is for our U.S. sales and it came out of a completely different system, which is why they're in two different spreadsheets or two different areas of the spreadsheet.... for convenience of the tutorial they’re on one tab. But normally they'll probably be on different tabs or even different workbooks. I want to combine them so I just get a sense of what's going on overall. We'll do some further analysis. I do V stack equals V stack and I simply point to the first range or array and in this case I'm simply going to highlight that range and then I point to the second one.And now what its gonna do, it's going to give me a spilled array that stacks these on top of each other. If, for example, I were to change, let's say, the amount of that first one from $900 to $59, You will see that the array immediately updates itself. And you know, even though it's in an array you can't format this column to get a much better looking result set. And again if I change it back to 900 it will update accordingly.
6:44 - 8:02: So this is actually very useful. But there are two shortcomings here. The first shortcoming is very very simple for us to deal with and that is that there are no headers. Well, you can always.Simply add on another range, in this case in the beginning of the V stack formula, to specify the column headings from one of our two sets of tables. So I'm going to do that. I'm simply going to go to the beginning of this formula and I'm going to pick.That range, put a comma.And now we actually have column headings as well. So that's great. What's not so great is if, for example, I were to add another record, I'll just maybe just to keep things simple and quick, I'll copy that and I'll just add it in as another record here because I specifically selected a range that only includes these rows.It doesn't recognize that I've added anything and it will not update my dynamic array, and my arrays not really all that dynamic. So we can do better. How can we do better or what we do? And this is one of the advantages of using a table. There are some disadvantages, but generally speaking when you have this form of data in Excel.You're adding a table. Turning it into a table allows for a lot more flexibility. So I'm gonna go ahead and I'm going to insert table, group, done, control shift T. My table does have headers and now we've turned it into a table and I'm going to do the same thing here.
8:03 - 9:01: Now, I can't just expect this formula to automatically figure out what I've done. So what we'll do is we'll simply delete this formula altogether and now we'll enter a new version of the formula where we will say V stack.Start off with the header rows.So just the headers.Then use the actual table content of the First table and then stack the table contents of the table on the second table.And now this is always completely and appropriately dynamically rearrange itself. So if I were to add a record, Again, ill just copy this to be safer. If I add a record here it immediately updates my dynamic array.So hopefully that gives you a little bit more insight into why you may want to use tables when you're working with dynamic arrays and dynamic formula, Unless there is another compelling reason not to turn your data into a table.
9:02 - 10:02: Finally, for this tutorial, we're going to look at a function called Choosecols. And yes you probably guessed it, it allows us to create a dynamic array that uses specific columns of the source table or the source data array.So in this example, we'd like to create a dynamic array that doesn't include all of the columns in this table, but just selected ones. This is often useful when you have a table of data that has dozens or even sometimes hundreds of columns, and you'd like to create a dynamically updated extract of just the key columns that you're particularly interested in trying to analyze. So although we only have six columns here. Let's just imagine that we had 20 or 30 columns and from that 20 or 30 column array, we wanted to create a separate array that will dynamically update whenever this data was updated or refreshed from the database that would simply show us the customer name, the country and let’s say the sales value. So Choosecols is a great way of doing that. You create the equals Choosecols formula and you can put it anywhere.
10:03 - 11:33: It doesn't really matter where on the sheet and I select the array. Now in this case I can either I could simply select the table data, I would not get headings, or I can select the entire table.And just make sure that it is in table and not in a range, so it says table or. And then I simply select the columns numerically. So invoices is the first column is column one, customer name is column two, etc. So let's say we want column two, we want column five, and we want column six. That's all we want.We now have a dynamic array we can resize so we can actually see this better. And again, we can format the money column. You can store it somewhere. We can fiddle around with the format of the header if we wanted to, but it's still going to dynamically update when we change things. So again, if we were to change. Let's just use Mark’s company and change that from 1200 to 6547. You'll notice that the dynamic array updates as well. And of course, if we were to delete any of the records, then this dynamic array would update as well. In fact, let's just do that. Let's go ahead and entirely delete row #4. And you'll notice that the array updated and removed that record as well.I will point out that similarly there is also a choose rows column that does the same. Basic idea except allows you to select rows from a range as opposed to columns, but works in an identical fashion.
11:34 - 12:02: So I hope you find at least some of these advanced dynamic array function functionalities of Excel to be useful in your day-to-day Excel analysis. There will be a further set of tutorials that delve a little bit more into some of the dynamic areas and also to some of the more advanced and hidden features in Excel that employees here at Blue Link have found it useful in analyzing and dissecting our business data.
