This video walks you through multiple advanced Excel functions that any business owner can use to increase efficiencies when analyzing data.
Transcription
0:00 – 0:33 Welcome and thank you for joining today’s training session, my name is Danielle and I'm the marketing communications coordinator here at Blue Link. So, I want to first start off by letting you all know that we have everyone on mute. So, if you have any questions, please feel free to ask them in the chat and I'll be manning that, and we'll address all the questions at the end of the session. If you can't hear if you have any issues with the audio or viewing our screens, please also let me know in the chat and I'll try to help you out. And then finally, I'll just let you all know that we are recording this session and we'll be sending it out later on so that you can view it or send it to someone who may be of interest.
0:34 – 0:47 So yeah, I hope everyone is doing well and I hope you'll learn something useful today in our session. So, to get started, I'll introduce Mark Canes who most of you know as the President of Blue Link and he will be the one conducting this training. So, Mark, that's all you.
0:48 – 1:24 Thanks Danielle, and welcome everyone. I see some familiar names and some new names. So good to meet those of you have never met before and nice to at least be on the same session with some of my old friends. So, I'm hoping that you know most of you, if not all of you, get at least something of value out of this. You may find that at the end of this session, you already knew everything that I'm going to show you. In which case, that's wonderful and we'll invite you to do our next training session. But hopefully there's something of interest. I'm probably not going to take more than about 1/2 hour to go through what I'm going to show you and then as Danielle says we can try and field any questions.
1:25 – 2:07 But the main purpose of this is to just share with you some of the functions and features of Excel that have been added over the last two or three years or two or three releases, some of them are specific just to Office 365, which is now renamed Microsoft 365 and which I guess I only learned about them, let's say over the last year or so, maybe nine months. And when I shared some of these with some of my colleagues here at Blue Link, I was quite surprised to find that with the exception of one of the people here, Darren, for those of you know him, most of those were not aware of these features or functions either and the recommendation came from a lot of the Blue Link employees that we should share this with our customers as well.
2:08 – 2:40 So that's the context of what we're doing here. Now, most of these functions, not everything we’re going to look at today, but most of all we can look at today revolves around manipulating or extracting or sorting or finding out information from structured data. And the vehicle that we're using to show you these things is simply a live linked Excel query or sorry, a live linked power query where the Excel. So, most of what we're going to do is going to be based on manipulating the data you see here.
2:41 – 2:56 The data you see here is live linked sales history information at a line-item level from a demo data file. The Blue Link demo data file the demo data file that we use for our sales demos that will support experimenting with when we try and test something out.
2:57 – 3:21 So there is this is basically invoice detail information that's been extracted. For those of you who are familiar with power query or with live linked Excel spreadsheets, I can go ahead and right click on this any stage and refreshing it's putting up to date data from the database. I filtered it down to only the transactions of the last maybe three months because I didn't want to have too many transactions.
3:22 – 3:48 If you are not familiar with how to use power query to link data, there is a video that was actually linked in the original invitation to this meeting that you can go through that explains and unfortunately you have my boring voice droning on about it and that video, but it will tell you all you need to know in terms of how to link to Blue Link data in general from Excel, and very specifically this exact data set that we're working with right now.
3:49 – 4:00 I have also narrowed down the number of columns though the data set that the video tells you link to. The book sales view has many more columns, but to just keep this more manageable, I've narrowed it down to just a handful of columns.
4:01 – 4:35 So, what we're going to do is we're going to be talking about some functions. We're going to be showing you how to use some functions that allow you to do things like sorting, filtering, finding unique trends that we're going to look at, a few value added things around aggregating, and all of these are things that I have personally found useful in working with Blue Link data in one or other context, either as somebody who's working on Blue Links own books or own accounting, preparing stuff for our year end, or in many cases working with one or two Blue Link customers to help them analyze and make sense of their data.
4:36 – 5:08 So, without further ado, what I'm going to do is I'm going to. So let me just show you that there is a lot of data here. I think we've got three or 400 rows of information with a lot of different invoice dates, customers, products, quantities, prices, etc. Well, even more than I thought. OK, there's a lot. Umm. There's 530 something of these records and we're going to be trying to fiddle around with this data as best we can.
5:09 – 5:26 Now just in terms of basic Excel skills, everybody knows that if we wanted to sort this whole table by a, let's say the under the invoice number, we can easily do that by just saying sort smallest to largest. So, you guys already know that that's obvious and that's a given
5:27 - 6:02 But sometimes it's useful to actually have our data in one context in Excel like this where this is sorted initially by invoice number in case you only want the current sort order is it's invoice number, this column over here column B. Um, but it's nice to be able to look at views and slices that are either sorted in a different way or filtered for different criteria and so on, without messing with the original source data. And that is where the first family of formula that we're going to talk about come from. I tend to refer to them as the Spill formulae. S-P-I-L-L and you'll see why I do that in a moment.
6:03 – 6:38 And the, the formula in specifically are SORT, SORT BY, FILTER, and UNIQUE. So, these are the first few that we're going to look at. So, I'm going to go to a separate tab and in this tab, I'm going to restate, I'm going to grab this view of the same data. Maybe not all of the data, maybe some of the data, but I'm going to grab a view of some of this data and I'm going to sort it in a different sequence. In this case, what we're going to do is we're going to sort it in descending, descending order of the order date, descending order date.
6:39 – 7:02 So, we’ll do that first. So, in this tab, I'm going to use the formula called SORT. I'd also point out that on the tabs where it says MS365, according to the information that I have, the formula is only available in Office 365 versions of Excel, not the old sort of office 2016 or whatever. Now I haven't verified that. That's just based on information that I have from there on my help.
7:03 – 7:30 So, you know, take that, take that with a grain of salt to some degree. Mark sorry, sorry to interrupt. Can you just zoom in a little bit? We just got one request to get it a little closer. OK sure. So, I'll just do that. Hopefully that guys hold OK, and I'll zoom in and out as we want to see more, less context, more context. You know, let me go back to this screen and also zoom this one as well so this tab so that we can see more clearly.
7:31 – 8:15 So, I'm going to go ahead and just run the SORT function. So, the SORT function. Is literally equals sort. Open your brackets. Now the first you have to you have to give it one, at least one argument, which is where's the data? The array is the data, the two-dimensional block of data that you want to sort. You then have some optional arguments. So, let's just do it very simply. At first let's just tell if we want to sort several of the columns of that book sales query. So, what I'll do here is I'll go, and I'll select, let's say the order date, invoice date, product code columns. Just to keep it simple we'll just sort those and it's going to by default sort it by order date.
8:16 – 8:45 So, what it's done now is it's actually pulled that data out and it's given me three columns. Now you will notice that these two columns look a little bit odd, although they are date columns over here. This formula does not automatically format them according to the source data. So, if I was going to keep this, which I'm not because I've got a better idea, I would simply format these columns as dates. But we'll do that in a moment. But notice something interesting about this little block of data that it's pulled out here.
8:46 – 9:22 When I click on that first top left column, which is where I put that SORT formula, you'll see this looks pretty normal. This looks like a formula here so I unfortunately I'm not able to zoom the title bar, so hopefully you can see what's typed in here. But it's it looks normal, but if I click on any other part of this table, it's grayed out. It's a light Gray and you cannot edit it because the formula only exists in this cell, but it is spilling. Hence my use of the two words SPILL formulae. It is spilling its data as far down as it needs to spill the data.
9:23 – 10:06 OK, now we can do a better job of using this formula than just that simple example. Now what we're going to do is we're going a little bit more sophisticated. We're going to say I want to SORT the entire table, the entire book sales table, and my little trick for doing that is to hover over the top left until the little arrow. Again, I don't know how well you can see that I have no way of zooming it, but the little cell point changes from across to a diagonal arrow pointing towards sales, or when I click on that it selects the entire table. But not the headers. That's just a quick way of selecting the entire table. Doesn't apply only to this scenario. Any table you can do this to select the contents of a table without the headers.
10:07 – 10:56 So I have said equals, I sort this array. So here I have the array booked sales all view, now type comma and the next argument, which pops up nice and conveniently over here is the sort index. The sort index is the column number in my array by which I wish to sort. So, if I want to sort this by amount, it's this column over here. So, I literally have to count 123456789. I want it to be column 9. So I’ll type in nine there and now I can decide whether I want it to be ascending or descending. This is an optional argument. You can leave it out in which crystal by default be ascending, or you can type in a one or double click on the little blue thing that I'm hovering over. Or you can type in minus one to sort it in descending order.
10:57 – 11:19 I'm going to make it descending order and then I'm simply going to end my brackets there and now what it's done is it's taken this whole set of data. It has created a sorted version of it over here, but it is sorting it in descending order of amount now because I am going to keep this and I'm going to do a little bit more work with a little bit later, but I'm going to keep this. I'm going to format my date columns.
11:20 – 11:52 My date columns already formatted because I took the whole table, not just columns. I'm going to format my amount column to look more like a currency amount just so that we can see that it is in descending order. OK, now the important thing to one of the important things to realize about the SORT function is if any of this data changes. For example, if I refresh the data and there's now a new invoice in the blue link data that's bigger than the others or, or, it needs to be sorted into different area then that will actually automatically update over here.
11:53 – 12:20 So I will prove that to you by going into any one of, So, we'll notice that the biggest number we have here is 26325. I will do something that you should never do with live linked data, but I will go and just grab one of the numbers here and I'll just put a ridiculously high number. So, I put a really high number here, and when I go back to my sort function, it's too broad for that column. You'll notice that it's automatically updated this SORT function.
12:21 – 12:44 So, imagine if you wanted to have a summary of your sales for a particular period in multiple tabs of the spreadsheet where you pull the data onto first tab, and then you're sorting it by sales value, sorting it by product, you're sorting it by whatever criteria you like on multiple different tabs, so that when you update the data, all of your tabs are immediately updated at the same time, but you're going to have one data source.
12: 45 – 13:06 That's kind of the beginning. We're scratching the surface, but that's the beginning of how some of these formulas that we're talking about today could potentially be used. So, I'm going to go back and I'm going to undo that because I don't really want to mess with my data. So, let's just duno that and there we go. Now we're back to where we were.
13:07 – 13:39 OK, now maybe I want to do something a little bit different here. Maybe I'm not interested. I still want to do the SORT order, but I don't necessarily want to see all of these columns. I'd like to only see part of the columns which we've already seen an example of, but I want to sort them by a different number, so I just want to see which products and which invoices I sold for the most, but I don't necessarily want to see the quantity or the dollar value. Not a problem. Let's delete that and let's go and add in a SORT-by function.
13:40 – 14:12 So now we're into the second function, but it's just a variation of the first one. So, this one says tell me what it is you want to sort. The first thing is the array. I will select the. Actually, let's just keep it real simple, let's just make it the invoice date, product code and product description. That's what I want to sort but what do I want to sort it by? What is the sort or what is the sort sequence? Well, we want to sort it by the amount even though it's not going to be in the result set.
14:13 – 14:35 So, make that the second argument, the sort and then again I have the choice of ascending order, which is the default for descending and again I'll do descending just to be consistent. And now what we get is data that does not include the um, make the columns nice, we have data that does not include the amount, but it is sorted in descending order of amount.
14:36 – 15:07 So again, let's just say that you wanted to get a list of your sales. You just wanted to get the products and the quantities, but you wanted to sort them by product category or by vendor. But you don't want to see the vendor in your category in your result set. The sort by function works beautifully for that. It's a really good tool from that point of view. So again, the difference between sort and sort by I think you're hopefully you understand. Sort by allows you to sort data using a column in the original source data that you're not showing in the result set at all.
15:08 – 15:51 OK, so I'm going to do one other thing very quickly to show you one of the consequences of not understanding how this formula works. So, if I were to try and do the following. If I wer to take something here and say I'm going to make a note to myself. Teach users how to Sort. OK, good enough. I'm just putting some text here. Now I'm going to go over here and I'm going to say equals sort and I'm going to just do the simple thing just to go back to what we did before. I'm just going to sort the entire table into that array.
15:52 – 16:21 OK, watch what happens. I get what's called a spill error. What that basically means is there is something else occupying one or more of the cells into which this range would normally spill over that the result set. And you can see it actually shows you as I click on that the borders of where the result set would be, a scroll all the way down. It's going to go through all of that information. Easy solution, just delete that and now we'll get our results set.
16:22 – 16:47 So that is what I'm going to talk about at the moment on sort and sort by, but a little bit later, actually very soon, we're going to revisit sort in a slightly different context. Danielle, have you received any questions at this stage on sort or sort by or should we move on to unique, I mean filter, sorry. No questions yet so, I think we can move on. OK, great.
16:48 – 17:39 Filter is just a variation on sorted basically is the same kind of idea. You have your original, you have your original data set over here and now you want to reflect a slice of it. But this one is one that I think is much more practically useful because what it allows you to do, let's just say that I wanted to only look at sales where the amount was greater than or smaller than a certain amount. I could go and hopefully everybody knows this. You could go and say I want this to be greater than and put an amount here and do that kind of stuff. But then if I want to change it, I have to come back here and do this and it's not necessarily visible that, well, the visibility is that if I do a filter over here, let's say greater than. You know 100. I can see that it's filtered if I look very carefully because there's a little dot over here and if I hover over it says greater than 100, but it's not very easy to change and I don't know.
17:40 - 18:10 Also what it does is it when it filters what it does is it hides the lines that don't meet the criteria. So, if you look carefully at the row numbers you see 8, 29, 31, 38, it's just hiding row numbers, so I'm not a big fan of using the filter if I'm just trying to get a permanent slice of certain things. What I like doing is using the filter formula. So, I've created a little bit of a structure and a framework here. So Full disclosure, I already decided in advance what results I want to get, but the filter function doesn't require this.
18:11 – 19:06 So just to prove this, I'm initially going to go down to let's say here and I'm going to take well actually let me zoom in a little bit so you guys can see that it's bigger and I'll maybe just go over here it's fine. Initially all I'm going to do is I'm going to create a filter section a filter statement that filters the data in our book sales and only returns some of the rows. We had 530 something rows we're only going to return a small number of them. We’re going to filter that data set for so first of all, we're going to do again, is show the array and again this time I'm going to pull the entire set. And I'm going to include only those where the. OK, where the product code is 22790, so I'm going to say we're product code. Equals 22790.
19:07 – 19:38 Oops, it looks like I did something wrong. Um. OK. Maybe I'll just grab. OK, I'll just do it. More simply, I'll do that. Where let’s do the quantity is Grade is less than. Well, let's make it greater than 10.
19:39 – 20:15 So now it's giving me just a subset of the records in the book sales where the quantity is greater than 10. So straight away, I think you can probably see that how this could potentially be useful. But the issue of course is that if in order for me to know what I'm looking at, I have to look at the formula in order to see that it's greater than 10, that that's what I'm looking at, that something is greater than 10. So for purposes of making this really useful and this is really an example of exactly what I use this for and I have both versions of this that I use for both sales and for accounts receivables.
20: 16 – 20:38 So I do a little bit of AR analysis, sorry, accounts payable, not accounts receivable, little bit of accounts payable analysis, but I'm only looking for certain amounts. For items that are over a certain amount, I will go to my pre-built structure. You'll notice I've, I'm expecting to get columns invoice date, customer name, product description, quantity and amount. So that's the data set.
20:39 – 21:13 Those are the columns from the original data set that I'm going to use and I'm going to say equals filter just that range of columns from our book sales, so from invoice date and again by the way I should have mentioned earlier, when I hover over the top of a column in a table the little plus sign turns to a downward facing point. It means I'm selecting that column, I click and then I can drag across and it selects the adjacent columns Um, but again, without the row headings, which is a really quick way of selecting the data you want without having to go all the way down and figure it out.
21:14 – 21:37 So I'm going to filter that and I'm going to filter it where the amount is greater than, but now instead of putting an actual number in the formula over here, I'm going to go back to my filter tab and I'm going to click on that item there that, that, that cell over there. So whatever value is in that cell is what's going to be used to filter the data.
21:38 – 22:09 Now this is much more useful because now what I can do is I can change that to 100. And now it’ll filter for everything that’s over 100. I'm going to keep this, so I'm going to format this column as a date column and I'm going to format this amount nicely as an actual proper number with dollars and cents and same thing I can now say, yeah, let's filter for anything that's over 2500 and it'll filter it down to only those items that are over 2500.
22:10 – 22:50 So, and keep doing this, obviously you get. Whoops, I've got to do it in the right cell though. Yeah, let's try that again let's go to there and let's make that 1200.OK, so I'll make it 250, so we get more records. Now that's…Well, I will say that the filter function itself only allows you to filter based on one criteria and when you only have one argument. So, you couldn't use this to say let's filter for items that are more than $250 and where the customer’s name equals 3D TV or whatever the case is except that you actually can. And by the way, this is not customer name, this is product code.
22:51 – 23:30 Sorry, when I was preparing this obviously had a brain cramp. That's product code. Um. Except that you can, because you can actually nest filter commands. So, you could say equals filter this filter command, and then put another argument after it based on this. I'm not going to do that because I think hopefully most of you get the idea, but that I don't think is a very common use of this particular kind of thing, because then you'd have to have all your criteria but one thing that is very useful is would be very useful for this is It's great that I've filtered these items, but maybe I also want to sort them so I can still see maybe you want to sort them in invoice date format sequence for example.
23:31 – 24:15 That's actually pretty easy because all I need to do is use my existing filter function. But wrap it in a SORT function. And if I want to sort it by the first column which is invoice date, I don't even have to give it another argument, I can just close the brackets there and now it's going to sort my filtered set in date sequence. If I did want to sort it by one of the other columns, I would have to put in the column number. So if we wanted to sort it by product description there would be column three and again whoops, I think I put too many records there. And again you could put another comment, another argument to do it in descending order, but now I've got it sorted by product description.
24:16 – 24:39 So now I have live linked filter of my all the items that are over a certain date. Sorry that are over a certain amount that I can change over here, but they're sorted by product description. So again, I think I could go on about this for a long time, but I and there's a lot of variations on this. But one thing I will tell you that doesn't work is you cannot wrap a SORT function inside a filter function.
24:40 – 25:05 So, you'll get an error message if you try and do that. So if you want to combine sort and filter, build your filter first and then wrap a sort function around the filter function. Don't try and do it the other way around, again, it is a spill function, so the same thing applies. If I type something else anywhere in the results range, I'm going to get that #spill. If I make it go away, then we get our results here again.
25:06 – 25:28 And again, it's real time. It's dynamic. Make that $800 and we immediately get an updated data set that is still sorted in the sort sequence. Yeah. OK. Danielle, any questions on that? Nope, no questions, we can keep going. OK, Hopefully I'm not too many of you to sleep as yet, but I guess we'll find out afterwards.
25:29 – 26:05 So let's look at the next function. The next function is slightly different, although it does do the same thing. It's another spill function, but the usage is a little bit different and probably in many cases even more useful. It's theUNIQUE function and what the UNIQUE function does. It allows you to return only unique values from a set of data that has more that has multiple copies of a value. So there's one really, really obvious use for this function that I'm going to show you, and that is that… here are our book sales.
26:06 – 26:34 OK. I can see that I have sold a lot of different products during this time frame, but not all the products in my database. I have at least 100 products in active products in my demo data that are not represented in these sales for the last three months. It would be good for me to know what products I have sold in the last three months. But, looking at this data doesn't really help me because man, there's a lot of duplications, right? I can't really tell just the individual SKUs that I've sold.
26:35 – 27:00 But obviously you guys already know where I'm going with this. Using the UNIQUE function I absolutely can. So, the simplest use of the UNIQUE function is to simply say unique. So, unique and then put the array which is going to be in this case just the product codes and I can just close that, those brackets and it'll give me one line for each product code.
27:01 – 27:48 So very very simple, very quick. There are some optional arguments to this, so for example if I go and I don't find this very useful, but you have the ability if you're choosing a whole range of data, sorry. If you're choosing an entire two-dimensional range with rows and columns, it will allow you to return unique columns or unique rows depending on this argument. So what I mean by that is if I go and say and say…I can say equals unique. Sorry, typos. I could say equals unique and go back to my book sales and select more than one column. I could select the entire table, but let's say I want to select that range.
27:49 – 28:19 OK that by default is giving me the data for every row where all four of those columns are the combination of all four columns are unique, so therefore, and I'll just change this to date so that you can see what I'm talking about. So, I chose four columns. It's returning every row that it's returned is unique in some way or another. I will see the same product multiple times because they'll be combined with either a different date in this column, a different date in this column, or a different customer, or any combination thereof, but it is giving me unique rows.
28:20 – 28:35 If I change the formula to use the unique columns argument, it's going to give me data only where the columns are unique, but obviously the way that sales data is every column is going to be unique, so we'll just get all the data. But that's what that second argument means.
28:36 – 29:12 So the default is false by the way, unique rows. If I change that to unique columns, I'm basically going to get the same data in this case because…all four of the columns together are unique for each record. I don’t know if I explain that well, but honestly I've never had a reason to use the columns option. I've only ever used the return unique rows and usually the purpose of this will go back to my original one is to be able to figure out. I want to get one copy of each item. So let's just slightly different way. Which customers did we sell to?
29:13 – 29:31 So now I have one record for each customer that I sold to and again I could wrap this in a SORT. If I were to sort them alphabetically, I could say equal SORT and then I would get them sorted alphabetically. So, keep that in mind that is another, Uh, well, let’s just do that quickly just to show you.
29:32 – 30:13 So now I’ve got an alphabetical list of every customer that I sold to. Just one line per customer, doesn't matter how many invoices they appear on in the original data set. OK. Now, just to sort of put another little wrinkle on this, we'll go back to my book sales. Um, if I wanted to see one line for every day on which each customer has ordered because these two columns are adjacent to each other, it's pretty easy to go and say equals unique, select the two columns that are adjacent to each other, namely the Customer Code and the Order Date.
30:14 - 30:49 And again, I could wrap this in a sort if I wanted to. I could sort it by date or whatever, doesn't really matter. But now I'm getting multiple copies of say, Atlanta bread. But it's because it's a combination of the unique days and the customer code, probably more useful with some of the columns in that book sales view that I cut off for clips of this demo, such as year and month. If you want to say give me one row, give me one line or one row in my spreadsheet per customer per year and month in which they've actually placed an order so I can then go and pivot that data and figure out patterns or even graph that data.
30:50 – 31:15 That could be very, very useful. And yes, you can use the results of this data in like a graph or whatever and as it updates your graph or update accordingly. So other than the fact that it is a spill function that you can't, you know you must go and like any other data in any of these cells, once the data is here, you can refer to it elsewhere and you can use it in other, again referring to in a chart or something like that.
31:16 – 31:55 Now I just want to show I just want to prove one thing before we move to the final few little bonus tips that we do here. I want to show you. So, let's go back, let's have a look here and maybe, Danielle, you want to note that on the UNIQUE tab we've got records that go down to row 31. On the filter tab we go down to 23. On the sort and sort by tab at the moment. Yeah, never mind. We've got a lot of data. I'm not going to change that. But what I'm going to do is I'm going to go and I'm going to change the underlying data set that is feeding the spreadsheet.
31:56 – 32:43 I am going to filter out some of this data and we'll see that it will automatically update those other tabs. So again, for those of you who don't know how to manipulate data, you should look at the video that was referenced in the invitation. I'm going to go and I'm going to transform this data. You will not necessarily see everything I'm doing because I don't believe that's part of what I've shared, but what I am doing, in case you can't see it, is I am telling it to now only, Um, include dates that are..Sorry, orders that that are from the beginning of May, so we had from the beginning of March, we're now going in fact we're going to go to the beginning of June. We're going to make it from June 1st.
32:44 – 33:15 So we will get way, way less data. Oh, wow. That's too little. And you have three records, and you try that again. Let's change that to the beginning of April. That's what it was. I knew it was an even number, OK. Alright, so we have this data, but we still have a fair amount of data. So I'm going to reload that now and you'll notice that this part over here will start updating loading data and now it will tell us there are only 128 rows in 125 rows instead of the 500 and whatever rows.
33:16 – 33:52 So let's go back and see what that's done to I won't worry about our sort and sort by, you'll notice that it is still sorted as it was before, but look at our filter. Where will we have two before Danielle, what road did we go down to? 23 sorry, which 23. OK, so now because I eliminated the whole month of data, the same data is only showing four records instead of going all the way down to row 23. The unique function may not be that different because, well it probably is, it's probably a little bit less. How much was it before? The Unique one was down to 31. OK, it was down to 31. What we can see that we are different, so we have less data here.
33:53 – 34:11 So all of these things have automatically dynamically been updated as the data is updated. So what that means is if you've hooked this to your live link, if you've hooked this to your Blue Link data, if we go and post a whole bunch of new invoices now we simply refresh the data. Right-click refresh. All of these things would immediately be up to date. And so on and so forth.
34:12 – 34:49 OK, let's have a look at the next thing on the list. So, the next thing on the list is um, I'm assuming that some of you have you have used let's just zoom again, I'm assuming some of you have used the V lookup and possibly even the H lookup, but more likely the V lookup function from time to time in Excel. If you haven't, and if you've never used it, that's great, because then this is much better than unlearning the VLOOKUP function, but one of the tools that Excel has allowed you to for I don't know more than 20 years for sure is look up the value in a table of structured information.
34:50 – 35:17 A table being structured information like this that has two or more columns side by side and find information like look up an item in one of the columns and then return data from one or the other columns, and it could be 10 columns and you could look up the item in column one. So, find TV set in column one and if there was something in your 10th column that had like a price or whatever return the price. VLOOKUP was very good for that, but it had a lot of limitations.
35:18 – 35:46 There is a formula that's been around for a couple 2-3 years, maybe more, called X lookup that honestly, I only discovered last year, actually late last year, and that it appears most people are not aware of that does exactly that, but it's much more flexible, it's much more forgiving, and you have much more control. So, I'm going to show you what the VLOOKUP formula would have looked like for trying to look up the class for a specific product in this table.
35:47 - 36:07 It's going to fail initially and those of you who are familiar with VLOOKUP will know why it fails. I will then show you why V lookup fails and then I will show you how to use XLOOKUP. And that's just so that if you are familiar with V lookup you will understand why. One of the two main reasons why X lookup is a much better function, and you should just forget about VLOOKUP and HLOOKUP and you should only use XLOOKUP.
36:08 – 36:40 And I feel very weird saying all these words VLOOKUP, XLOOKUP, HLOOKUP. But those are the formula. So, if I wanted to whatever product is in this cell, I wanted to look up the class and return it. Test that if I change the product here it automatically returns the class and I could for example build the whole product card here by looking at values in other tabs at the spreadsheet if I've got sort of inventory data, this is pulled from different data source and so is this, but I would potentially in the past have said equals V lookup.
36:41 – 37:27 I would point to the value that I want to look up Umm, which would be in that column, and I would say the, Sorry, I made a mistake. I would tell it the value that I want to look up, which is the contents of that cell, I would tell it where to look it up, look it up in that column, and which value to return. I want to return column #2, which is the class, and it isn’t going to work. OK, it's going to give me an error. And the reason it's going to give me an error is because VLOOKUP is very unforgiving. One of the ways in which it's unforgiving is that you can only look things up in the first column. Which I did do, and it has to be sorted alphabetically or numerically depending… well it's got to be alphabetically sorted basically.
37:28 – 37:59 So if I change the sort order for this now, I've got a reference error, so obviously I've done something wrong here as we look up, I'll try that again, but I'm not going to spend a lot of time on VLOOKUP because we're getting rid of VLOOKUP. But you can probably see right away why I say VLOOKUP is not a very forgiving option, Umm. I think I have to look. I have to select the whole table. That's the thing with VLOOKUP, I'm going to select the entire table and then I've got to tell it to return column 2.
38:00 – 38:30 And there you go. But if I get rid of that sort order, Umm, or even sort it in reverse order. Doesn't really matter. I get an error message, so VLOOKUP is not the answer, so I'm just going to wind back everything I did, Umm, and we're back to square one we have these sorted in any random order. We could also have additional columns across all on the side. It makes no difference. X Lookup works much better.
38:31 – 38:51 I'm going to use XLOOKUP to look up the class. So, with XLOOKUP unlike VLOOKUP, you tell it what you want to look up, We tell it where we want to look it up based on a column and then we tell it which column contains the results. So, we select the entire column for the results.
38:52 – 39:20 We'll get there. So, if we change this product to table, for example, it'll automatically dynamically update the appropriate class for the table, which is furniture. If I type in a value that does not exist it's going to give me an error message if I don't like, if I want to avoid the chance of getting a bad error message, and particularly if I'm going to copy this formula down in a table and do a whole lot of could it make it a tabular kind of thing, which is often what one does with VLOOKUP and would do with XLOOKUP.
39:21 - 40:00 Then what you can do is you can edit this formula you can put in an additional optional argument which is a text argument of what should I put there if something's missing? So, I might say put asterisk, asterisk, asterisk. Missing something like that, OK. Incidentally, there were two other there are two other arguments. One of the arguments is Does it need to be an exact match, or can it be an exact match, and if there isn't one, look for the next small item next to a large item. Or you could use a wild card character match, which is actually very powerful for those of you understand what I'm talking about there.
40:01 – 40:27 Using Wild Card match allows you to actually search for like include, do a lookup based on a pattern. Um, and if so, we'll just say zero for exact match, and your other optional argument is in which sequence should I sort search. That should immediately tell you that if you're familiar with VLOOKUP, that in VLOOKUP the contents of my column B. In this example, the column that we're looking at have to be unique. In XLOOKUP they do not.
40:28 – 41:30 So, am I searching for the first match? Top to bottom, bottom to top, and then binary searches we won't get into it, but anyway, let me just simply choose that option and now you'll notice that when I put in junk it says missing, but when I put in let's say sofa, it now finds the right thing. And just to sort of close the loop on this one, I'm going to put another extra curve formula in here which I'm going to do, looking up again the contents of that looking it up in that column and now I'm going to return the margins on from a non-adjacent column. And again, I'm going to just say, um, if there's, if it's not found, we'll just say...And maybe I'll just format Excel nicely, And maybe just get rid of the decimals. Uh, maybe make it centered so.
41:31 – 42:17 Actually, no, probably look better. I'm just thinking here would be if that was all left justified. No, OK, it doesn't really matter. Um, so basically what I've done now is I have created a little bit of an information card that is dynamic and live linked. So, if I change this to tablet it updates immediately. So, this particular example is probably not the way you would use it. You might use the XLOOKUP more likely in a table of itself. For example, to add, let's just say we had an inventory. Yeah, in fact, we could do this, we could, we could take this book sales, Umm, the table and we could say, man, I really want to put another column next to this. But without going back and changing the source data, I want to put a column next to this that has the last sale date out of the inventory table.
42:18 – 42:48 No problem. We could live link the inventory table in another tab and then we could do an XLOOKUP over here in column and XLOOKUP here as a column in this table that looks up the left sale data out of the inventory based on looking at the product code so. Hopefully despite my missteps and needing to be bailed out, for which I'm most grateful. Hopefully you get an idea of the XLOOKUP formula, particularly if you have been struggling with VLOOKUP like I always did. XLOOKUP is so much better.
42:49 – 43:07 OK, I'm mindful that I've gone way longer than I said I would on these things. So, what I'm going to do is I'm going to quickly jump to the last two things that I'm going to show you. I'm not going to give it lots of three things. I'm not going to give a lot of editorial. I'm just going to show it. And again, the recording will hopefully help you if you want to come back to this.
43:08 – 43:46 We're going to talk about a function called aggregate, which again you many of you may know of. I was not aware of it until last year. It saves me a lot of time on certain things in two, two areas in particular. If any of you have ever worked with data that sometimes shows error messages because you're trying to do like this, is actually a calculated column that says multiply 1 column by another and if one of those columns contains text value instead of a numeric value, you'll get that. Also, if you're trying to divide 1 number by another but the number you're dividing by in some columns ends up being zero, you'll get a pound error or pound zero kind of error.
43:47 – 44:11 Then if you try and actually do use the sum formula again, I'll zoom. Sorry, let's just zoom a little bit…If we look at the sum function. If we do a sum of that column I’ll get an error message if there's any errors there. So let's rather do it this way, let's rather use the aggregate function.
44:12 – 44:44 The aggregate function takes a number of arguments. The first thing is how do we want to aggregate it? And for those where this text is a little bit small, the options are things like average count, maximum, minimum, product, et cetera. But I'll choose some, which oddly enough is number 9. You would have thought would be the first choice, and then I will…I get anoption saying ignore nested subtotal and aggregated functions, ignore hidden rows, ignore error values, et cetera, and a whole bunch of things. But one of the things we can do is say ignore error rows.
44:45 – 45:17 So I will choose the argument that ignores error rows and I will simply close the bracket I have to tell it what I'm summing, yes. Let's try that again. Let's now tell it that I'm summing this data, and now I've got too many brackets. But that's fine. And now it actually works. It ignores, or rather treats the error. It treats the error values as if they don't exist. It doesn't even treat them as zero, because you can use some of those other things like products and so on, and it just treats them as if they don't exist. It ignores those error values.
45:18 – 45:48 So that's one of the first uses that I found for aggregate are very useful way of summing or doing some kind of a math on a column where there are error values that I want you to ignore if there's an error value. Second, use for aggregate, which is probably even more useful is, I have a table where I've taken out subtotals. Now, we'll tell you that I've taken out these subtotals not by using the sum formula, but by using either an aggregate or a subtotal formula for these subtotals.
45:49 - 46:17 That is a prerequisite for what I'm going to show you. Now, if I wanted to get the total value of all of this, If I were to say, If I were to say equal, Oh sorry guys, let me just zoom again, make it bigger. If I were to say equals sum. And do this, obviously I'm going to get double values because it's going to sum both the values and the totals. So this should be five $550 300 on and it's actually coming up at 10,000 so we know that's wrong.
46:18 – 46:58 Another way I've seen people do this and I used to do this myself as equals that plus that, plus that, plus that, plus that which is a horrible thing to do, especially if you have 20 subtotals. Not very nice, but because I did not use a sum function here, I used either an aggregate or a sub, or a subtotal function I can go here and say equals aggregate. Sum again is the first argument. The second argument is I can say ignore nested subtotal and aggregate values. So again if this is real small when you do this yourself you'll be able to see options pop up. There are seven options.
46:59 – 47:25 The first one, which the argument value is 0 says ignore, nested subtotal and aggregate functions and then I can just select the entire range all the way down and what it's going to do is it's going to give me just the actual values that are not in subtotal or aggregate functions and we get the right answer. And the nice thing about this is if I add in new sections here with new subtotals, as long as I don't use an equal sum function, this number will always be the correct total.
47:26 – 48:11 OK, and just in case we landed up with an error value in one of these. So, let's just say this ended up being a text value, So let's just say this ended up being an error value I'm just going to simulate, I'm not going to make it really happen. If this was an error value, this would initially show up as an error value except one of the arguments I can do is to combine, ignore subtotal and aggregate values with ignore errors and if you use that option, which is one of the options that pops down then, It's #3 ignore hidden rows, error values, nested subtotal and aggregate functions. And I should also mention this will also allow you as one of the arguments to ignore hidden rows.
48:12 – 48:55 So what that means is if you….I'll just undo that OK if you hide, let's say these two rows our subtotal doesn't update, but if we change the option to ignore hidden rows then it would take those numbers out. So again, if I do that. Ignore hidden rows and largest #3 is everything. It's like ignore everything. Now you'll notice the total has changed. It ignores the hidden rows and then as soon. Now this is dangerous, obviously this could give you. This could give you problems if you don't realize that that's what's going on. But as soon as I unhide these rows. We get our original value back.
48:56 – 49:39 OK, second last thing, just a little thing that I've used for years, but I didn't realize it that many other people don't. I have some data in different contracts. In this example on three different tabs of a spreadsheet and you'll notice each of them uses the same column, column D for the prices, but there are different number of rows. If I wanted to sum the total value of all three of these, the traditional way that I've done this in the past on another tab was to do something that looks like this. Let me just zoom in a little bit. Something that looks like this.
49:40 – 50:22 Equals some of the range on the first tab plus some of the range on the second tab plus some of the range on the third tab. And I suspect there's at least one person may be more on this course scratching head saying Mark, why did you ever do it that way? I don't know, but I discovered the right way to do it is to simply say equals sum, go to the first tab, select your range Hold down the shift key. I should have mentioned that. Select has long range as you possibly can. Holding down the shift key, click on the last tab and then close your brackets and now I have a formula that looks like this.
50:23 – 50:47 And the nice thing about this is that if I insert another tab anywhere in between these tabs and put numbers in that column D, it will add those in as well automatically. So, save you a lot of time versus doing this plus equals this plus that plus the other thing plus the other thing. Final thing for this session, flash fill. Again, if you know flash fill, nothing to learn here.
50:48 – 51:11 Flash fill is a feature added to excel a while back. This one I personally only discovered this year 2021. You may already know about it. I have some data here that is you know, and this is actually something very practical that we sometimes encounter when we do data migration for new customer. So, I have some data in one column that is both city and province and I want to break them out into city and province respectively.
51:12 – 51:46 And I want to do it quickly. Now of course I could easily write a formula that looks for the in string formula that looks for the position of the comma and then calculates that and separates it out. But even easier I could do the following. I can just type in next to the first one Montreal. OK, notice what happens. The system is telling me that it has an order fill function, flash fill function, and that this is what the flash fill function will fill out if I simply invoke the flash fill function from here on.
51:47 – 52:06 So I just press enter and that's what it's done. This looks great. This was really, really quick way of pulling out the cities, right? It worked really, really well. I could certainly do the same thing for the provinces. I can type QC, I can start typing AB, and as soon as it does it, whoops, went too far, Um, anyway, you get the idea. I'm not going to worry about the province, it’s the same thing, but there's a problem here.
52:07 – 52:32 The problem is Fort McMurray is improperly capitalized, looks really ugly. So is Toronto. Winnipeg actually starts with a lowercase W in St. John's is all in caps. And that's really ugly. I that's not what I want. All I want this to do a decent job now. What I'm going to do now is not going to save me a lot of time because we only have 11 cities. Picture where you have 150 cities. The same thing works.
52:33 – 52:57 What you do is you just find two or three examples that would not automatically flash fill correctly and you put in the correct answer, and it then knows what to do. So, I go to Montreal, type it that way. I'll go to Toronto and type Toronto the correct way. I will go to one more maybe actually, that probably will work as is, but I'll just do Winnipeg as well just to be safe.
52:58 – 53:16 And now what I do is I just anywhere in this column, it doesn't matter where my cursor is, I go to my data tab. I select the flash fill icon and if Flash fills that's that that entire range and it is figured out that I want proper casing and no I know caps except for the first letters.
53:17 – 53:43 So hopefully you found that useful. I find it useful, but to be honest I don't use it that often. But it's a very quick way of intelligently filling data and correcting or separating things into points. And yes, this does work even if you have multiple segments. I've taken an entire address with the street name comma City Comma province or state comma ZIP code and I've been able to do this very quickly with Flash Fill.
53:44 – 56:02 That's all I had to say. So, any questions, any last questions, comments, requests before we end the session? We don't have any other questions in the chat, so if anybody else has any anything feel free to ask it now or if not then you can always e-mail me questions later on.
