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.

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 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.

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 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