Please find a transcription of the audio portion of Bill Jelen’s MrExcel’s Favorite Excel Tips and Tricks webinar 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. Watch the complete webinar on-demand at your convenience.
MrExcel’s Favorite Excel Tips and Tricks
Posted: 8/8/2018
Presenter: Bill Jelen
Moderator: Kyle
02:59 – Continuous Excel Demo: In my book “The 54 Greatest Tips Of All Time”, I started with what I call the “gasp” tips. These are the tips that if I have a room full of 100 accountants who use Excel 40 hours a week, that I can make half the room go “huh!”. That’s one of my favorite tips. One that I get started off with is…a long, long time ago, I was working in an accounting department in Akron, Ohio. A guy in the marketing department had gone to a trade show and come back with thousands of rows of data. First name column A, last name column B. For whatever reason that day, he decided he needed to get that data together for a single column. Being someone who doesn’t use Excel all the time, he came over to column C and started doing this [typing in the full name manually]. Thousands of rows of data and he was going to type all of those. He got about an hour into that when one of his co-workers walks up behind him and says “Hey, what’re you doing?”. “First name in column A, last name in column B, I need to Jon them together”. That guy didn’t know to solve it but he called me and I came over. It took a few attempts for this formula.
“=A2&B2” is how you join texts together. It takes the text from A and the text from B and smashes them together into a single cell like that. But of course he wasn’t happy with that because there’s no space [between first and last name]. I went back and edited that formula.
“=A2&” “&B2”. Whatever’s in the quotes, in this case a space, now I know I’m going to save this guy a day’s worth of work. He says “That’s okay, thanks for stopping by but that’s not going to work, I’m going to keep typing”. I asked what he meant and he said “I was typing in upper and lower case, in proper case. You’re typing in all upper cases. This isn’t going to work, I’m going to be doing a mail merge…”. I told him to hang on, we have 455 options in Excel, including a function to solve this very problem. I edited that formula and wrapped the whole thing in the proper function (=PROPER(A2&” “&B2). Take that text from A and the text from B and capitalize each word. Finally have something that works. That whole setup there was the buildup to “How do we copy this formula down?”. If you have thousands of rows what most people do is come here to the little square dot, it’s called a “fill handle”, they’ll start to drag and that works great for 5 or 6 rows but once you start to scroll, then things begin to go crazy. They slowly scroll down to the bottom of the data but if they miss, you can see now I’m on row 608, when there’s only 100 rows. It’s very easy to miss the bottom of the data. People call this the “Yoyo” where you go down too far then up too far and then down too far. A really frustrating thing for people in Excel so I’m going to undo that and instead I want to show you the fast way to do this. In the exact same spot. The fill handle, the little square dot, you’ll see it starts out as a big + white sign but when you go to the right spot, right over the fill handle, we have a black +. That means you can now double click the fill handle, Excel will look to the left, all of the columns to the left and figure out how many rows of data we have today and it’ll shoot that formula down to the last row of the data. Excel knows where the end of the data is because they’re able to pause for 200 microseconds as you’re dragging so it’s easy enough for them to just shoot the data down. If you’ve been using this trick for a long time, there’s been a problem we had if someone didn’t have a last name. Let’s say it was “Cher” or “Madonna”. Excel would look at (the last name in row B before the first name only column) and stop there at row 6. But starting in Excel 2010, without any fanfare, never made to the powerpoint slide about what’s new in Excel. They now look at all the columns to the left and find their way to the bottom. We have a couple things, the “proper” function. By the way, the way I’m showing you the “proper” function is an awesome new thing called “formula text” function. It will show you the function next to the cell, it’s new in Excel 2013. Double click the fill handle in order to be able to copy that formula down. Next time you have to copy a formula down a thousand rows, double click the fill handle, a fast, fast way to go. If you’re in Excel 2013 or newer, this whole thing become irrelevant because they introduced something new on the data tab called “flash fill”. What this does is give Excel an example of what you want. It’s really important for flash fill that everything has a heading including columns A and B but also column C. You have to have a heading in column C or this won’t work. Let’s come over here and I’ll do something different [going to column C]. First initial, last name, proper case like that. I can do the first one and then from the blank cell, hit flash fill and they will follow my lead and copy that down. Is it possible to do this with a formula? Yeah but flash fill is a great way to be able to take data and join it together or even break that data apart. If someone had sent you for example data with all the information in one cell we can use flash fill to break that apart. Yes, text to columns by all means but…if you just need to get the zip code [for example, types 86044]. Flash fill is actually a little nervous to work with numbers so it’s good to give it a couple examples particularly one that had a leading number zero which I don’t see here on the first page. [Hits flash fill] They correctly figure out that I was trying to pull the last 5 characters from the cell. So, flash fill, brand new in Excel 2013. Let’s more one to the next example.
This is about using filter. So here we have a large database 563 rows of data, several different customers, lots of different products and for whatever reason we need to run a report of all the Friday sales of widgets or something like that. So the trick here is the filter and this has been around for a long time, it used to be called auto filter, now it’s just called filter. Turns the filter drop-down on and filter has actually gotten better over the years. I actually don’t like how filters improved over the years because it used to be that if I just needed widgets I could open product, choose widget and I’d be done but now to get to widgets I have to open product, I have to uncheck select all and then recheck widgets. That’s two extra clicks but you know, the people that use Excel all the time or any product all the time are really upset when Microsoft releases something that causes two extra clicks in the process. And if you had a lot of customers, it’s frustrating to scroll through that large list of customers to get to where you’re looking for. Rather than use these dropdowns, I’m going to use a feature that I finally remember from Microsoft Access. I used to use Access a lot when Excel only had 65,000 rows because I’d have larger data sets. I could take that data to Access, I was always filtering in Access. Access had an awesome button up here called “filter by selection”. If I needed all the widget sales, all I would have to do is click widget and then click by selection. I have 2100 videos out on YouTube and this was so long ago, video number 300 and something. Someone writes in and says “hey, filter by selection in Access is really cool. Could we do that in Excel?”. Back then, I wrote a 29 line macro, a little macro you could add to your macro workbook that allowed filter by selection. Video goes on YouTube and you get views upon views. About 3 days later, someone writes in the comments “wait a second…why did you write that macro when filter by selection is already in Excel?”. I’m like what? This was back in Excel 2003, this was 15 years ago. He said “yeah, it’s there. Now, admittedly, you’ll never find it because they’ve hidden it”. So here is where filter by selection is hiding. You want to add this to your quick access toolbar, that’s the strip up here with, undo and redo. Right-click up there and choose “customize toolbar”. And you see we start out with the popular commands, 40 or 50 commands and you can see this command is not popular because no one realizes it’s in Excel. Instead, I’m going to open that left drop-down and choose either all commands which is all 2500 commands or my favorite one, commands not in the ribbon. This is command stuff that they’re hiding from us. Things they don’t want us to know about and it’s not under the letter “f” for filter by selection or even “s” for selection. It’s using the old, old name of auto filter. Choose auto filter, click the add button. Once you add it there, it’ll be under every quick access workbook on this computer. Then it’s very easy. If I need to see all the widget sales, choose widget, see all the dropdowns are not on yet and then instead of the filter and the ribbon I go to quick access toolbar and it turns the dropdowns on and it’s just the widgets. Now, if I need to further filter, to take just financial modeling purchases of widgets, I’ll choose that and then click auto filter again and I get just those cells. It’s so much easier than opening the dropdown to find what you’re looking for. If I needed both widgets and gadgets, this trick isn’t going to work but if you just need one item from this column, one item from that, awesome, awesome trick.
I enjoy traveling around the country, doing these seminar’s live. I used to be out of Akron, Ohio now I’m in Orlando, Florida so anywhere you could fly easily on the eastern side of the country. I was up in Fort Wayne, Indiana 12 years ago, I got to this point in the trick and I always encouraged people in the audience to give me new tricks. I learn a new trick every time I do a seminar and here in Fort Wayne a guy named David stops and says “hey wait, I got a new trick for you. I bet this is new. Wouldn’t it be nice if you could see the total of just those visible records. You’ve applied a filter, you want to see a total of the visible”. I sad that would be a hot trick. He said the most important part of this is you have to make sure you filter first and then you have to be in the first row below the filtered data. Then come over here to the revenue column. What I’m going to do here is what almost everyone does as the first thing in Excel which is the auto sum. The auto sum of course puts the sum function in. Excel is going to change the formula since it’s sitting below a dataset that has been filtered. It’s not going to give me the sum function, it’s going to give me the “=SUBTOTAL(9)” function. Subtotal is awesome because it ignores the hidden rows, the (9) tells us to sum. There’s 10 other functions you can do there. Average count, max/min but it defaults to the sum.
You can see the Fintega Financial Modelling purchases of widgets, 32544. If I would change from widgets to let’s say gadget then the total is 16936. I always have a little prize for someone who shows me a new trick so I gave David a little pin for Excel Master. Next year, I’m invited back to Fort Wayne and I get set up and David’s manager, not David, David’s manager and he says “I didn’t bother to come last year, my guy David came and he got a little pin”. I said “yup, I remember David, I stole his trick, I’ve used it at every seminar”. He said “look, that’s not David’s trick. That’s my trick, David learned it from me.” I’m wondering if he showed up just to get a pin and he replied “no, I’m not here for the pin. He didn’t show you the second half of the trick. Which is the best part”. He said sometimes you’re going to choose a customer that has more than one screen and you’re going to have to scroll down and back up to see the total visible. So get that total visible and then insert two new rows at the top of the worksheet and take your total and cut, it has to be cut, control X, not a copy and then paste at the top. Then, when I choose more customers than will fit on more than one screen, even though I can’t see the bottom of the screen, I’ll always have the total visible at the top. That’s one of my favorite tricks of all time. First the filter by selection, total visible from David and then moving the total visible to the top so that way you essentially have a nice set of ad hoc totals there from David’s manager.
I’m going to talk about subtotals and then pivot tables. We’re going to come over to this dataset. We have region, product, date, sector, customer, quantity, revenue, COGS (cost of goods sold) and profit. I have two years worth of dates here, about 563 rows of data running from 2017 all the way through 2018. I’m going to sort this by customer. Now to sort by a single column, just choose one cell in that column provided you have no blank rows, no blank columns, headings above every column. Excel’s intelisense will allow this to sort correctly. Click the data tab then A to Z, it sorts ABC Stores at the top. If I needed to figure out the total of ABC Stores, well that’s simple enough. Just choose those 4 cells and then look down in the status bar in the bottom right hand corner where I see the sum is 54,048. I think on your computer you have sum, count and average down there but if you right click on sum, you can turn on all 6 statistics. Average count, numerical count, min/max and if you do that once, it stays on forever. There’s certainly room for it all down there. That allows us to answer a lot of questions. We’re going to choose all of column G and we’re going to see down there the sum is 6.7 million dollars, largest order 25,000 and smallest order 1704. Numerical count, 563. Because of the heading, 564 cells and the average 11914. We found out how to find the total for just this one customer but what if I needed totals for every single customer? It’s really tempting, particularly when I only have 27 customers to insert new rows. I come here, insert new sheet rows. Pull that data down, that was control + apostrophe, pulls the data down and I type Total. Apply an auto sum too so that’s 4 cells and then just keep repeating. And I’ve actually gotten to the point where I can do about 2 or 3 of these a minute so I can solve this whole thing in 9 minutes. There’s a much, much faster way to go and it’s been in Excel for 20 years back here on the data tab. It’s called sub-totals, subtotal command. You have to make sure the data is sorted by customer first. If it’s not, subtotals will get at it but it’s just a disaster. Sort the data by customer, choose the subtotal command and then pay attention to these choices because they always see each change in the leftmost column. They want to add a subtotal after every change in region. Open that up and change it to each change in customer like that. Then, the sum function. There’s actually 11 functions here, I’ve never used anything other than sum. They’ll sometimes default to count if the right most column is text. It’s very subtle what they choose, sum or count. They’re there if you need to change it. Each change in customer, use the sum function. Choose the numeric columns quantity, revenue, COGS and profit. They offer page break between groups, that’s a choice. I guess if I was creating a PDF file, that would make a lot of sense. I’m just going to leave this one the way it is. When I click “okay”, Excel is going to do what I had started to do there by inserting a new row between ABC Stores and AT&T. They’re going to bring the ABC Stores text down and add the word “Total”. They’re going to bold cell E6. They’re going to add totals to each of those customers. It’s going to happen in 2 seconds or else. It’s not just that one that they did. I’m going to scroll down and see that on the next screen, we’re going to see AT&T Total. I’ll scroll down another screen and another screen. Some of the smaller customers, now we can see them. Cummins Inc., CUNA Insurance, Exxon…there’s a total row. Every time there’s a customer change, they inserted a new row of totals. I’m going to press control + down arrow, that moves me down to the bottom of the dataset. You see that after Walmart, my last customer, the grand total in row 592 appeared. The grand total is hard because it has to ignore every other subtotal along the way but that subtotal function, that magical subtotal function, same thing because it’s just the visible rows, will also ignore all the other subtotals in the range. It works. If I manually added all those totals in, I would’ve had to use some sort of accounting trick. Usually sum everything then divide by two. It’s just one of those ancient accounting tricks. Just being able to add these totals in is a huge time saver for someone who has been doing it the old fashioned way.
I want to call your attention over here to the left hand side of the screen. Three new buttons. These in Excel are called group and outline buttons. They’re added automatically by the subtotals command. When I click the number 2 button, this is a beautiful view of the data. They take all the detailed rows and they hide them, giving you just the customer total row.
This next trick came to me from Derrick in Springfield, Missouri. Derrick said “this is fine but your report is alphabetical. Nobody wants to see it alphabetical, they just want to see the largest customer on top”. I agreed with Derrick, I said “yeah, you’re right”. But we have to sort the data somehow by customer and there’s no way to get Walmart to come to the top. So here’s an amazing things in Excel. When you have the group down to the number 2 view and you sort the data by revenue, Walmart will come to the top of this. But it’s not going to come to row 2, it’s going to come to row 63 or something like that because behind the scenes when they brought the Walmart total to the top, they attach to it all of the Walmart detail records. Right now we’re in row 67 and this formula is pointing to rows 2 through 66. When we go look at the number 3 view, it brought all of those Walmart records. It didn’t sort them, it just came as a single group. A chunk of records. After that, General Motors. Look at the General Motors total at the bottom and so on. Completely crazy that you’re able to sort this data after adding the subtotals in.
One more “gotcha” here with the subtotals that I wanted to talk to you about. Now that we have this report, I print this out, give it to my manager and he says “hey, that’s great. Just send me those 27 records”. And this is so frustrating because if you try and copy this data, control + C, you’ll see the marquee is around the whole thing. Then when I go to a new workbook with control + N and hit paste, control + V, what I’m getting is not just the 27 rows, I’m getting all of the data. All of these Walmart records and then Walmart totals down in row 67. Remember the day that my manager stood behind me and said “I did that”. This was right after subtotals had come out, it was 1998. This is really unfortunate that I can’t copy that data. That manager doesn’t know it but at night, I’m already moonlighting. I’ve started Mr. Excel, I’m answering people’s questions around the world and got stuck by this. This guy is the most annoying manager in the world and he said “of course it didn’t work, you didn’t use visible cells only”. I said “what’re you talking about?”. Back then, it was hard to find the visible cells only box. You had to use the go-to dialogue box. In the lower left hand corner of go-to, you had to click special and then inside of special, on the right hand side, visible cells only. This bizarre command that’s hidden 3 levels deep that somehow my manager had figured out. I remember asking him where he found it and he said Excel Help. When you choose visible cells only like I just did there and then copy, you can see the marquee is now around each individual row which is saying there’s hidden things in the other rows which we haven’t copied and I go to a new workbook now, and then paste. I get just the total row. If you’re going to use this a lot, it’s much easier to add it to the quick access toolbar.
Excel is a funny thing, there’s three different ways to hide things. You can hide things with the group and outline buttons, you can hide them with the filter and you can hide them by hiding. If you hide them with filter, it will already be excluded by the selection. But the other two ways, you need to select invisible cells which is why people are frustrated. The rules change depending on how you go to the hidden rows.
Pivot tables weren’t invented at Microsoft, they were invented across the country in Cambridge, MA for Lotus 1,2, and 3. It was the category killer. It had 89% market share. If you were in accounting in the 80s, you used Lotus. Microsoft was there, they were slow but I love how Microsoft takes a long view of this. They said they will eventually catch up and they did. This feature was called Lotus Improve but when Microsoft borrowed it a few years later, in 1993, they had to rename it to be a “pivot table”. Pivot table will allow me to take a large dataset. In this case, 500 rows but it also works with 500,000 rows and I can create a one page summary in just a few clicks. So if my manager comes to me and says I need to see total revenue by region and product, I can create that report in 6 clicks. So the first click is go to the insert tab. Pivot tables are the first category. First icon in the insert tab. Make sure you have one cell in your data selected. Insert pivot table. They detect the edge of the data, A1 to I564. They offer to go to a new worksheet. In real life, I go out here to column K. I want to be able to delete this so new worksheet is fine with me. And we’ll click “okay”.
This is why pivot tables are so intimidating to people who are new to pivot tables. We have a big blank screen here over on the left hand side, the area where the reports going to go, a list of fields and then 4 drop-zones. Filters, columns, rows and values. And what is really frustrating if you are a pivot table pro, someone who uses pivot tables all the time, somewhere around 2016 the Excel team reversed these icons. So this icon next to the columns with the dark gray in column A is the wrong icon. That icon belongs down in here in the rows icon. The rows icon has a dark gray along the top of the report, that belongs in the column icon. Those icons are supposed to help you figure out which ones you’re supposed to use are wrong. If you’re new to pivot tables, don’t feel bad, it’s confusing.
I’m going to choose revenue. Because it’s numeric, it goes to the values area. I’m going to choose region. Because it’s text, it goes to the rows area. And my last click, my 6th click, will be to take the product field and drag it to the columns area. You’ll notice that the data is never formatted as currency or even with commas. It always comes up as general so right click and go to number format and choose whichever format you want. Go to number, thousand separaters, zero decimal spaces, click “okay”. There’s nothing we can do to get those correct. These words, sum of revenue, product and region. Sum of revenue, that’s just a weird way to talk. You can give it a new name like total revenue or you can even call it “sales”. That works but the one thing you can’t call it is revenue. If you try and call it revenue, they will say the pivot table field name already exists. Now, if you really want to use “revenue”, just type revenue and space (bar). Revenue space (bar) is different from revenue and they’ll let you get away with that. It’s one of those great accounting tricks we do all the time.
You print this out, give it to your manager and your manager says “no, it’s not what I wanted. I wanted regions going across the top and products going down the side”. This is where the word pivot comes in because we’re going to take this report and we’re going to twist it. We’re going to make the products go down column A and regions go across row 4 just by taking the product field and dragging it to rows. Then taking the region field and dragging it to columns. Very, very easy to change the layout of the report just by dragging fields around. Take this, give it to your manager and he says “that’s great, now instead of products on the left hand side, can you show me customers?”. Absolutely. Uncheck product, check customer and I have a completely new report. Print this out and your manger now says “wait a second, Cummins Inc., they’re in Indiana, there’s no way we should have any “West” sales. This must be miscoded, where did that 158,000 come from?”. All you have to do is double click [that], this is called the drill down feature and you get a brand new sheet to the left of the pivot table. Now I’m on sheet 3, pivot table is on sheet 2. This shows where that 158,000 came from. It’s great for answering those ad-hoc questions. To get rid of that report, just undo (control + Z). You can double click as many times as you want and get a drill down report.
I have a couple of cool pivot table tricks and one of them involves “date rollups”. I’m going to put daily dates on the left hand side of the report, get rid of regions going across. Now we have daily dates. There’s going to be 458 dates here. Days we invoiced. I want to create comparing this year to last year and I want it to be a monthly report. Choose one of the very first dates, it doesn’t matter which one. Go to the analyze tab. This is the first pivot table tab in this awesome field called “group field”. I start on a date, I group field. I say I want to roll this up to months and years. Click okay. Those daily dates have been replaced in those two columns by years and dates. But what I love, if you look over here in the pivot table area, it’s now two fields: years and date, which was brilliant that they made it two fields. I take the years field and I drag it to columns. So now for every month, I get to see where we were for last year, where we are this year. I’m going to right click and get rid of the grand total. What I really want to see out here is percentage growth. So I’ll say “% Growth”. All right, here’s a bug. This is a bug. Microsoft says this is a feature but I think it’s a bug. Anytime you’re outside of a pivot table, pointing to cells inside of the pivot table, I don’t just get the formula (for C5), I get this horrible “GETPIVOTDATA” thing. So this is one case when you don’t want to use the mouse, just type “=C5/B5-1”. Format that as a percentage, one decimal place and double click to copy it down. For each month, we see where were last year, where we were this year and whether we’re up or down that month. And now that I have this year over year thing working, I can take for example date out and put product in and we’ll see for each product whether we’re up or down. We’ll have to do something to hide those errors, maybe some conditional editing or just add or delete those extra rows as you need to. Let’s take product out, put customer in. I now have a nice little year over year report that I can put anything in on the left side and see where we were last year versus this year.
Slicers are brand new. First in Excel 2010 and then improved in 2013 with the timeline slicer. This allows us to filter our pivot table. Make sure you’re in the pivot table, insert a slicer based on sector. Click okay. And if I want to see the retail sector, I’ll just get the retail customers or just the manufacturing sector. And again, because GM is in my list, GM, Cummins and Ford, they came to the top of the list. You can choose multiple of these. So communications, financial and healthcare…so you can build a report however you’d like. The idea here is if you gave this to your manager’s manager on a touch screen, they could actually touch the tiles and the slicer and you’d be able to quickly filter the report.
I want to touch on a brand new feature in Excel called “Power Query”. Impossible to find. It was one of the greatest marketing blenders of all time, the way that they introduced power query. It came out between Excel 2013 and 2016. At first, it was an add-in so you had go out and download the add-in. A lot of companies won’t let you download add-ins. A lot of people never heard about it. But the Excel team had heard about it, the Microsoft team had heard about it so when it came time for Excel 2016 to come out, they said we’re going to add power query back into the product. We’re going to put it on the data tab where it seems it belongs but then rather than calling it power query, they used this horrible name of “get and transform data”. In Office 365, they actually moved this, “get and transform data” and what used to be here is the identical icons where you can data from the web, data from the original file. They took icons that used to be there that were very unpowerful and they replaced them with super amazing features that are incredibly powerful with the same names, same icons and something very bizarre called “get and transform data”. This is just crazy how powerful these icons are…we can get data from anywhere. From a file, from a workbook, CSV, from an entire folder. You can load all of your CSV folder. Azure, everything. Platform independent. From online, from sharepoint, dynamics, Facebook, Salesforce and then anything with an OBCD connection. All kinds of places. What I have here is just a table. I’m going to format this as a table, make it officially a table. Go to Home, format as table and you’ll see this is a real example that someone came to my seminars with and they put all their website sales in column E. He said “I need to take just that data, it can be anywhere from 1 product to 16 products. I need to take column E, I need to twist it sideways”. This order for Central, ABC, January 4th, transportation…”I need all that to get copied down as we put watermelon one row, and then fig, cherry…” and so on. Amazingly, text to column has been around for 30 years, it can’t do this but power query can. I’m going to get the data from a table. It always takes a good 10 seconds for power query to load up, it feels like your computer is about to crash, don’t worry. This is the power query editor that we see here, it was added in 2016. It’s on the Windows versions of Excel, not the Mac version yet. If you have Excel 2010 or 2013, you can go ahead and download this for free from Microsoft. What we have is tools across the top. Home, transform, add column and view. The transformation tools that are available in power query are better than the tools that we have in Excel. Here, I have my customers. Actually, that’s mislabeled, that should be product so I’m going to right-click and rename to change this to product. I’m going to choose that column. Split column by delimiter because there’s a semicolon in between each one. Then I’m going to say split as semicolon. Just the fact that they filled in semicolon, that’s brilliant. They looked at everything, they saw all the semicolon and they said “hey, I bet it’s a semicolon”. Back in Excel, they still don’t know, they make you say it’s a semicolon. Power query can guess a small thing. “Each occurrence of the delimiter”, that’s great. Then in advanced options, they have this thing that says “split into rows”. This first row, with a hundred of these, when I click “okay”, it’s going to get turned sideways and we get watermelon, fig, cherry, orange, guava, iceberg, elderberry…and then the next one, took all of those products and twisted them sideways. First off, that’s just not possible in Excel. Number 2: the things that we’re doing here in power query are repeatable. If I would’ve done data text to columns in Excel and tomorrow I get a new dataset, I would have to go through and redo those items. But anything I do here in power query they’re going to remember so I’m going to take the customer field and move it over here where product was and I’m going to right-click. I’m going to remove products, I don’t need that. Then here, this date, I don’t need to have the timestamp on it. Under transform, I just want it to be a date. Then if I want to convert those dates into months, they added essentially the same thing as flash fill called column from examples. I just come over here and call this Jan., 2018. Second example, they figured out what I’m trying to do is put in the month name all the way down and when we switch out here to February, it should update. Now I’m done, I want to do Home. Close and load. Right now, it’s going to insert a new worksheet and it’s going to take that data on the original worksheet and change it into the new format. They’ve taken the data, they’ve turned it sideways, they’ve broken all the products out and we now have two sheets of data. We have the original data which is on the datasheet inserted on this and over here, 5038 rows loaded. Here’s what’s awesome. Let’s say tomorrow we have a different data set. What I’m going to do is take this 100 and change it to “999”. I’m going to add a new product in front of watermelon, I’m going to add mango. Then, come back here to the results but nothing has changed yet. Right here, on the right hand side under the query’s panel, click refresh and they’ll go back and redo all of the steps that I did yesterday on the new dataset. The tools and power query are just crazy-how powerful it is.
I have two more quick examples to show. One of them is called “goal seek”. If you want to figure out the loan payment (ex.: trying to buy a house, new car etc.) and let’s say you’re trying to buy a 29,995 car (inserting data into B) for 60 months at 5.25% interest. The function that we want is the payment function. I love function arguments because it helps you build this one step at a time. I always clicked on rate and I would always get a really high payment and that’s because this should be a monthly payment. Used 6% divided by 4 for quarterly payments so I want to divide it by 12 and pay total number of payments, that’s the term…PB is the present balance, in other words how much are we paying for the car. I’m going to make this negative so my answer is positive. I build these answers here and I get 493.5. Other arguments aren’t required, they’re not bold so we don’t need those. Just use the defaults, we get our answer. That’s great except for my goal was to pay $425. I’m too high. I either have to pay more months or find a lower price for the car. Of course we can start guessing [puts random numbers in]. Instead of guessing, there’s a tool in Excel that will guess for you. It’s back here in the data tab. “What if” analysis and then goal seek. Great little tool. Set cell B5 at $425 by changing cell B1. Click “okay”. They didn’t just get it to the penny, they got it down to the ten-thousandths so 5 decimal places. Now I’m going to undo that [does another example with term and rate changes].
One of the truly geeky features in Excel is the “B lookup” function. B lookup is when you have a data table over here, customer and sector (over here) and I want to pull this back into this table. We insert a new column called sector. Some people do this with index and match, I prefer VLOOKUP because more people understand it. We’re looking up the customer data in this table over here. What we want is the second column in the table and we want an exact match, not approximate. Approximate match is going to be the wrong answer some of the time. And we have our fields. So that’s VLOOKUP. When I used to be a hiring manager and i’d hire a new data analyst who is going to use Excel 40 hours a week, VLOOKUP was my #1 question. Can you do VLOOKUP, can you do it with your eyes closed? If you get outside of the accounting department, people don’t like VLOOKUP because they only have the opportunity to use it once every other month so they don’t want to use, they just want to create a report showing total revenue by sector. This would be easy with Access, you could just put these two tables together. Starting in Excel 2013, we can now join 2 tables together. Make both of these be a table with Home, format as table. Name the tables. Insert a pivot table. And then this box, “add this data to the data model”. Essentially, it lets you create a linked table behind the scenes. That sentence is underselling how important that feature is.. don’t worry, this is going to take 15 seconds to load the first time you do it. Over here, all the fields. So we take revenue, drag it to the values area. Now I want to use something from the other table. At the top, this is brand new, I want to see all the tables. I choose sectors. Drag sector from the second table down to rows. They still don’t know how we should join these two tables with each other. They tell us up here and those are the wrong answers. A relationship is needed, I’m going to click auto detect. They figure out that customer is the field in common between them and we end up with our answer there. This is too different datasets probably on two different sheets. Your main data and your lookup table without doing a VLOOKUP between them. Behind the scenes, they join the two tables together. We get our answer in Excel. That’s my list of all of my favorite features.
Watch the on-demand recording of MrExcel’s Favorite Excel Tips and Tricks
