Please find below a transcription of the audio portion of Mike Thomas’ webinar, Excel – Quick Tips to Help You be More Productive, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience.
Melanie: Welcome. Hello. Melanie here with Team MPUG. Welcome to Excel, Quick Tips to Be More Productive. We invite you to join in today using the go to webinar chat feature. Put those questions in as we go and Mike will stop periodically throughout the session to answer those. If you’d like to speak to us, raise your hand and I can unmute your mic as well. And as usual, I’ll be sending out some little gifts for those audience members getting involved today. You will notice I chatted out the link to Mike’s downloads for this session. There’s some handouts and some files so you can follow along. It’s also in the email I sent with the reminder for today’s session. For our PMPs joining today, the PMI PDU code is on the screen now.
Melanie: Now I’d like to introduce our presenter, Mike Thomas, we’re thrilled to have him here of today. Mike has worked in the IT training business since 1989. He’s a subject matter expert in a range of technologies. His primary focus and passion being Microsoft Office, especially Excel and Power BI. I’m trying to get him back for a Power BI session everyone. So in 2012, Mike founded the Excel Trainer where he is nearly 200 written and video based Excel tutorials. He has recorded several Excel training courses for Pluralsight and in his career delivered thousands of courses and webinars on a wide variety of technology related topics.
Melanie: Mike is a fellow of the Learning and Performance Institute and has worked with and for a large number of global and UK based companies and organizations across a diverse range of sectors. In addition to training, he also designs and develops Microsoft Office based solutions that automate key business tasks and processes. He also just told me that he is available for some one-on-one training sessions, so I’m sure I’ll take him up on that. Mike, a very big welcome to you. Thank you for being with us today.
Mike: Thank you very much. [crosstalk 00:02:16]
Mike: Okay. So you should be able to see my screen. Thank you very much, Melanie. Hello and welcome everybody. So the plan today in this session is for me to take you through just a number of tips and tricks, which hopefully you’ll find useful. Now, I guess there may be things that you know, but fingers crossed there are also lots of things that you don’t know as well. So let me come out of this. And I have a set of demo files. So I’m going to start with tables. If you do want to put questions in as we go, that is absolutely fine. Questions should come through to me and I’m more than happy to stop and answer as we go. Okay. So the first topic that I’m going to talk about is tables. I’m just going to open up this tables file here.
Mike: So I have here a block of data. It’s made up data, it’s sales data, but it could just as easily be project related data, or IT data, or HR data, doesn’t matter what it is. The important thing is there is a set of headings across the top and the data is structured into rows and columns. It goes down to row 50. So you and I would probably refer to this as a table, certainly in Microsoft Project, we talk about tables in the views that we have. There are times that I might refer to Microsoft Project, that does not mean I’m an expert on Microsoft project, Melanie, just to let you know. It’s something that I know a little bit about, but certainly not as much as other applications, just in case you were thinking of getting me back for a Project session. But yeah-
Melanie: You’re reading my mind over here.
Mike: … I’ll take you up on the Power BI one.
Melanie: Okay.
Mike: We’ll talk about that another time. Anyway, you and I would probably call this a table just because it’s the way it’s structured, but this is not a proper Excel table. What is a proper Excel table? Is that a Microsoft term? No, it’s not. It’s a Mike term. A proper Excel table, well, when you have data structured in row and columns, it’s really a multi column list, but you can convert this data into a recognized, or proper as I call it, Excel table. And the only way you know it’s a proper Excel table is because when you click in it, you have the table menu up here. Now, why would you convert a block of data that looks like a table that isn’t a table? Why would you convert that into a proper recognized Excel table?
Mike: Well, I’ll show you that shortly, but the first thing I’ll do is show you how to convert your data into a table. There’s actually a couple of ways. One way is just to click anywhere in this data and then go to format as table and then you choose a color scheme. And not only does it apply the color scheme to the data, it also converts the data into a table. So now when you click into this data, the table menu appears, when you click away, the table menu has gone. If I just undo that, so it’s no longer a table, it’s just a plain list because the table menu is gone. Another way that we can convert our data into a table is to click on insert table or use the keyboard shortcut CTRL T. It will usually pick up the correct range for you, but sometimes it doesn’t put a tick in that box.
Mike: Usually if you just had a bunch of columns that were all text and your heading row was text, then it can’t distinguish the difference between the text which is the data and the text which is the headings. So sometimes it doesn’t tick that box. In this particular instance, we’ve got a column with dates in and a couple of columns with numbers in and so it has made an educated guess that the data on row one is heading, so it had ticked that box, but I would say nine times out of 10 you need to make sure that box is ticked, either do it yourself or it’s done it automatically. Then you click okay and it’s created a table. So now the table menu appears up there.
Mike: So if you want to change the format of that table, you can just pick a different color scheme. Or if you just want to clear the color scheme, because you didn’t actually create a table to make it colored, you can go down to the clear option at the bottom. So it’s still a table, it’s just got no formatting applied to it, which sometimes you want. You want the benefits of the data stored as a table, which I’m going to come onto in a minute, but you don’t particularly want any coloring applied. The other thing I’ll do is I’ll take the tick out of filter button up here. So again, on the table menu you can turn those filter buttons on or off. They are just standard filter buttons that you can use for sorting and filtering, but it puts them on automatically and I’ll just turn them off.
Mike: When you create to table, Excel assigns the table a name. So you’ll find the name on the very left hand side of the table ribbon. And it’s called it Table6. It basically gives it the name table plus a number and that number is the next sequential number. So there’s already several other tables in this file so that’s why it’s called it Table6. But to be perfectly honest, I would always rename my table. Give it a more meaningful name because if you’ve got several tables in the file, you don’t really want to be calling them table one, table two, table three. You’ll only get yourself confused as to which table is which. So with my cursor anywhere at all in this data go to the table menu, click in the box, and give the table a different name. So I’m going to call this table Sales Data. You can’t have spaces in your table names. They are not case sensitive, it’s just the notation I use, capital S, capital D.
Mike: So press enter and I’ve renamed the table and called it Sales Data. Now, one of the big benefits of having your data in a table is that as you add more data, either that way or that way, so either horizontally or vertically, it will automatically increase the size of the table. So if I whiz down to the bottom, the table actually goes to G51 and that’s what that little blue dot is at the bottom corner. So if I was to add some more data, as soon as I enter this data here that little dot moves down and the table now goes from row one to row 52 instead of row one to row 51. So that’s the first thing, and as I said, first of all I was showing you how to create a table, now I’m showing you some of the benefits of having your data as a table.
Mike: And one of the benefits is that as you add more data the table automatically changes its size, changes the amount of rows and columns. Which is great if you’ve got a formula, or you’ve got a pivot table, or you’ve got a chart that is connected to this data, because as soon as you add more rows or more columns, the chart or the formulas or the pivot table will update. Well, actually in the case of a pivot table, if you know about pivot tables, you still have to do a refresh. But what you don’t have to do is go into change data size, change the data source and tell it to now use row one to row 52 instead of row one to row 51. Okay?
Mike: So what I’ve done then is I’ve converted my data into a table and I’ll just show you a few of the benefits of a table. So if I go over to the next sheet, which is chart, this data is not a table because when I click into it, there is no table tab up there. So I will quickly convert it into a table. Its name is Table7. I will just change its name to Office, just is more logical based on what is in there. If you want to clear the formatting and you want to clear the filter buttons you can do, but that’s just aesthetics. I want to create a chart from this data. Now normally when you create a chart, you highlight all the data and then you go to insert and choose chart type. But when your data’s in a table, you can actually get away with picking just a single cell and then go to insert and choose your chart type. So there is my chart and let’s say I want to add June.
Mike: Now normally if the data isn’t a table, you would have to add June’s figures and then edit the data source of the chart to include June’s figures on the chart. But as soon as I type June and press enter, watch what happens to the chart. It’s automatically added the June heading to the axis. If I then typed in some numbers, let’s just make some numbers up. It’s then automatically added those numbers to the chart and it would work the same this way as well. So if I, let’s just type in here Office 2016, not spelled like that, there we go, it’s automatically added Office 2016 onto the chart. I’m not going to fill in the numbers, but you can see that that’s what it’s done. Okay.
Mike: So there’s the first benefit of having your data in a table that anything that is connected to that table, which in this case is a chart, the chart will update automatically as new data is added rather than you having to go into the chart and select chart design, select data. Another thing is, if I scroll down the spreadsheet, you can see that… I’ve gone back to the create a table sheet here. You can see that as I scroll down the spreadsheet and row one disappears off the screen you lose the head headings. Now, I’m sure you know that you can freeze the headings, freeze the panes to keep row one on the screen all the time, which isn’t working there. Hang on a minute. Let’s do that again. So unfreeze them. I hadn’t selected the whole of row two, that was my problem. Okay, so now row one is remaining on screen all the time. So I can actually see what that data relates to, but you don’t have to do that when the data’s in a table, it’s automatic as long as your cursor’s in a cell in the table.
Mike: If I scroll down, I lose the A, B, C, D, E, F, G and it’s replaced by the column headings. That can actually be a little bit confusing sometimes if you don’t realize what it’s doing. I once had a customer say to me, “Mike, I’ve got this strange problem. As I scroll down the spreadsheet, I lose the column headings and they’re replaced by the data from row one.” And I thought, ah, you’ve got the data in a table and you haven’t realized. So that’s a nice feature, just be aware of that. You don’t actually have to turn on the freeze panes and it just means that the column headings are there automatically, just makes it easier to read the data.
Mike: Okay, formulas. Let’s say I go and add into… Well, first of all, this data is a table. How do we know that? Because when I click into the data, I have a table menu. So there we go and the table is called TBL Formulas Demo. Sometimes what I do is I prefix the table name with TBL and then I know it’s a formula. Particularly… Sorry, it’s a table, not a formula. Particularly useful if you go to the formulas menu, and you go to the name manager, and you look at all the names that you’ve got set up in the spreadsheet you can immediately see which ones are associated with the table. I know you have a different icon, but just by looking at the names you can tell which ones are tables and which ones are just standard cell names or range names.
Mike: But anyway, let’s say I wanted in G1 I wanted to put tax, I wanted to calculate the amount of tax to be paid. And the tax is, just to keep it simple, the tax is going to be 10% of the revenue. So I will do =E2*10%, just a simple formula. Now, when I press enter it copies the formula automatically all the way down of the column. So I know it’s only a little thing, but it does save you time. So you type in the formula once and because column G is part of a table it copies formula down. And the other great thing is if you added more data, that formula would be copied down further. So again, it’s just a little time saver. It just saves you copying the formula down. Okay. So let’s just undo that.
Mike: Another thing about formulas with tables is that instead of typing E2, if I click on E2, which of course is another way to create a formula, it actually puts in a different thing into the formula. So instead of seeing E2, you get @revenue. What that means is pick up the data from the revenue column on this row and then multiply it by 10%. So what it’s done is it’s just replaced the cell reference with the column heading and an @ sign. So the @ sign means this row. Some people find that easier. People who like me are old school and have been using spreadsheets for 30 odd years, I started with Lotus 1-2-3 in 1989, people like myself are used to the formulas of E2 times 10%. But what Microsoft tried to do is make it easier with this kind of natural language, if you like, where you reference the columns by their names rather than sell references. Some people like it, some people don’t, but that’s how it works.
Mike: Okay. Another thing to mention is formulas where you want to do something like a sum. So let’s say I wanted to do a sum of the revenue column. So traditionally I’d have done =sum(E2:E50), or whatever it is. The problem with doing that is if you did sum E2 to E50, then as more rows of data are added at the bottom here that formula will not update and it would have to be edited. But instead of doing that, I can put =sum open brackets, and it’s not just the sum function, any function that’s going to refer to data in a table you can do this with, so =sum, open brackets, the name of the table, which escapes me. So the table is called TBL Slicers Demo One. So I’ll do that again, =sum(tblSlicersDemo1, double click on that, open square brackets and that brings up a list of all the column names from the table, and then just double click on revenue, close square brackets, and you’ve got a total.
Mike: And as you add more data that will update. So if I just added some more data, the table is resizing and that total has updated. Okay. So there is more to tables, but we have a certain amount of time and a certain set of topics that I said I would cover. So I’m just going to pause there, grab a quick drink. Does anybody want to put any questions in the chat?
Mike: Or ask a question verbally? Melanie said she could unmute people if they wanted to ask. I don’t mind leaving questions to the end. I don’t mind taking questions as we go.
Melanie: Mike, this is a question from me. If I pasted a lot of data in here or imported data, how would that work? So I already put it in the table and now I need to add…
Mike: Okay, right. Let me actually show you importing data, because that was one of the topics that I was going to cover anyway, so that’s a nice little segue. If you actually just copied and pasted data and you dumped it at the bottom, I think that would resize the table. Let me just check. Let me just delete those rows there so the table is back to how it was. And then just pick up a bunch of data from another table or another sheet, it doesn’t matter whether this is a table or not, go to here, paste at the end. No, it hasn’t, it hasn’t. Okay. So no, it looks like it’s not. I know there’s a different number of columns here, but it looks like it’s not actually resized the table, but if that happens, you can resize the table yourself.
Mike: You should be able to grab hold of the little blue dot, get the double-headed arrow and drag down and across, and that will resize the table manually. So I have found that sometimes tables don’t resize for certain reasons, so if you want to do it manually, then you can do it. So it looks like when you paste new data, certainly in this instance, it’s not resizing, but I have seen it when it does resize.
Melanie: Thank you for showing us that. And I have to interject this one, I just got a question in there, an audience member saying this is brilliant, so thank you.
Mike: Excellent. Thanks, Joe. I’ve just seen that myself. Right, let me talk about importing data then, because I’m sure that’s something that you will do from Microsoft Project, because Project from memory can take data from Excel, either in an Excel format or a CSV format, I’m guessing. But I’ve got here, I’ve got a CSV file. I’m assuming you’re all familiar with CSV files or at least what the term means. It stands for comma separated value and it’s basically a plain text file where the data is separated with commas and most applications can export data as CSV and most applications can import data from CSV, so it’s a great format for data exchange. If you take something in your personal life, I can log into my online bank and I can download a statement, a list of transactions, in CSV format and then load it into Excel.
Mike: But what I want to do is I want to get this data into Excel and I’m going to show you the new way of doing it. One way of course is just to open this up, and just select it all, and copy and paste it into Excel, but that’s not always going to work. So if I open up a new spreadsheet in Excel and I go to the data menu, and on the data menu we have this little area here called Get & Transform Data, which used to be called Power Query. So what Microsoft did way back in 2013 is they created this add-in called Power Query that you could download for free. And you could then use it to import data from lots and lots of different data sources. And then in 2016, they changed the name to Get & Transform Data, which isn’t as sexy as Power Query, but everyone calls it Power Query. So maybe Melanie, you can use your contacts at Microsoft to get it changed back to Power Query because everyone calls it Power Query.
Melanie: I don’t think they’re going to come up with a sexy name for anything, Mike.
Mike: No, I think you could be right. Anyway, if we go to get data, so on the data menu, if we go to get data, we’ve got loads of different data sources that we can pull data in from. So you can pull data in from another Excel file, you can pull data in from a text file, which is what we’re going to use in a minute. You can pull data in from a PDF file, which is very timely for me to mention because my latest YouTube video, which I released on Monday, is all about how to get data in from a PDF file and some of the gotchas you’ve got to watch out for. I’ll give you my YouTube link at the end. You can pull data in from a whole bunch of databases. You can pull data in from Power BI, you can pull data in from SharePoint lists, you can pull data in from webpages. So there’s a whole bunch of data sources you’ve got here. Microsoft do add to these on, I was going to say regular basis, a semi-regular basis.
Mike: Anyway, I want to bring data in from a CSV file. And I want to find that CSV file, which is in this folder here. And then I’m going to click the load button and what the load button will do is it will load the data into… Actually, I’m not going to click the load button because if I click the load button, it’ll load all the data from that CSV file into a new worksheet because that’s the default, new worksheet. So instead I’m going to click the drop down arrow next to load and choose load to, and I’m going to choose existing worksheet. So basically clicking load to gives you more options. So choose existing worksheet. I’m going to dump it into A3, because I might want to heading on row one, and click on okay. So it brings the data in and it creates a table. So automatically it’s created a nicely formatted table. There’s the table menu. It’s called it Employees because it bases it on the name of the data source that it was bringing in.
Mike: If you want to redesign the table in terms of colors, et cetera, you can do because it’s just a normal Excel table. This little panel over here on the right hand side is the queries panel. So what happens is when you bring data in from any of these sources using the data, get data, it creates what’s called a query behind the scenes. And that query is basically a set of instructions that holds the connection information. So Excel knows where the data’s come from. I’m going to close the query panel down. We don’t actually need to see it. Now, if I was to who go back to this CSV file and I was to make some changes, so for example, I’m going to change Bernard Gomez and he is now based in the UK, so he’s moved from France to the UK, and I’m going to add another employee at the bottom.
Mike: So I’m just going to, rather than me doing a load of typing, I’m just going to do a copy and paste. Okay. And save the file. Now of course, in the real world, you wouldn’t edit the CSV file directly. You’d have to lock into your HR system and perform an export to generate the CSV file. And that’s pretty much like Microsoft Project because… And two questions have just come in. I don’t think you can do this directly from an MPP. I haven’t got Project installed on here otherwise I’d test it. It doesn’t actually appear that you can pull data in directly from an MPP. So I’m guessing what you’d have to do is get it out from a Microsoft Project file as a CSV file or as another Excel file and then import it that way. So sorry to disappoint you but that seems to be how it is.
Mike: I would’ve thought that with it being Microsoft Project to Microsoft Excel that they could create a connector between the two, but it doesn’t look like there is one unfortunately. But yeah, anyway, what I’ve just done is I have updated that CSV file. So in your case, I would guess that what you’d have to do is every time you wanted this data to update you’d have to go back into your project file and perform another export to end up with an updated CSV file. But my point now is that I want this data to update based on the updated data in the CSV file. So what I would do is I would click anywhere in this data and the query menu appears. And the reason the query menu appears is because it knows that this table is based on a query.
Mike: So I click on query and I click on refresh, and what it does is it goes out to that CSV file and it’s updated France to UK and it’s added the new row of data. So it’s really, really easy to keep this data up to date. Okay. How can Bernard Gomez start working six months prior being born? Yes, it’s not real data and yeah, I did have some data a couple of weeks ago where I had the 29th of February in a non-leap year and wondered why it was throwing up errors when I was trying to do dates of birth. But there you go, it’s just made data, Joe, but well spotted. Okay. So any other questions on that?
Mike: Feel free to put questions in as we go. I think somebody put in a question and kind of broke the ice, didn’t it? So people now seem to be quite happy to put questions in and that’s fine. Now, the other side of Power Query or Get & Transform, it’s not only getting data in, but you can also edit the… You can also go into what’s called the query editor to edit the data. So let me give you an example because you might be thinking, why would you need the query editor if you need to edit the data? Can’t you do it directly in the spreadsheet? You can, but let me give you an example.
Mike: So let’s imagine you import this data on a weekly basis or on a monthly basis. Can you automate the data import query once it’s set up? That’s exactly what I’m going to show you, Joe. It’s not totally automated, although you could create a macro to automate it. Actually there’s a couple of things you can do, so just bear with me on that. I’ve just had a couple of thoughts. But yeah, actually setting up the import, actually doing the import, which I just did a couple of minutes ago, that was a manual thing. But let’s imagine that you import this data on a weekly basis, and okay, this is HR data, but it could just as easily have come from Microsoft Project, so it could be data from Microsoft Project. But let’s imagine that you need to build some charts and pivot tables, which I’m not going to cover today. But imagine that you need to build some charts and pivot tables and you need the department column to be displayed in upper case.
Mike: Now, one way to do that would be to go to a blank column and use the upper function to convert that text into uppercase and then copy and paste that data over the top of this data. Okay? Now, some of you may not be familiar with the upper function so I will just go through that again. You can convert text to uppercase, or lowercase, or proper case, which is where the first letter is capitalized by using a function =upper(E4), enter. And that converts the text in E4 to upper case and then we can cut and paste that over the top of that. But that means that the next time we do a refresh it’s going to reload this data into this table from that CSV file. At this point, that column won’t be there.
Mike: So when it does the refresh, this will go back to not being in uppercase because it’s reading the data in from the CSV file. And that goes for other things, so for example, let’s say you don’t want to include TET as the employee number. So the employee number it starts with TET and that is stored in the CSV file, which originally came from Microsoft Project or wherever, but you don’t actually want to show the TET. So I know you can do data text to columns to split that into two columns and then remove the column with the TET, but next time you do a refresh it’ll come back. So the idea of editing the query is that you can provide instructions to manipulate the data. So let me show you.
Mike: I’m going to click on query. I’m going to click on edit. And by the way, we are literally touching the tip of the iceberg with this, okay? Literally touching the tip of the iceberg. I can spend an entire day covering this query editor. So what I’m going to do is I’m going to click on the department column and I’m going to click on… Where’s it gone? Transform, format, uppercase. And what that’s done, over here it’s added a step to the query. So these three steps were created automatically and basically those three steps are the instructions to go and get the data from that CSV file. I am now manipulating the data as it’s loaded into Excel. If I then go to this column here and I click on transform, extract, text after delimiter, and specify the delimiter as the dash, what it’s now done is it’s kept everything after the dash and it’s added another step. So literally when you are back in Excel and you hit the refresh button to reload the data in from the CSV, it runs through those actions one at a time.
Mike: So the first three actions are all to do with get in the data from the CSV. The fourth action is uppercasing that column. The fifth action is removing the TET from that column. So if I then come out of the query editor, it’s now updated the data, but also every time you hit refresh it goes and grabs the data from the CSV file and then it performs those other two steps that I’ve just added, so that means that will always be an uppercase and that will always not have the TET.
Mike: So when you are talking, Joe, about can you automate the data import query once it’s set up, you can automate it so that all you have to do is hit refresh, which is just a couple of clicks, but you can also in the properties of the query actually have it automatically refreshing every X number of minutes, so that is real automation. So if I set that to 10 minutes, every 10 minutes Excel will go out to the CSV file that’s on my desktop, and it will read in the data, and it will perform those data cleaning or data manipulation, whatever you want to call them, actions.
Mike: Okay? So you’re saying that’s absolute gold, Steve, and that is just the tip of the iceberg. Power Query is amazing. Okay? Right, any other questions on that? Anything you want to interject with, Melanie?
Melanie: No, this is great. Thank you.
Mike: Okay, cool. Right. Okay. Right. Let me close that down. I won’t save it. I’m just trying to work out what best things to cover are based on the time we’ve got. So the next thing I’m going to throw in is dynamic array functions.
Mike: Okay. Dynamic array functions, these are fairly new and they are a real game changer in terms of what you can do with Excel. Now, dynamic array functions are only available to Office 365 subscribers. And I appreciate that not everybody subscribes to Office 365, although I think more and more people are doing that these days. Now, just to mention about Office 365 or Microsoft 365, as it’s now called, because some people seem to get the wrong ideas as to what that is, some people think that Office 365 is Microsoft Office running inside a browser. So the web version of Word, Excel, PowerPoint, it’s not. Office 365 or Microsoft 365, let’s call it it’s proper name, is a subscription service where you pay Microsoft a certain amount of money or your company does each month or each year and for that you get monthly updates. So sometimes these updates are big, sometimes these updates are small.
Mike: The dynamic array functions was a biggie. In fact, it was bigger than a biggie, it was a huge thing. But what Microsoft are doing to dangle that carrot in front of your eyes to make you or persuade you to part with your money every month is giving you updates. Some of these updates are really small but really useful. Now, I am on the beta program. I’m signed up to the Office beta program, which basically means I get a lot of the new functions before they’re released to the general public. And sometimes within organizations, companies don’t update Office every month. Some companies do, some companies don’t. The company I work for, it’s no secret, it’s on my LinkedIn profile, I work for Astrazeneca, and they don’t update Office every month. They update Office every six months. So the version that they’ve got of Office is six months behind the version that you might have and it might be eight months behind the version that I have because I’m on the beta channel.
Mike: So let me just show you something. If I select a bunch of numbers, if you look at the bottom of the screen, you can see the total of the selected numbers, the average of the selected numbers, and the number of selected numbers. That is not new, that has been around in Excel for years, and years, and years and it’s quite handy if you just wanted to know the total without having to actually go and put a sum function in and then delete the sum function. But now if I wanted to copy that total, I can click on it, copies it to the clipboard, and then just paste it in a cell. It’s not dynamic, it’s a static number, but it means at least you don’t have to highlight a bunch of cells, remember what the total was, and then type it into a cell.
Mike: So that is new feature, but it’s not available to everyone yet. So I just threw that in because it reminded me when I was talking about the beta. Anyway, hopefully that makes sense to you with Office 365. So I just digressed very slightly there. So back to these dynamic array functions, and it could be that you don’t have these functions available to you. So if any of you are following along with me and you find that it’s not working for you, that’s the reason why. Okay, so let me show you the first function. The first function I’m going to show you is the unique function. So we have a small set of data here and what I want to do is I want to get a unique list of my customers.
Mike: But the first thing I’m going to do is I’m going to convert this into a table. My personal mantra is anytime you have data in a tabular layout, convert it into a proper table, so that’s what I will do. Insert table, click okay, that’s now a table. I’m not going to concern myself about the formatting. It can just stay as it is. And I’m going to change its name from Table1 to… Well, actually, no, I’ll leave its name as Table1. Right. I want a unique list of customers. So one way to do that would be to manually copy that list, paste it over here. And then with that list selected, go to data, remove duplicates, click okay. And we now have a unique list, which we could then manually sort. A bit faffy though isn’t it that? A bit messy. So how about if I go to F2 and type =UNIQUE and then specify the array. So if I specify the individual cells, it’ll highlight, it’ll actually show you the range as I’m highlighting the cells in the brackets in F2.
Mike: But when I get to highlight the last cell, because I’ve highlighted the entire column it’s changed it to be Table1 Customer using that notation that I showed you earlier. So it’s =UNIQUE, you can either specify a column or you can specify a row, because you might have the data going across a row, but to be honest it’s more likely to be in a column. And if your data is stored in a table, once you’ve highlighted the entire set of data it uses the table name and the column name. But if I then close the brackets and enter, job done, we now have a unique list. But that’s not the end of the story. Okay? So no messing around with copy and paste and then manually deleting the duplicates, just bang a formula in, tell it which column you want it to look at, and it does the rest for you.
Mike: Okay. So a couple of things you’ll notice, first of all, what’s this blue border going around the cells? Well, the blue border indicates that that is being treated as an array. An array is basically a block of cells, multiple cells, that contain a single formula. So it’s being treated as a block. I know it is a block of cells, but it’s actually being treated as one unit. Some people find it a little bit difficult to get their head around that, but it is separate cells, but it’s being treated as one unit. In fact, if I actually scroll down the cells, you can see if you look in the formula bar up here, you can actually see that it’s the same formula. I didn’t have to copy it down. It gets copied down automatically. And all of the cells, apart from the first one, are grayed out.
Mike: So you can’t actually edit this formula here and you can’t delete a single entry. I’m pressing the delete key and nothing’s happening. The only way to delete it is to go to the first one and hit the delete key and that deletes them all. Or if you want to edit it, you’d have to edit the top one. So it is a block of cells, it’s multiple cells, but they effectively contain one formula. Back in the old days, pre array formulas, it will be one cell, one formula. Now, it’s multiple cells, one formula, but only if you are using one of the array function and there’s about seven or eight of these functions. Okay? So the blue border indicates that that is an array. Basically, what happens is you enter the formula into one cell and the range of values that is returned by that formula spills into the other cells, which is why people often refer to that as the spill range.
Mike: And in fact, let’s imagine I for some reason had some data in one of those cells there, if I put in =UNIQUE and then select the range of cells, I get a spill error. And if I then go and delete that cell there, it then works for me. If I then go and add a new entry, so I’ll add mint and I’ll add, let’s say MPUG, and I add Kentucky, and 100, it automatically added MPUG. I didn’t need to do anything. It’s automatically increased the size of the array, which it wouldn’t do when you were doing the manual copy, paste, remove duplicates. But this is a formula which is connected to this, so as you add things here, it updates this automatically.
Mike: Hopefully you are amazed at that because I was when I first saw them. Now, that’s only one of the functions. Let me show you another function. Let me show you the sort function. So I have a list of customer names, I know it’s only one column, but I’m going to convert it into a table. And then over here I’m going to put =sort open brackets, and then I can either type in the name of the table, but I can’t remember what the table’s called, it’s called Table Two, so I could actually type it in =sort(Table2[Customer]) and it gives me a sorted list.
Mike: Now, what if I wanted a sorted list that was unique? Okay, here’s a real life example. I’ve changed the names, but it’s a real life example. Anybody use Microsoft Teams? I’m sure you do. Well, in Microsoft Teams if you are the organizer of the meeting, what you can do is you can get a list of the attendees and it generates a CSV file. And what it does is it displays the name of the attendees and it displays their action and their time. So we can see that Sue joined the meeting at one minute past 11, but she left a quarter past and then rejoined one minute later. So I’m assuming she had internet trouble. But what I actually want now is I want a list of the attendees sorted by name. So I don’t want duplicates, I want a list of the attendees sorted by name.
Mike: This is actually a real life thing that I have to do after each of my training courses, because I need to get a list of the people who attended and then I need to compare them to the people who are signed up for the training, but didn’t actually attend. So what I’ll do here is I’ll put =sort, open brackets, unique, open brackets, select the names. I didn’t practice what I preached there, I didn’t convert the list to a table, but nevermind. Closed brackets, closed brackets. So what it’s going to do is it’s going to give me a unique list of the names from column A sorted. And there we go, you can see that Sue only appears once.
Mike: Okay. Just see a question there from Jeff, or a comment. You might mention that you can click the header of a table column to select the table column too. Yep, you can do that. You can click the column header of a table to select it. So if I put =UNIQUE and then click the column header, oops. And then that’s not working. Why isn’t that working? Oh, I haven’t got the arrows. There we go. Oh, that’s still not working. I don’t know why that’s not working. Okay. But yeah, you can click the header of a column to select it when you’re doing a formula. Good spot there. Okay, other things that you can do. Let’s say I wanted a unique list of customers. Sorry, no. I wanted to know how many customers we’ve got, but I didn’t actually want a list of customers because what you could do is if you’ve got a list of customers, what I could do is I could put =counta, open brackets, and then select that range.
Mike: And you’ll notice that as I highlight all of the sales there, as soon as I get all the sales highlighted, it changes to F2#. The # means all the values in the column. So it’s automatically counting all the values in the column if you use the # sign and it’s giving me 10. Okay. So if you ever see a formula where you have something like =count or =counta and then you’ve got a cell reference and a # sign, the cell reference refers to the first cell of the table array and then the # sign means the entire table array. Okay?
Mike: Right. Yeah. So I’m going to delete those. One more thing to show you on the dynamic functions is if I was to put =counta, open brackets, UNIQUE, and then put in Table1, which I think is this table, Table1, square brackets, customer. That means that I have got account of the unique customers and that way I haven’t actually had to list the unique customers. So if you want to know how many customers you’ve got without listing them and then counting them, you can actually count the number of unique customers that way. So it’s combining two formulas. Okay. Any other questions at this point before we move on to something else?
Mike: Because we’ve got just a few minutes left and I really want to show you XLOOKUP.
Mike: Okay. So let’s go on and look at XLOOKUP. Right. I’m guessing everybody is familiar with VLOOKUP and HLOOKUP. Maybe not, but VLOOKUP and HLOOKUP, so very, very quickly, if I want to know, let’s say I wanted to know when the contract… So this is a list of employees and their mobile phones and I wanted to know when the contract was due for renewal for that employee there. So what I would do is I would go to B4 and I would use a VLOOKUP. So I would put =VLOOKUP, the lookup value, so that’s what I’m looking for, which in this example is that cell there. The table array, the table array has to start with the column that contains the matching values, so because we are looking for an employee ID, we have to start with the employee IDs, but we have to include at a minimum the column that contains the answer.
Mike: So in this case, because the answer’s coming from column I, we have to go all the way from column D to I. If we were looking for the model of the phone, then we’d have to go from column D to column G. You could go further, but at a minimum D to G. Okay? Then you have to tell it which column the answer is coming from. Well, it’s column 1, 2, 3, 4, 5, 6. So the column index number means it’s the sixth column of the table. So I had to manually count and I have seen look up tables with 20 odd columns and it’s a nightmare manually counting. And then the final parameter is the word false because we want an exact match. So that is a quick example of VLOOKUP, as I said, some of you may have been familiar with VLOOKUP, some of you may not have been.
Mike: VLOOKUP is great, but there are a number of disadvantages of it. One disadvantage is that it’s clumsy. You have to include a reference to which column based on a number the answer’s coming from. And one of the biggest problems with VLOOKUP is that it will only look to the right for the answer. So for example, if we were… Let me delete those two. If we were putting in a phone number and we were asking it to look down that list to find the matching phone number, and then we wanted to return the model, we could use VLOOKUP for that because the model is to the right of the phone number. But if we were wanting to return the name or the employee ID, then we couldn’t do that because they are to the left of the phone number. So you’d have to restructure your lookup table.
Mike: All that changed with XLOOKUP and XLOOKUP can work down a column, but it can also work across a row. So Microsoft have kind of replaced VLOOKUP and HLOOKUP with XLOOKUP. So if I was looking for the name of the person who had that phone, what I would do is put =XLOOKUP. We’re looking for that phone number, we’re looking for it in that range, and we are looking for the answer in, let’s do the employee ID, not the name. We’re looking for the answer in that column there. Close the brackets and enter, and that’s it.
Mike: There are loads of benefit of XLOOKUP. I haven’t got time to go into them right now, but if you’re interested, I have got a blog post on my blog, I’ll give you the link in a minute, Five Ways That XLOOKUP beats VLOOKUP. But yeah, XLOOKUP is really, really useful. I was helping somebody today who was getting stuck with VLOOKUP and I said, right, delete that VLOOKUP and I’ll show you XLOOKUP, one, because it’s better, it’s the future, and two, because she actually wanted to grab data from a column to the left of what she was looking up, so a VLOOKUP wouldn’t have worked anyway.
Mike: Okay. I think we have hit the top of the hour near enough. So hopefully you found that useful. I don’t know what the protocol here is, Melanie, if you, if you want to facilitate a Q&A session, then I’m up for that.
Melanie: Yes, if we have more questions. I will just switch back over momentarily for so people can have the PDU if they missed that. I’m a little teary, Mike, because you have saved me countless hours in the spreadsheets that I’m using, so thank you so much.
Mike: You’re welcome. Can you just re-share my screen again because I’ve got my contact details on there?
Melanie: Certainly, certainly.
Mike: Okay. So if anybody wants to drop me an email, whether you’ve got a question, whether you just want to say, “Hi, I enjoyed the training,” feel free. Go to my website, visit my YouTube channel, subscribe to my YouTube channel, link up with me on LinkedIn. I hope that’s been useful.
Melanie: It was amazing. There’s comments coming in from the audience saying it was brilliant. And Mike, if you don’t mind, I will share all this information with the audience that you have here today. I’ll send it out to them after the session.
Mike: You’re very welcome, no problem at all.
Melanie: Yeah, I will share that with the audience, if you didn’t get time to write the it down, and we’ll make sure it’s included in the thank you and the notes to the folks who couldn’t make it today.
Mike: Okay. Gus says, “Do you use index and match?” I have used index and match. In many situations I think XLOOKUP is actually a good replacement for index and match. There are still situations where you would use index and match, but some of that has been sort of… is now, I wouldn’t say it’s obsolete, but I think in many situations you can use XLOOKUP, just makes it easier.
Mike: Okay. Thank you very much, Joe. I’m very honored because I know you’ve had Mr. Excel, Bill Jelen, so hopefully I rate up there with him.
Melanie: This was absolutely excellent. I will just briefly, if there’s any more questions please bring them on, I will share Mike’s information. I’ll briefly put the PDU activity code up the screen, as I know many of you need that. So again, Mike, thank you for a super session, a big session. Big thank you for our audience today. Thank you for choosing MPUG to grow your skills with today. And I know this was a particularly powerful session, so thank you again, Mike. I’ll share a link today with this recording, a quick survey, Mike’s contact information, so please let us know your thoughts. Mike is coming back with us for a course, which I’m very excited about. And I will also follow up with more information on moving Excel between Project as well. So thank you for your questions. Mike, if there’s any other questions I’m happy to hang on here.
Mike: No, nothing from me and it doesn’t seem like there’s any more questions coming in. So hopefully you found that useful. It seems from the comments that you have. So thanks, everybody. Stay safe, take care, and have a good rest of day, and I’m sure we’ll see each other again.
Melanie: Thank you.
Mike: Cheers, everyone. Bye-bye.
