Excel Basics 20: PivotTable Power: 14 Amazing PivotTable Reporting Tricks: Slicers to Show Values As

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
Welcome to Excel Basics video number 20. Hey. In this video, we got to see the power of pivot tables. Now, we've already had four videos where we talked about pivot tables earlier in the class. But in this video, we want to see 14 amazing pivot table reporting tricks, from slicers to the amazing show values as. Now this is video 32 and the Office 2016 series. And of course, if you want to download this file or the 22 pages of PDF notes, you can click on the link below the video. Here's our 14 examples. And we're going to see examples like calculating average revenue by product category and country, connecting it to a slicer, which will filter the entire pivot table report. And then we'll connect the slicer to more than one pivot table. We'll see how to group revenue by year and month. And then we'll see the amazing show values as calculation to calculate things like change for each month, percentage change, running total. We'll even have two text data sets. One where we have phone purchased data. And we need to count how many of each phone was purchased and calculate the percentage purchased for each phone. Then we have a CPA data set, where we're going to calculate the percentage that fail or pass. And we'll cross tabulate those percentages to figure out whether or not taking a CPA prep course really helps. Now, we've had four videos already in this class. So I want to go look at each one of the videos. I'm going to click on video four sheet, just to remind us of where we've come from in this class with pivot tables. Now in video four, we just had a small data set. And we compared and contrast summits and pivot tables. We saw clearly that pivot tables are an easy and great way to make a calculation with a condition or criteria. In video number five, we saw that we can have different aggregate calculations like count, sum, and average. We also, very important, we saw that it's easy to create a cross tabulate a report and add a slicer with a pivot table. Also, the PDF notes for video four and five are epic notes for everything about the basics of pivot tables. In video 13, we saw how to use the IF function to add a helper column, which in essence, added a new category for our data set. And then we were allowed to use the entire data set, including the extra column to build a pivot table. And in video 15, we saw how to use the XL table feature with dynamic ranges. So we could add new data and create a pivot table from it. Now let's go to the sheet data analysis just to remind ourselves. We've talked about this many times. Data analysis, or also in the business world called business intelligence, what's the definition? We're going from raw data. And we're going to convert it into useful information. What do we need in order to create a pivot table? Well, we need a proper data set. And as we've talked about many times in the class, that means field names in the first row. Records and rows and empty cells all the way around. Now let's go look at our first example. I'm going to scroll over. And we're going to click on the sales data sheet. Now here is our data set we're going to build our first couple of pivot tables from. Date, product, country code, units, revenue, and cost of goods sold. Now, we actually already had a data set similar to this. And we used country code in the rows area of a pivot table. The problem with that was is it's hard to know from the abbreviation which country is which. Well, what we're going to do is we're going to add an extra helper column. We're going to look up the country code. And here's our look up table. Country code in the first column. Country in the second column. Once we use v look up and copy it down, it'll be much easier to read our report when we have the proper country name. Then we'll create a second column where we look at product price in this product table and return the product category. All right. I'm going to click in cell G4 equals VL tab. I'm going to use my arrow key to arrow over to get country code, comma. The table, I'm going to click in the top two cells, use my keyboard, control, shift, down arrow, F4 to lock it. Now I've got to type a comma. Column index, 1, 2. The second column has the country. So I type a 2. We're using exact mass. Because we're looking up exactly that text. So I either put false or zero. Close parentheses. Control, enter, double click and send it down. Now I'm going to control, down arrow to go down to the bottom. Wow. Look at that. 73,000 rows down. F2. There I'm just verifying the cell references. Hit escape. I'm going to use control, home to jump back to cell A1. Now we click in H2 equals VL tab. Left arrow to get the product. Comma. The table, I'm going to highlight and hit the F4 key. Comma. I'm counting on my fingers. 1, 2, 3, 4. Product category is in the fourth column. In the fourth column, 4, comma, 0 for exact match, close parenthesis, Control-Enter, double-click, and send it down. Control-Down Arrow and F2. We just verified the cell references. Then Escape, Control-Home. Now we have Data, Product, Country Code, Units, Revenue, Cost of Goods Sold, Country and Product Category. Now we're actually going to use these two fields, along with Revenue, for our first PivotTable. Now I'm going to click in a single cell. And our PivotTable's going to go on a sheet that already exists, called PT1. So I'm going to click in a single cell, Insert Pivot Table, or use the keyboard, Alt-NV. Now it got the data set right. I want to click on an existing sheet. And when I select that dialog button, I'm allowed to go down and click on PT1. You can already see the sheet reference there. Now I'm going a select A7. There it is, A7. Click OK. And here's our field list, and look at that. There's our Country and Product Category. Those are the two columns we created with VLOOKUP. Now I'm going to drag Country down to Rows. And as always, when you drag a field down to rows, instantly in the PivotTable, we get a unique list, one of each item from that column. Now I'm going to come over to Product Category. And for the time being, I'm going to drag it to the Columns area in the task pane. Instantly, for the columns, I get a unique list of items from that column. Now I'm going to drag this over here. I want to now drag Revenue down to Values. And in the Values area, instantly I get a calculation with two conditions or criteria. And the conditions or criteria for that calculation, $22,364.97 is Beginner category and Bahama's country. Now column labels and row labels are not good labels in a report. Design, over to Layout, Report Layout. Show in Tabular. I'm immediately going to click in the Values area, right-click, not format cells. Number Formatting, because we want to add number formatting to the actual field, not the cells. I'm going to select Currency. And for this report, I'm not going to show a dollar sign, because it will be very cluttered with so many numbers. And I'm going to, since this is a high-level report, display 0 decimals. Click OK. Now I'm going to drag this over to the side. Now this report is nice. Any intersecting cell I can clearly see at the top of the column. There's the criteria, Freestyle. And at the head of the row, there is the country, Colombia. But really, I would like a more concise report, where I see for each country a vertical list of the Product Category. So instead of having Product Category in columns, I'm going to click and drag. And this is why they call it a PivotTable, because I can drag it below Country. And instantly, I have pivoted my report. And that's what I'm after, the country, and then each one of the product categories, and the sum of revenue total. Now I want to come up to the top here. Hit F2 to put it in edit mode and put my cursor at the end. Then I'm going to indicate the unit, space, open parenthesis dollar sign, close parenthesis. Enter. I'm going to come up and change the column width. Now it's very common in reports to indicate the unit at the top, because then we don't have to clutter up the inside of our report with the unit. Now sum of revenue is great, but we don't really want sum of revenue. We have in a PivotTable 11 different aggregate functions that we can use to make calculations. And there's a list in the PDF notes. There's actually a list right over here on this sheet, summarized values also. Now I'm going to right-click in the Values area. Go down to Summarize Values By. That's where we change the function. And in a PivotTable, when you right-click, there are two different locations to change the calculation. Summarize Values By, that has aggregate functions. And all 11 of them are available in More Options. And then as we'll see in PivotTables coming up later in this video, Show Values As, with a bunch of amazing calculations. All right, so we're going to try it, Summarize Values By and Average. Now look at that. It even changed it, Average of Revenue. And it kept our dollar sign that we created there. Now the next thing I want to do is look at PivotTable styles. Now lots of times, I just keep the default, because a lot of these styles up here don't look very good. But I'm going to click the More button for styles. And you can preview. Look, that one there, that one's ridiculous. How about this one right there? That's terrible. Now there's a few of them that look OK. I want to select-- in the first row up here, if you look over the side, the white font and blue background, the value difference isn't big enough. Same with this one right here. But this second row right here, I like that. The green with the black font color, in particular, looks good. So I'm going to select this one right here. Light green, pivot style, medium, 14. Actually, I just learned about this cool style [INAUDIBLE] someone in the class the other day, [? Reesa ?] said she liked this one. So we're going to use that one right there. Now the last thing we want to do to this is we want to name our PivotTable. Now we've learned how to name Excel tables in an earlier video. But we want to name our PivotTable, because we're going to have multiple PivotTables. And we're going to want to connect them to a slicer. So I'm going to, up in PivotTable Tools, Analyze. Go over to the PivotTable Tools, and there it is. You can click up in the PivotTable name. And I'm going to type something like Country Report, and Enter. Now later, we'll see that that name will come in handy. Now for this particular PivotTable sheet, we're actually going to create a second pivot table, add a slicer, and then connect both PivotTables to the slicer. But before we do that, we're going to go start our second PivotTable report on the sheet, PT2. Now there's nothing here yet. And we're going to start over on Sales Data. We're going to click in a single cell. We're going to use our keyboard, Alt-NV. It got the data set correct, because we have a proper data set with empty cells all the way around. I'm going to click Existing. Click on PT2. And click in cell A9. There I see A9. I click OK. Now we're going to see something amazing here. That is the Date field. And I want to go back over to the sales data and notice this data set, as well as most data sets that we get that contain transactions from sales, those transactions are almost always encoded with a date, sometimes with a date and time. But notice, dates are fine. In fact, we might want a daily sales report. But oftentimes, we want month, quarter, and year. So remember, those are individual days. If we go back over to PT2, and watch this, that whole Date column is filled with days. But in Excel 2016, when you drag Date down to rows, it will automatically group by year, quarters, and date. Now what this means is that when we drag Revenue-- in fact, let's drag Revenue down here-- we are allowed, from those individual dates to now add by year, quarter, and date. Now we don't want quarters for this report. So I'm simply going to click and drag it off. Now we're left with years and date. Date is really going to be our month. Now I'm going to come over. And in the row area, I want to right-click and point to Expand or Collapse. And then I want to Expand Entire Field. That way, I can see the year and the months, the year and the months. That's absolutely beautiful. Now one thing before we move forward. Sometimes you actually want the individual days. You can actually right-click and point to Ungroup. That's if you actually want the unique list of days in the rows area and then sum up by day. But we're not going to use ungroup right now. So I'm not going to click that. Escape. Row labels is not good. So I'm going to go up to PivotTable Tools, Design, Layout, Report Layout, Show in Tabular, or Outline. Both of those will show the field names. So I'm going to click that. And then I'm going to right-click Number Formatting, Currency, 0 decimals, and no dollar sign. Click OK. Click in the top, F2, space, open parenthesis, dollar signs, close parenthesis, and Enter. Increase the column width. With the cell selected, I'm going to go up to the More button and click light green, pivot style, medium, 14. Now we'll come back to this PivotTable and add some extra columns and see some amazing Show Values As calculations. But I had to come and create this PivotTable first, because I actually wanted to make sure that we grouped so that we had this new field, Years, because I want to go over to PivotTable 1. Drag this to the side. And with a cell in our PivotTable on this sheet, I want to add a slicer. Now a slicer will be a list of the years over here. And I'm allowed to click on the year. And it will instantly add that condition or criteria to the entire PivotTable report. Now we're going to go over to PivotTable Tools, Analyze. And in the filtered group, we're going to insert a slicer. Now when you insert a slicer, you can pick whichever field you want from your data set. I'm going to check Years. And it will add a list of all the unique items from that particular field. Click OK. Now we'll fix this up in just a moment. But what a slicer does is, right now, all the years are selected. That means every cell in the PivotTable table is for all the years. Now when I click on 2016, that means every cell in this PivotTable is for only the year 2016. Now when you use either a filter right here-- and we saw this back in video number 5-- or a slicer, filter or slicer, every single cell in the PivotTable gets that condition or criteria. Now if we compare and contrast rows or columns, that means that for each cell, as we're moving down, the criterias and conditions are changing. In that cell right there, that has three conditions or criteria. It's for the product category, Freestyle, for the country, Algeria, and the year 2016. When I go down to the next cell, the conditions change. Well, at least, product category did. This is the average revenue for the product category, intermediate, in the country Algeria, in the year 2016. Now when I come down here, 2016 still applies. But now the category is beginner. And the country is Argentina. So slicers, they affect every single calculation in the pivot table. Now we need to click on this, because we need to get rid of these ghosts here. So I'm going to right-click and point to Slicer Settings. And over in Slicer Settings, here it is. Hide Items with No Data. I'm going to check that, and click OK. They're gone. Now I can resize this, drag it to the side. Up in Slicer Tools, Options, if I want, I can add a consistent color. I'm going to add slicer style. Now we want to add a PivotTable right off to the side in F7. But we're going to start it off by going back to Sales Data. Click in a single cell. Alt-NV, Existing Worksheet, PivotTable 1. And I'm very carefully going to try and click in F7. Click OK. Now here we want to drag Product down to Rows, Revenue down to Values, get rid of row labels. Design, Layout, Report Layout, Show in Tabular. Right-click, Number Formatting, Currency, 0 decimals, no dollar sign. Click OK. Click in Sum of Revenue, F2, space. And I'm going to add the unit. Enter. Increase the column width. Go up to PivotTable Style. Select that consistent style. Now we're going to learn a great trick. We have sum here. And over here, we have average. Now we're totally allowed to have multiple calculations on the same field in the same PivotTable. So in PivotTable fields, I'm going to drag Revenue down to Values for a second time. I'm dragging it below sum of revenue. And there we go. Now I can come over, right-click, Summarize Values By, and we're going to change it to Average Revenue for Each Product. So when I click Average, there's the calculation. Now I want to display these with the same number format, and I did over here, and is over here. Right-click, Number Formatting, Currency, 0 decimals, no dollar sign. Click OK. Click in the top cell, F2, space, parenthesis, dollar sign, space. Enter. Now I'm going to drag this so we can increase the column width. Now notice every time we drag a number field to the PivotTable Values area, it defaults to sum. Now let's try-- because we want to count how many transactions we have for Alpine, and Aspen, and [? Bellon, ?] for each one of the products, because we want to count, I absolutely could drag Revenue down there and then right-click and change it to COUNT. But if you drag a text field down to the Values area-- let's do it. I'm dragging Product down to Values. It will default to COUNTA. And then we can see there, it says Count of Product. It's counting how many transactions in the data set set were for the product, Alpine. Now I'm going to right-click the top cell, Number Formatting. I'm not choosing currency, because this isn't currency. We're counting. I'm going to include 0 decimals and use a common separator. Click OK. Now the number formatting looks the same here. But that dollar sign means these are dollars. These are just counting. The word count means integers. We're counting 1, 2, 3, and so on. Now let's come over-- this is exciting. I'm going to use my slicer. Oh, but look at that. When I click on the slicer, it's only affecting this PivotTable. Now before we connect this, let's come back to this PivotTable. We want to come up to PivotTable Tools, Analyze. Over to PivotTable Tools, click in the Name box. And we're going to name this Product Report, and Enter. Now we can click on the slicer. And we want to connect both PivotTables tables to the slicer. So I can right-click, Report Connections. And there's our list of PivotTables. We already see we made a mistake on that other PivotTable. On PivotTable 2 sheet, we forgot to name it. But the reason we name it is because many times, you have lots of PivotTables in one workbook. So we got to name them to know which ones to check. All right, so we're going to connect Country Report and Product Report to the slicer, and click OK. Now check this out. When I click 1716, instantly, both PivotTables are updating. That is amazing. Now we're finished with this sheet and this double PivotTable and slicer report. Now I want to go back over to PT2. All right, and the first thing we want to do here is click in a single cell. PivotTable Tools, Analyze. Over to PivotTable Tools, click in the Name box. And we're going to call this Monthly Report. And Enter. Now we have sum of revenue for each month and for each year. I want to add some extra columns and calculate the difference between. So the difference between each one of these would be February minus January. And so the difference needs to be down by $7,000. Then we need to calculate the percentage difference. And then format it as a percentage. Then we need to calculate the running total, which the running total always gives us, as we move from month to month, the total for the entire year so far. So those would be examples of the calculations we need to do. Now instead of doing formulas-- and I'll actually drag these off to the side just so we can see that, in fact, the pivot table calculations will calculate this correctly. By the way, if you try to do this yourself, you might not see cell references. You might see Get Pivot Data Function. And I have those turned off. So you would have to go to File, Options, over in Formulas. And I uncheck Get Pivot Data Functions for Pivot Table References. You can keep those on if you want. There's some good uses for them. But I have it turned off for this video. All right, let's click back in our PivotTable. And we're going to do the same thing. We need multiple calculations on the same field. So I drag Revenue down to Values. Come over, and we're going to right-click, down, not to Summarize Values By. But this is the first time we're going to see Show Values As. Now Percentage of Grand Total would calculate the percentage of every number, like in a cross-tabulated report. Compare it to the grand total. We'll see percentage of column total and row total in later reports in this video. But they just calculate the percentage of each item of the column and of the row. We'll also see Difference From-- that calculates the difference from period to period or from category to category-- Percentage Difference From, and we'll even see running total. Now we want, as our first calculation, Difference From. So I'm going to select that. And it says Base Field Date. I'm going to click Cancel, because I don't like that. I'm going to come right over here. This is a PivotTable. And we have our report layout tabular, so properly, it shows the date field name. And we can see that checked up here. But we're totally allowed, in our pivot table report, to type a new name. I like Month. Not only is it going to be more informative for our report, but Enter, it'll also help us understand what's going on when we use Show Values As. Right-click in the values area for the second column. Show Values As, Difference From. And now the base field is going to be month. That means we need to calculate the difference between each month and the base item. Now sometimes, like in some accounting reports, we want to base everything off of January. That means as the formula copies down, it will calculate the difference from each one of these items compared to January. But that's not what we want. We want to select Previous. That way, as we copy it down, it'll take April compared to March, then May compared to April. All right, I'm going to click OK. And that is amazing. It totally is calculating the actual amount of the change or the amount of the difference as we copy down. Now right-click, Number Formatting. I'm going to select the same number formatting to be consistent. Currency, 0 or no symbol. Click OK. I'm going to come up, and I'm going to create a new label here, Change From Previous Month, Control-Enter. Now I'm going to increase the width a little bit, and go to Home, Alignment, Wrap Text. Now let's click inside the PivotTable. And guess what? We're going to drag Revenue down a third time. I'm going to try and move this PivotTable field list. By the way, you can change the dimensions of the field. It's kind of hard. You have to really point carefully. There it is, sum of revenue. Right-click, Show Values As. And we want Percent Difference From. Same thing, From Month, that's our base field. Base item, always look at the previous. Click OK. It actually calculated the percentage change and added number formatting. By the way, we can see over here that our calculations by formula or by PivotTable are the same. Now we're going to drag Revenue a fourth time. Drag the PivotTable fields off to the side. Right-click, Show Values As, and down to Running Total. Base field is month. What that means is it will calculate the running total for this year. But then when it gets to the next set of repeating criteria, it'll calculate a new total. If I selected year, it would actually calculate a running total for all three years, which means the running total will keep going until it got to the very grand total. But that's not what we want. We want month. When I click OK, that's totally amazing. And we can see over here, we got exactly the same numbers. It's totally adding as we go along. And of course, December equals exactly the grand total for the year. And when we look down here, it starts over. And when it gets to December, that's the yearly total, as we can see right here. Right-click, and Number Formatting. And I'm going to be consistent. Currency is 0 and none. Click OK. Now I'm going to change the labels here. Percent Change tab, running total in parenthesis, the unit. Control-Enter, and do some wrap text. Now that is pretty amazing. We did not have to create a bunch of formulas. But we're totally allowed, first off, to group by year and month. That would be quite hard to do with formulas. You have to use SUMIFs with an upper and lower limit for each month. But in a PivotTable, we just drag and drop the date. And it does that automatically. Then we did Change From Previous Month, the percentage change and a running total. All right, now we have two more amazing PivotTables to create. I want to go over to the cell phone data sheet and look at our data set. It's a single column. These are phones purchased. So it just lists the actual name of the phone purchased. Now this is very common, where you have a single column, where you could have automobiles purchased last month, a particular set of survey responses to a question. But you have a single column. And all we want to do is count how many of each are listed and figure out the percentage of each particular item. Any time that's the case, we can do a quick PivotTable. Now I'm going to click in a single cell and use the keyboard, Alt-NV. I'm going to put this PivotTable on the existing sheet. I'm going to put it in C6. Click OK. Now here is our field list. And look, there's just one field. But here's how easy it is. You drag the single field down to rows. And instantly, we get a unique list. Then you drag Field down to Values. And because it's a text field, it defaults to counting. And instantly, we have our count for each one of the phones. Then we can drag the text field down a second time. Come over, right-click, Show Values As. And we're going to use Percent of Column Total. Now what does that mean? That simply means, well, there's a column total, 559 items purchased. It'll take each one of the numbers and divide it by 559. So when I do Show Values As Percent of Column Total, instantly, it not only calculates the percentage, but it adds number formatting for us. Now, of course, 100% at the bottom, because 559 divided by 559 is 1. And then formatted as a percent, we see 100%. But look at that. Each one of these items is expressed as a percentage. We can see Samsung Galaxy S8 was the most, iPhone, second most. Now sometimes, this is called a frequency table or a percent frequency report. This is the frequency. This is the percent frequency. And here's the deal. It's so common. You have a single column in business of whatever the attribute is. You've got your unique list and our percentages. Now in our case, we did a sample of 559. But if next month we're projecting 10,000 sales, we can take each one of these percentages, multiply it by that 10,000. And that would give us a good estimate for how many of each phones we expect to sell. All right, so this is a very common situation, a single column of text data, unique list count, and percentages. PivotTables are the way to do this. Now let's go look at our next example. I'm going to click on CPA Data. Now here we have scores from a CPA test. We have a column that tells us whether they took a preparatory course or not. Now this is the same column. It just has a longer label. And this column says whether they passed or failed. Now if I Control-Down Arrow, this is like 10,000 records. Control-Home. Now what I want is, I want whether or not they took prep course in the rows, pass or fail in the columns, and then calculate the percent. So I click in a single cell, Alt-NV. I'm going to put this on an existing sheet, try to click in F6. Click OK. Now I'm going to use the long label, CPA Prep Course, drag it down to rows. There is our unique list. Now I'm going to drag pass or fail down to columns. Now we have a crosstab. I'm going to drag Test Pass/Fail down to values. That gives us the count. You could see we had exactly 10,000 records. Now we need to express, for the row, this is they did not take a prep course, they took a prep course. I need to compare each one of those numbers, 5,175 and 1,000, and divide it over 6,175. Do the same for took a prep course and the same for every person in that data set. Right-click, Show Values As, and Percent of Row Total. Just like that, with a few clicks, we have our answer. Well, actually, our first answer is that, yes, the CPA exam is incredibly hard to pass. This is passing all four parts. And that might even be a good year. But now we have our answer for the people who did not take a prep course. Only 16.19% passed. 83% or 84% did not. But look at this. Here's the people who took the prep course. Only 39.22% passed. Almost 61%, even though they took the prep course, did not pass. I almost forgot column labels, not a good label in our report design. Layout Report, Layout, Show in Tabular. In fact, I better come back over to this one. Design, Report Layout, Show in Tabular. All right, so yes, the CPA exam is hard. But what's not hard is creating PivotTables. And in this video, we saw some epic examples, including this cross-tabulated report, where we did Show Values as Percentage of Row. Back over here on cell phone data, we saw the incredibly common single column with text. And all we want to do is get a unique list count and get percentages. Then over on sales data, we actually did VLOOKUP for country and product category, added these two columns to our data set, and then created PivotTables and used these fields. Over on PT1, here's our first PivotTable, Product Category and Country in the row, and calculated average revenue. Then created a slicer, which of course, filters the entire report. We connected the slicer to both reports. This report was product, with sum of revenue, average of revenue. And please count the transactions, the times we had a particular product. Then over on PT2, we saw the amazing Group By feature in PivotTables, perhaps one of the most beloved features in a PivotTable. We grouped by years and month, calculated total revenue, then saw Show Values As to calculate change month-by-month, percentage change, and even running total. Now if you like that video, be sure to click that thumbs up. Leave a comment and sub, because there's always lots more videos to come from XLS Fun, including the next couple of Excel basics. We'll see a couple of short, cool PivotTable tricks. All right, we'll see you next video.
Info
Channel: ExcelIsFun
Views: 91,766
Rating: undefined out of 5
Keywords: Highline College, Busn 216, Mike Girvin, Michael Girvin, excelisfun, Excel Basics, Learn Excel, Excel, Excel 2016, Learn Excel Online, Complete Lesson in Excel, Basic Excel, PivotTables, Power of PivotTables, Pivot Tables, Excel PivotTables, Excel Pivot Tables, Show Values As, % of Row Total, % of Column Total, Difference From, % Difference From, Running Total, Group Dates in PivotTable, Monthly Report, Yearly Revenue Totals, PivotTables are Easy, Learn PivotTables free
Id: Sv9-P50CGog
Channel Id: undefined
Length: 39min 50sec (2390 seconds)
Published: Fri Nov 17 2017
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.