Ultimate Excel Course #11 - Master PivotTables for Powerful Analysis and Reporting | Alan Murray | Skillshare

Ultimate Excel Course #11 - Master PivotTables for Powerful Analysis and Reporting

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
14 Lessons (1h 8m)
    • 1. Introduction

      3:20
    • 2. Creating a PivotTable

      8:09
    • 3. Changing the Values Function

      3:19
    • 4. Formatting Values the Efficient Way

      2:19
    • 5. Creating a PivotTable Style

      5:10
    • 6. Sorting PivotTable Fields

      2:36
    • 7. Grouping Data in a PivotTable

      4:55
    • 8. Show Values as Percentage, Difference and Ranking

      5:10
    • 9. Conditional Formatting with PivotTables

      3:41
    • 10. Filtering PivotTable Data

      3:59
    • 11. Refreshing a PivotTable

      4:48
    • 12. Creating a PivotChart

      5:22
    • 13. Using Slicers for Interactive PivotTables

      11:55
    • 14. Using the Timeline Slicer

      2:54

About This Class

bfe6cf15

This class is #11 in a series called the Ultimate Excel Course.

In this class you will learn how to use PivotTables in Excel. PivotTables are one of the most sought after skills in Excel, so learning to master their use will make you in-demand.

PivotTables are a built-in reporting tool. They allow us to simple, quickly and effectively summarise large quantities of data. It always amazes people how easy they are to use, when they use them for the first time.

We will show you how simple it is to create a PivotTable and get immediate insights into your data..

We will look at how to sort, filter, group and perform different calculations on your data in a PivotTable. We will also see how to apply rankings and compare data to other fields such as previous years.

Slicers are also covered in the course to allow users to interact easily with the PivotTables and charts to see what they want.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enrol and let's do this.

Please note that this is class #11 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Function and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot

Transcripts

1. Introduction: Hello and welcome to Class Number 11 off the ultimate Excel course, where we look at using pivot tables in Excel for powerful analysis and reporting. Now pivot tables are one off the most loved features Off Excel, and there is a good chance that even if you personally haven't used him before, you have seen or heard of the mentioned. They are a built in tow for reporting and analysis, so you don't always need to go through complex formulas to get what you want. Now what are we going to cover? What we need to start with had to create these pivot tables from your source data. We're going to create our pivot tables from data that's in a table was covered on a previous class. It doesn't have to be that way. You can create them from normal ranges, but as the approach we're having, because it's the most efficient way of doing it with their knitting rattle through lots of videos showing you how to change the calculation you're performing had to sort data, format it, filter it, group it by different numeric fields on lots of useful ways to shape and transform the way that you report is presenting his findings. We also need to look at comparing a ranking values because there's lots more you can do than just just standard some in in counting with pivot tables. So we look at how to calculate percentages of columns, present differences and comparisons ranking by multiple columns. There's lots of useful built in options, no formulas needed here, guys where they need to look updating it because there is a good chance that your source data will change over time. And as that changes, you will need your report slash pivot table toe update. With the new and fresh data, we're going to create a pivot chart because everybody loves a chart. And why just present your findings for a table as good and useful as they are when a chart camp? If I'd better viz on understanding off that data and also slices air coming up in this class, another very much loved feature Off Excel. It's your modern feel too tall for fast, simple, powerful interactivity so that users, including us, can easily interact with their dashboards and reports choosing options on having the tables and charts react to their selection and to show the data that they want to see, so that must sound good. If you want to learn pivot tables on any Excel user should have the skill under their belt . Then grab yourself a beverage in role. Now on, I will see you in their class. 2. Creating a PivotTable: hello and welcome to this lesson on creating pivot tables. Now, pivot tables are one of excels, most well known on loved features. They provide a powerful yet very simple way off analyzing and summarizing huge quantities of data. And as an example of one here, I wanted to use the data from the previous lessons on formatting ranges as tables. So I've got my table here on screen, which to remind us, is called sows. I can see that in the top left. Now I have this cells table with the style where it was created. We're now going to summarize and view some reporting data on this. So to create al pivot table, we will just click somewhere within the table and click on the insert tab off the ribbon and then pivot table. You do get a recommended pivot tables button from Excel 2013 and that just helps newbies to pivot tables, get a feel for what they're all about. So if I was to click recommended pivot tables right now, I get some examples off this data set presented as a pivot table. So here we've got some of the unit price column by country here got a some of the total sours and follow you column by country. Here. We've got one that's condensed with the country's and averaging discounts. It just shows you how it's summarizing most of these by country in a moment. Here's a Category one. He's a South right one, and I'm going to cancel this because I don't want to use that. But it's a nice starting place at for newbies. If I click the pivot table button so we can create our own one, it would immediately pick up the sales table Data set. It prompts you for the table or range, but it picks up sounds table for us. Would we like on a new worksheet? That's probably a good idea. There's a lot going on on this worksheet, but we could get pivot table data from an external data source. We're going to talk about a data model in some later lessons off this course, so save that for then and we can all support the pivot table report, as they call it on existing worksheet that we have in this spreadsheet. At the moment, we do not have on have a worksheet, so we're going to leave. It is new worksheet and click OK that will create a new work it for us called Sheet one, which you could happily rename if we wanted to. And we've got the pivot table on the left hand side, and we have the field list on the right hand side. If, for any point you click away from the pivot table and somewhere else onto your sheet, you'll lose the field list on the right and we can bring it back by simply clicking back on the pivot table on a pivot table will not look like much. Yet if you have not created one before, you will not be very impressed yet. But using this field list on the right hand side, If I was to tick the box for country and then tick the box for total sows value now it's beginning to look a bit more impressive as it immediately summarizes over the country. Sows Andi sums them is easy as that, just replicating one off the demonstrations they showed in the recommended pivot table feature. So that was extremely quick and easy to get exactly what we might have been wanting, and that is why people you know who need this kind of stuff, that a report sort of Data Analytics. I will talk very passionately about pivot tables and how much time they save them and how useful they are now in that field list on the right, you may notice that when we take to those boxes, it's simply poor country into the rose area because country is text, and we didn't tell it what to do with it. So Excel just defaulted to put it into Rose on with total sows value, which is a number it defaulted to put it into the values area, which is where the calculations happen. On it defaulted to summing them because that's the most popular calculation, but not always what you want. So we're going to cover had to change this stuff through the coming pivot table lessons, for now, something I wanted to focus on. To finish this less enough is that it encourages you to drag the fields between the areas. So these four boxes are the areas on. We will be talking and demonstrating all of them. For now, though, we can just click and drag that country filled out off the rose area and drop it onto the sheet to remove it, just like they encourage us to do with dragging the fields on. Instead, weaken going, get the products category filled and drag that into the rose area to get the sows by category instead off my country. So it was very simple to pivot this table around and to summarize the values by the field that we want, which is now product category. Now we can put as many fields as you want into any off those four areas. So I could, for example, grab the country field again and drag that into the rose area. And if I do that by zooming in on that area, you will see how, as a drag country into roads, how it visualizes with that green line, whether I'm going to drop it before or after the category field and if I drop it after the category field that is going to show the category in the pivot table, so sows off beverages here and in a south off those beverages within each region or country . But if I dragged product category under country, it would order it the other way and show sows for the country and the breakdown of those cells and by category within each country. So now we've got two conditions or two fields being summarised on weaken. Simply specify the order by the order is dragged into its position in the area. Now, if I remove product category but dragging it out, I can bring something else in, like custom and name and drag that in on. It's a simple Is this to set up the report How you want? So you just need a simple day to set like I have on the other sheets just a table of data, and you can put that into a pivot table and learn just by dragging the fields among these areas, changing their order and getting a little bit of a feel of how to work with them and over the coming lessons, we're going to explore Mawr Mawr off the features that pivot tables offer to dive into even more detail from their data 3. Changing the Values Function: Now let's talk about changing the calculation that is used because, as we saw in the previous video that the pivot table D thoughts to some in a numeric value . If you were to put text in that values area, it would have defaulted to counting them. So we've got the some off those values. But maybe we wanted account. Or maybe we wanted an average. Now there are different ways of doing this. That the approach buyer normally take is just a right mouse click value within the pivot table to go to summarize values by and to select the function that I want from here. So here's account his at average. We could even go to MAWR options to see the full complement of functions that pivot tables provide. Which is not that many, to be honest, nowhere near the expansive ways that we can analyze data with formulas cancer that box for a moment. But it's normally or wouldn't you need. So I just switched that to account. I can get the count off the orders rather than the total off them, with some off them. Except how many Robert and how much in this data set so in Argentina are conceived from the three customers we have that the 1st 2 placed 11 orders. The last one placed 12 orders. One more Bolivar was to switch that back to a some. Then I would see that it's actually the second customer who have spent was received the most money from. And although it's 12 orders is actually about £600 less in value, I could switch that to an average, which will probably look quite bad here right now because of the volume of decimals they will show. So we're going to look at format in these values in the next lesson. But for now, I'll just switch that back to a son. Another thing I want to mention just before I conclude this lesson is that we've got the sum of total sows value in that values area. We could drag total sows value into that values box again. So it now in there twice and both of them some by default. But I could right mouse click of value in the new column. Summarize it by count. So I've got both. I do have a some and I do have a count in addition to that, these headers at the top, Converium labeled. You don't have to keep these quite nasty headers hair that they've got there. You can type over the top and every label with those. We're not going to worry about doing that now because we're going to change this pivot table orbit over the coming lessons. But if this was to finished article, I would definitely format in these values on. I would definitely changing those headers and possibly even applying a different style. 4. Formatting Values the Efficient Way: now we really need to format the values of this pivot table. It's very misleading at the moment, the way they're being presented with the free values in Argentina all showing different number of decimals, it would appear that the 3rd 1 is the largest when indeed it's the second. And until you look closely and see, they've got two decimals, making me appear larger. It's very confusing. Or to the two first. Customers in Brazil have the same look to them, so firstly, they are money, so they should be presented as it. But it's also going to bring clarity as we read the values. So to do this, I'm going to right mouse click value again and choose number format. Now, by choosing number format, it's going to format. All of the numbers in the pivot table is going to do them all on. As the pivot table grows and shrinks in size as data comes in and goes etcetera, it would automatically update Where's four map sales will only format. The cells have got highlighted on at the moment of only got one, and I could highlight column B and do format sales this way and that would do the job absolutely fine, but they are not the same. One of them is formatting. The physical cells on where was in no sales will get formatted number format formats. The Total Sours value field. They are slightly different without any click on number format. It's strangely take you to formats that helps, but they are different options. If I choose accounting and remove my decimal places, keeping a pound sign, we've got of writer symbols to choose from and make sure it's formatted in a way that makes sense. And it's easier to read, and I click OK and diseases that job is done. They're now consistent in their appearance on are presented in the right unit off measure. 5. Creating a PivotTable Style: In a previous lesson, we looked at creating our own table style, and that was applied to this table over here on this sheet with our companies color scheme being used for the head up. Now, maybe we want that again for a pivot table instead of this standard blue gonna head up on the nice blue underlying on each sub total. Head up. So what we could do is click the design tab at the top, where you can see a gallery off pivot table styles. And if I click the mawr button to expand that, I can apply any style that comes shipped with Mark Soft Excel or create our own new pivot table style Winterbottom. And if I did that, it would prompt us for a name. So maybe I'll cord it pivot standard. And then we conform at all the different elements off a pivot table style in whatever way we want, including the whole table. We could also said it as a default for any new pivot tables in this document, which is what I might be doing now just to do a couple of simple changes. For now. If I choose head Arrgh and click format and in the field tab and Mawr colors. And then Custom said, I can enter that specific RGB value for our fictional companies at Color Scheme, which is 24012 white, 1 to 8. With that done, I can okay on okay, gain. I could obviously change font or anything else that I may have wanted and move on. And former another area such as the sub Total Rows, which have got these underlines Save. I chose sub Total Row one, the only one that's being used right now Anglican format. On in this boarder section are my go into the color setting, select my color from the recent color list and then apply that as a border along the bottom so that when I okay, that will turn bold to indicate that it's been modified. I'm going to sit. This is the default for this spreadsheet and click OK. That style will now be in the gallery at the top me to choose from, and it's applied to this pivot table. At this point, we realize that we've made a couple of mistakes, and that would ideally like her headers to be bold. But the main one, but and also maybe the sub headers. We also realize that border has not come across. It's effectively removed the blue one, but we don't have the border that we wanted. So we could right mouse, click the style up in the gallery and go back to modify it at a later date. I can choose the header over, go into format and apply a bold front. And then as we looked down the list, we realize that I've applied it to the sub total row and maybe we're quite happy with that . Maybe that's something we may be using that later date, but I'm going to go into there and remove it for now. So we have no border, and instead what we wanted was the row subheading. It's very confusing of all the word sub. You can easy click the wrong one column subheadings roller subheadings sub totals. But if we format that into the colors, apply the border as before, maybe apply a bold front again, okay, our way out. Then the changes take effect. We have the bold headers, that bold sub headers and also that match in underlying color, and we can continue to modify a later date, however, as the company's image changes over time, or we may realize, as a pivot table may change or new pivot tables change how, where lacking styles potentially in other areas. 6. Sorting PivotTable Fields: when Adan Field to a pivot table by default, it will sort them by name so you can see how the country's ordered a to Zed by name and so other customers within the countries. But we may want them in a different order. We may want to sort the country's by the revenue from them. So to do that nice and easy weaken right mouse click a customer value choose to sort largest to smallest and that more sort each group of customers, not just the area that you write, clicked, but in this data, wherever it's Finland, Canada or Argentina, they are all sorted Within that country. We could do the same behavior to the country's I could right mouse click a country value such as the total for Canadian customers. Sort it large to small and all of the countries will move. So we have the USA a top followed by Germany, etcetera. But within the country's steal customers large to small as the pivot table maintains the sort that we asked off it. And that's important, especially if we're crying in pivot tables for our reports on at dashboards. The fact that we know that no only do the calculations update, such as the summing and accounting, but so does the sort in on a filtering on the group in and other things that were discussed during this part, of course. So that is really, really important. To know on is a difference, too, when we sort normal data where sort and filtering is a manual process on does not automatically update a standard fruit. These arrows pivot tables. Do they are your own in one reporting tall. Now, if I right mouse click a country name such as Germany, I could sort those back a to Zed. So I've got Argentina in Austria, etcetera at the top again, and the customers maintain that large to small sort, which is what I wanted. 7. Grouping Data in a PivotTable: in pivot tables you can group any numeric filled on. This is a really useful skill to know, and in this example we are going to do possibly the most common use of group in fields, which is to group a date or time value. So we do have an order date column or filled in our original data set that we can use here , and that shows thesis specific date of the order. But maybe we need to summarise our values by month or by year. So we're going to group into month and year and then use that within our pivot. Now I want to begin by removing the two fields we company having the rose area. I'm going to drag country out off there and also product category and replace them Selves rip so I can see the performance of each sours rep in this data. I'm now going to bring in order date, which is the important one, And as soon as I drop order date before sales rep in that rose area, it groups it into years, quarters and month. Now it's quite a new feature that is doing this in 2016. It started to do this in previous versions. If you're using one, it will not group immediately like that. You will get the specific date of the orders just like you get in the original data. So what you will need to do or if we want to change this, What we would need to do is to right mouse, click a date related cell so I could right click one of the mumps so I could right click February here, moving to group on. Do you have the options to group it? However you want because even know in excel 2016 onwards, it does this default grouping for you. That is not necessarily the one that you want. So there's a good chance you have to come in here and specify this. Now I'm going to remove quarters imagining that I'm never going to use that. And I just wanted months and years click. OK, I now have what I wanted. I can see their totals for each month on the sales reps performance within knows mumps, and I've also got the year performance as well. If you look to the road area on the right, you will see that we have a year's field. In addition, toe order date. So not only has it grouped it, but it has created an additional field for us to use. Now. Years is years or the date is month because it's still grouped. We could ungroomed pit, but what that means is that I could remove order, date completely and then move years up into this columns area on the right hand side. So here it looks at the moment, with the year performances ever drag up two columns, they will come down in columns on We Conceicao Drop Across the Road, which is a love leak on across tap. Report on view off that much better way off scene trend and variants and growth. What's a comparing those values side by side, which is normally, How would view that kind of information Robin in a column format? If we wanted, we could bring order date in there as well, dropping under years in columns, and now we have monthly variance off. He sows rep performances, and if we don't want that anymore, we just simply drag it back out on. We're left with years. So we've seen how to group values here, and he can group any numeric values. But dates and times are the most common. Use off that. No to put dates into months or times in two hours or 30 minute intervals for your analysis , but it can group any type of number. There are other uses out there. Voice is seen, the columns area being demonstrated at for that left to right view, which is so common as a way of viewing time. 8. Show Values as Percentage, Difference and Ranking: in this lesson, we will start to look how to show your values as a percentage, a difference or a ranking. So we've already looked at how we can change the function being used in this values area off the pivot table to show over some account on average or more. But we can also right mouse click these values on, in addition to summarising them by a particular function. We can also show the values as any of this good stuff, and hopefully a lot of them will kind of speak for themselves, such as rankings and running total. But I wanted to demonstrate some of them she get a better feel for it. So let me start with percentage off column total Remember, have got the years here coming down, showing the sum of each salad reps sounds. And if I choose a percentage of column total, the grand totals moved to 100% for each column, and then we can see the contribution that was offered by each soured rip above yearly and also a grand total. She can see how Margaret in the grand total was just shy of 1/5 off the total Sal was to be made. Revenue selects percentage off column total There was also an option percentage of Roe total kind of does a year when a pie chart would do visually but in a table form showing the percentage is all running up to that big total. The bottom now over right mouse clicked value again. I went back to Chau values as next up. I wanted to show the difference options so we can see the difference from a value or also the percentage difference from a Value Select sale wanted to go for percentage from there would prompt me percentage. From what on have got sours. Rep. Here is the base field and in Margaret Peacock, let me change that years and in the base item can be the previous year said that when I click OK, it will prompt me for the fat a auto sort, or to show custom calculations air on and would like to switch them off. I'll say yes. Otherwise, you can't do the positional reference off the cell next to you. And here we go looks a little bit ugly here because 2015 is not a full year, so you can see with 2016 we've got some crazy percentage is going on. Where is with 2017? It begins to look a little bit more normal, if you will, but I showing the percentage difference from the previous year or the column to its left. Remember, we also had the option of just difference from said, a monetary difference rather than percentage difference, led by twos years and previous. Here we have the money different service 18.5 1000 down from Margaret from the previous year and you can see a negative here for Steve started down, So they are some of the difference options. If we go into show one more example among the many good things in here we can also do Rankins. So now obviously we consort a list consort by name. But Norse is sort by the totals and have them descending. But if I choose a rank, I wouldn't want here when largest to smallest, I guess, and sales rep is really our only option here. And if I click OK, this is what we get. Concede that Margaret is the best ranked overall that she ranked first in first couple of years but actually third in the final year. And Janet was the best cells up that year who is second overall, and we can see these rankings CSR consorted list, but I can't rank by multiple fields in the way I can hear. So that is another really useful option that I think Waas introducing 2010 are thinking excel. So it's not always been around the ranking option. If you went back to Chau values as we can switch this back to no calculation, and that will bring it back to your day, folks, some just like of demonstrated earlier in this course. Remember, you can add multiple filled into this values area as well, so you could have a ranking in addition to the percentage difference so we could have a some and a percentage difference. Now you're not limited to just the one, but obviously you don't want to overcomplicate it or clattering at the same time. 9. Conditional Formatting with PivotTables: So one of the most popular features, Off Excel is conditional formatting, and it's something that we've spoken about on this course already. Let's see how we can apply it to pivot tables because it's very similar, But there are some differences to be aware off. Select me. Imagine as an example that I have a kind of target fresh hold off £100,000 and I would like to visualize anyone who's gone beyond that fresh hold. So if I highlight these values, I'm ignoring the grand totals. Could you can't really put them in the same consideration. And I shall use the conditional formatting button on home just like we did when we use it with normal ranges. And I want highlight cells rules greater than 100 12 3000 and we can see how it previews it in the background. I can then go and choose my format in keeping this a little brief, because it's something we've discussed on this course already. Onda. Yes, I will have, uh, Greenfield will think. Okay, My way out of here. There's the application made and that's wonderful. Yeah. Now, if I went back to conditional formatting and down to manage Rose on. And if I go to edit this role, this is what I wanted us to see. Their fact that by default, because the approach that I made it applied it to the selected cells, and that's wonderful. But what if the number of sales reps that we have grown shrink over time, which is governed toe happen? No employees will come and they will go, and the company will expand or maybe reduced in size, so it would be more appropriate. So I've have all the cells showing a ton of some of total sours values or order sales. Shona some off total salad value values for South Europe and years. And that's when we want. Really, Because if I choose the 2nd 1 and click OK and apply that, that's going to apply it to all these grand totals as well, because they are also sums. But if I go back into that role and say only for the salary and years values, then it goes back to looking like before by no appliance to grand totals. But more importantly, has the intelligence moving forward as this pivot may grow and change over time to apply this conditional foreign rule two additional rows or to reduce roads rather than just a selected cells like we would typically do with a standard data range so we can use most of our conditional formatting tricks. You may have noticed that some of them were disabled are available within pivots. But the key thing only to mention because of nature off a pivot table, is the fact that we do have other options when we're in there managing their roles and editing a rules and to make sure we get that right, so they applied itself correctly over time. 10. Filtering PivotTable Data: Now let's talk about filtering pivot table data because we have a few different ways to feel to it. But let's begin by talking about the field filters that we get. So looking at our pivot table, we have a field filter for the row labels and also for the column. Labels on this is pretty much like your usual filter, with a data range on the shades. We've got over the usual good and useful date filters here because it recognizes a date value but also the check boxes underneath. So I could remove 2015 if I didn't want it visible. Because it's not a complete year. It kinda distorts the data in some ways, depending what we're using it for. Or maybe there are specific sows reps to exclude forever reason that maybe may want to exclude Janney and Michael. Maybe they're from a different team. I don't want to focus on them right now, and this is just using you pretty much your normal filters on now your field filters within our pivot. Let me just bring them both completely black. Clear that and the other one to focus on is the filter's area off our pivot table Seditious . Your report filter. So what I wanted to do here is moved the years filled out of columns and into that filters area so that it would appear as a filter above the pivot table. So it's possibly in cell B one, but not necessarily. But it always goes above the pivot of standard, and I can choose a year from there, and that would filter the table. Now remember that we can put as many fields as we want into any of these areas as well. So I've got years in there right now, but could also bring country intellect filter's area and, in addition, to feel, trim it for a year. Also, fuel to for a country such as France on now will concede a sow's within France 2017 and I consider sours reps performances in that region off that year on with the brilliance of pivot tables, we could move these fields around. However, we want to get what we want. I could remove sours rep completely, but product name into Rose Now consider sours with these products in France 2017 I could even bring product category in its 1/3 filter and shoes, something such as Dairy on. Now I'm only looking at the dairy products to customers in France in the year of 2017 exceeded the best dairy product was that one or the other two are pretty close match. Apart from the 2nd 1 that was pretty poor. Overall, fruit hasn't 600 so we can really drill down, said a Daito. And put multiple conditions into the way we evaluate this. There are four conditions there. Imagine a some ifs doing that. That is nine arguments to complete just to get one of those values. So pivot tables are created with this exact feeling. Mind find a user friendly, simple yet fast and powerful way off getting this kind of information into report format very quickly and very easily. 11. Refreshing a PivotTable: very important topic now with pivot tables and that is refreshing or updating the pivot table data. So this pivot table on screen is linked to the data on the other sheet in this sow's table . But if the data in that sows table was to change, it will know immediately affect this pivot table unless we refreshed it. And that is the default way off behaving. So let me just simplify this pivot table for a moment. I'm going to remove product name, move category into Rose, and I think I'll get rid of both of those filters, actually, So to get a really simple pivot table during the sows by category and if I moved to the pivot tables sheet just jumped to the bottom off this list for one moment and I'll start to put in a pretend cell. So put an order idea of 123 I don't have to worry about complete in order of these fields. Let me just skip to the one of interest, which is category. Andi. I shall just type biscuits that will do for a simple category. Ah, unit price off £10. We have sold 200 off them Very, very good. No, no discount for biscuits. Everybody wants a biscuit. And that will do for now. So we have some figures in here. I have a total south value off 2000. Now let me move over to the pivot table and you will see that biscuits is not in here. But it is a category. Now I can I have a right mouse click on the pivot table to refresh or go to the analyze or even the didato tap to refresh. From here, there are multiple ways of getting to. It is very quick, very easy. It's just a case of remembering to do it when you're new to using pivots. And you don't normally have to think of these things when using formulas and charts and conditional formatting and some of those tours. So if I click refresh incomes Biscuits with its 2000 So it was ableto update from it's linked to the other source Bill. It didn't do it immediately had to be refreshed. That's important to know there is a refresh all option. So it doesn't matter how many pivot tables we have. They would all be updated, and we're going to talk about getting external data into Excel in some upcoming lessons, and they would all be refreshed if we wanted them to bay at the same time as, well. That one button fits all now on the far left off the analyzed tab is also an options button , and we can also access that by right clicking the pivot again and going to pivot table options. I have a way. We end up some options and has lots of good stuff in here. But all I wanted to focus on right now is on the day to tab off this options window on. We have the option to refresh data when open in the file. And if we ticked that box and click tok, it would make no difference right now. But every time this spreadsheet is opened, let's imagine it's opened once a day you're in a open. It would refresh its data, so we have some type of semi automatic refresh. Its no eMedia, in aware of formulas, normally behave, but it's normally adequate for what we're using pivot tables for if we're using them as part of our weekly report that un, you really need to update once a week. Anyway, it's not really a big issue, But if you're dealing with data that is a little bit more live in real time, then you may want to consider the regularity that it refreshes and that you have up to date data on this option is all we have. Really? Apartment. We get into the likes of macro VB a weaken doom or a standard is still we have If I clicked , Okay, so you would not notice any difference right now on. But if the fire was date was changed and it was closed and reopened, you would see the update happened. 12. Creating a PivotChart: Now this pivot table looks amazing. I am sure you will agree. But if we are going to use this in a report than to visualize it graphically is probably something we're going to look at to do. So we have spoken about creating charts and excel in a previous section off this course, but we can also use pivot charts on if we're using Pivot table data is important that we use a pivot chart and not the charts we spoke about earlier because although they were work , they were not completely synchronize. We have the pivot table, so ignore them when using pivots. And on the analyzed tab, there is a pivot chart button, and when we click it apart from the odd little difference you'll see that is the same. Really, in any skills that we learned earlier can be transition to cross your notice for a look at this time of doing this video that scattered charts, for example, on all available here. So there are some little differences, things you can do, things you can features. Pivot charts have that normal charts don't and vice versa, but they are minimal now. If I was to choose a column on just click. OK, I have myself a chart on our No, intending to speak too much on this in his part, of course, because we had that whole section to it earlier. But I do want to demonstrate and talk over a few things more. So if I was to keep this, I would get rid of thes legends and these titles, like we spoke about earlier, just clicking on them and pressing delete to remove. I have these what they call his field buttons in here. So I have a little filter actually integrating the pivot so can change what categories are being used. But if I want, all of them are not really interested in them. So I could click on my analyze tab at the top and removed the field buttons on the far right and they got and I've got a little bit more room to work. My chart. Bit more solos, text at the bottom look good on a design tab. We can change the colors of the chart and different styles in the chart type and the same stuff as earlier, including adding chart elements so I could easily come in here. Look up, starting to remove axes and removing grid lines and instead I ever from the plus on the right or from the HR Element button, bringing in some data labels as an alternative. When I'm on the chart in the field list on the right, you can see how the column and row labels now refer to themselves as the axes and the legend. Because I'm in the charts and I can work with this in the same way that we worked with them for out the pivot table talk, Most people would feel more comfortable in a pivot table, so I click to my pivot table. It would go back to rows and columns, which is an area where people would feel a little bit more natural using note terms than axes, which not everybody is comfortable doing. But we can change. Iver and the chart will talk to a table on the table. Talk to the chart. Remember, we have our filters area if I moved country into filters. So I have the report filter like demonstrated in the previous lesson. And if I click on my chart and bring back the field buttons because now they might be useful for me to come in and Phil to my chart using the built in filter. We're going to talk about slices in a coming lesson, and that is another alternative for us. Depend on what we're trying to get out of data. I can go and remove those field buttons again if or not plan on using them or from, and using the utilize and a filter in a different technique as opposed to the one built in to the pivot chart, which is good for what it is used for. Better, some very clever of the ways of working. It sets things I can remove the country again from the filters on everything. Updates on. This is just a real quick lesson on looking at creating pivot charts. The importance of using a pivot chart rather than the standard charts. When work of pivot table data, they should come together and recap in on some at key topics about making your charts look the way you want them to be able to use them in a built in tools such as the filter on access in those fueled buttons 13. Using Slicers for Interactive PivotTables: Okay, really good lesson now on something called a slicer. Now this is well loved. What Excel users, especially those involved in creating dashboards or in data analysis, this a really useful tool that was introduced with Excel 2010. And it's available with both pivot tables on also normal tables. So we have this pivot table. I have just one at the moment, but I wanted to quickly create a 2nd 1 to show you some of the biggest strips off slices. Now I could use the textbook approach and go back to my table and insert a pivot table, but rather and do it that way, I was going to select the one I have got and just simply copy and paste that one. Now it's important when doing the copy and paste approach that you select the pivot table in its entirety. Otherwise, when you paste it across, it may paste across this just values and not actually be a pivot table. But as long as I selected its entirety, Fillon paste, I now have a second pivot table is doing the same thing at the moment, but that can easily be changed. I can remove the product category on bringing a sours rep. Maybe all sorts both for these lists into large to small order. That will be nice so we can see the best selling category A product and also the ranking off sales person also still have this chart from a previous lesson. And then I'm gonna keep hanging around. She can see affected by the slicer, but I'm no greatly interested in it anymore. Now, if a click back on the first pivot table, it doesn't really make too much difference. But it does make sense to go from left to right and start with the 1st 1 on the analyze tab and the pivot table tools. We have an insert slicer button, and if we click on that, it will prompt us for what we want to slice a four. No, maybe I will choose country and click OK, so we have quite a lot of countries, said it will dump it in the middle of try, move into a bit of spice, and they'll provide a scroll bar because we have 21 countries in this list, defaults to its blue color. Now, if I click a country, it performs a filter. Every time I click country Canada, you can see the first table on the chart are affected by the slicer. So it is a modern type of filter to kind of replace at the filters that we get in the field or that you seen your normal range is much more visual with it's kind of color and style. It's far stomach. It's one click rather than orders could check box system and okay buttons. But at the moment is not really functionally doing any more than the filter I put in the filter's area off the pivot table a few lessons ago. So you may have expected this, and here it comes. We can connect it to the other table as well, and that is something you do not get with the pivot table Report Filter. So a conniver right mouse click my slicer and moved to report connections or using the options tab for the slicer. Report connections. Depending on your version of Excel, it may be called pivot table connections, this button that will list along the pivot tables we've got in the entire workbook. Notice how it references the sheet that Tehran this would work even if their own different shades. No, you can also name these pivot tables. I think it's important to note, because they called Pivot, say, with three and pivot table four in a moment. If I was to zoom out camps without, for the moment on each pivot table, the far left of the analyzed happy have the ability to name them. So if I named this one by South and it was named my 1st 1 by category before we got to that slice a moment, imagine a situation where you've got nine different pivot tables. Are you really going to know what one is? What now when I come in here? Sorry, the report connections. I need to go to options report connections. They now have meaningful names. If I take both of them and click OK, it is now connected to both. So as I filter, you can see both pivot tables affected, and that is the biggest strength that it brought to the table when it was introduced of Excel many years ago. Now, quite a few versions ago that it gave us his visual fast, but also powerful way of filter in more to poor tables and charts in one click of a button , perfect for reporting and dashboards. So we didn't necessarily have to go to as much set up work as we would before these were around retrying use formulas to combine data and to work with one drop down or one filter toll. Now, the fact that is colored in that way is another strength for it. It does have a style. So, just like in previous lessons on the pivot table style and the table style, we can also create styles for the slicer on the options tab. We can use one of their default styles, all going create our own just like we've spoken about before, although obviously a slice has different elements toe what a pivot table or a normal table does, so we could give this slicer a name. I might be recording this slicer for now, this lifestyle, and then we can go and do some stuff. I could go and make the head of bold That's Deuce, Deuce riel, quick stuff, and I want to dedicate too much time to this on. Then the selected item with data that is where, maybe, or bring in that style. That color scheme I keep referring to are going to mawr colors. Custom colors are put in those RGB values off 2401 to 812 white give to get the exact color that's the company uses. Maybe we'll have some bold fund as well. Let's go for some bold phone. That'll be nice. And it may be for the UN selected items with data I'll just choose. A gray color will keep that quite brief, and as our okay out, you've got a default option there and then apply it to this slicer. Now we have our own style that I could have dedicated Mawr Time to and Siyassah hover over , the background goes wet, you can choose the whole McCullough, and all this stuff is well to get the exact style winter to get your chart on your tables in your slices, all consistent in that brand image that the identity of your company, or maybe just to add some different contrast to make it look professional. One look the way you want. It's do to look good, but it is my slice of for now. Now, another reason I like to use country as the field for my slicer here, he said, I can demonstrate the on the options tab. There's also an option for columns so we can resize their table. We can change the size of buttons, but are 21 countries. It's a quite a lot, so we can also be quite smart with it. Though I could increase the columns to three and maybe with a little bit of work, fit order countries in without the need for a scroll bar. There's obviously nothing wrong with scroll bars. People know how to use them. They're effective what they do. But in the world we live in of smartphones and touchscreen devices, it may be beneficial to try and take some effort. Both of the wording of the countries would help as well robin entire name like Switzerland but also for re sizing. The slicer results in the buttons. Adding columns are Cavite just in one click, and there wouldn't be any need for the user to scroll fruit. So that's a nice touch. I could even increase it to something a little bit more radical, such as 12 and then resize this into more of a horizontal one vote or in a vertical one. You can get very clever with the slices to get the appearance you want. You can hide these filters and staff in his headers if you don't feel it necessary, and sometimes just battling with the really state of your dashboard to make it by functional, but also to look good and to be accessible as well. So that's another option to be. Be aware off now if I was to remove this country slicer just for one moment and maybe I'll look at bringing in some different slices, I want to demonstrate one that has years. And then I also want to insert one that has insert slicer sounds. Rip, I think, could be a good one. And its position is a bit on screen on a apply the lovely style that was just created. No, just resize this year, ones to cut out those greater in less than elements. Let's just resize this to get all sells reps in. That's all of them there, Steve. I'll start to click years that 2015 and all I wanted to demonstrate really was that as I choose 2015 there are two sales reps who were no around in 2015 so they do not show when I was in the style earlier. But go back to modify. There was the option of selected item with data, so they are selected items with no data, and they just appear in this kind of white color. So first of all, it's good to know that you can style though it effectively. You might not be happy with that. Maybe, but also I wanted to make a point of showing that the slices are very intelligent filter in the fact that they're only going to show you options that are relevant. No, Terry knew. Right now, there's no point clicking on Andrew because you're not going to get anything. There is no data with that. So the fact that they are dependent upon each other, they would talk to each other on only present options in that that makes sense to use is really, really cool as well 14. Using the Timeline Slicer: hello and welcome to this lesson on using the timeline slicer off Excel. Now this license was introduced with the 2013 version of Excel. So don't fear if you've got a version prior to that and you do not see this one on. This slicer was introduced to work with dates on kind of timelines that time based data a little bit more intelligently than your normal slices that a farm or commonplace and Maura loved my XO uses generally. Now let me get rid of this year's slicer here. Maybe I'll keep the Sauer's rep one for a moment, and I'll click on my first pivot table. Analyze tab insert. Timeline is right next to insert slicer. If I click on insert timeline and select Board a date, that is the only date time based field in my pivot table. So the only option presented to me and I click OK that comes in can be resized and minute plated and moved into certain areas. Let me add a bit more space at the top for it here by inserting some rose, and that's going to look good up there a little bit more. Once again, we can apply styles to this stuff from the options tab would have to create one just like before to fit in with it. And you can see there's a little filter in the top right to choose different date based options so I can choose years. I could choose months and also quarters in this example, to make sense. And as I click on a year, see how it filters the tables and charts. I can also resize that books to expand the years or indeed the mumps that it uses. It's it's a very quick and visual way off view in date and time based data. I can see that that is just looking at 2015 March fruit to at the end of August, of which there is nothing. But if I moved it along a little bit into the start of 2016 UTC had a data appears on on working with date time based data on a timeline, horizontal left to right filter that is maybe visually more understandable than just some buttons to click on on. This is your timeline slicer. It's another slice of that was just more catered for that kind of information in the way that is presented and may be manipulated on that. Maybe something that you want to think about introducing with your reports for pivot table into activity as well.