Advanced PivotTable Tricks - Useful Tips to Develop your PivotTable Skills | Alan Murray | Skillshare

Advanced PivotTable Tricks - Useful Tips to Develop your PivotTable Skills

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

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

      2:14
    • 2. Two Reasons your PivotTable Counts Instead of Sums

      6:07
    • 3. You Must do This One Thing - Excel Tables

      6:48
    • 4. Grouping Dates and Times

      9:21
    • 5. Using the GETPIVOTDATA Function to Pull Data from a PivotTable

      9:22
    • 6. Going Beyond the Sum - 3 More PivotTable Calculations

      9:43
    • 7. Calculating the Difference to Previous Years/Months/Weeks

      5:16
    • 8. Create your own Calculated Fields

      3:41
    • 9. Using Conditional Formatting with PivotTables

      6:21
    • 10. Show Top Ten Results

      3:20
    • 11. Five Useful PivotTable Settings

      8:16
    • 12. The Power of Slicers

      10:29
    • 13. 7 Slicer Settings you Will Want to Change

      8:53

About This Class

d4875e94

In this class we will be delving into some advanced PivotTable tricks. We will be looking at taking your PivotTable knowledge deeper and further than the standard user.

During the class we cover advanced calculations such as calculated fields, ranking and comparisons to other field data. We look at the GETPIVOTDATA function also to pull data from a PivotTable.

We cover grouping date fields into years and months and then comparing values to previous years and months.

There are various PivotTable setting that can be changed to get more from them. Many users are unaware of these and don't have time to look. This class will show you them.

We finish with utilising Slicers to the fullest and the secret settings to take them to another level.

If this sounds like fun. Enrol and I will see you in the class.

Let's do this.

Transcripts

1. Introduction: hello and welcome to this class on advanced pivot table tricks where we will look at some really useful tips to develop your pivot table skills beyond the standard user. So let's have a look at what we're going to cover in this class. We're going to make a deeper understanding off your pivot tables. We will understand why your pivot table might count instead of some, which is always a common issue in question. When I'm doing my training, we'll look at some really useful default settings as well, to change the way that your pivot table beehives will be looking at working with dates specifically years, months, weeks but had to compare dates and time values but also how to group them. So we'll be looking at grouping how wish and comparing them to previous months and years. On that note, we will be covering advanced calculations, so be showing the difference with previous years and months, both as a value. But also it's a percentage. But we also be looking at calculated fields and ranking and other calculations that many people are not aware that pivot tables can do. We'll be looking at a get pivot data function and how we can use it to pull data out of a pivot table. We'll be looking at using conditional formatting on how using conditional formatting of a pivot table condemned for to how we use it with a range of cells to make sure it works further into the future as your pivot table may expand and shrink with data over time and we'll be finishing by looking at slices both how to use them effectively but then also some secrets settings to take them to another level. So if this sounds like fun and we're talking about pivot tables, so I'm assuming it does, let's get into it in role now and I will see you in their class. 2. Two Reasons your PivotTable Counts Instead of Sums: hello and welcome to this lesson where we look at one of the most common questions and column problems that people come across when they start using pivot tables. And that is why does my pivot table count values by default instead of some them? Now there's two main reasons, Boy, that maybe on we're going to cover them here now, in this big list of data, that of God, you can probably see already, but let's divert your attention to it. I have a blank cell in Seoul J 13. So having a blank in that column is going to cause me a problem. Let's have a look. It it in action. If I go in, insert a pivot table quickly. And if I know that is to correct range of data, put this on a new worksheet, and I'll just simply add in that say, product category. And then, let's say total south value. Now, immediately, you can see there's a misunderstanding. Total sales value. That column is a bunch of numbers. It should be addendum. But the fact that is put it in the rose area immediately indicates the problem. And if a drag into values, he goes to account as the pivot table believes that that is text simply because of that single blank sale. Yeah, it doesn't think it is numeric. No. Yes, I can come in here and I can change the value sort of formula to a some function on that work Absolutely fine. It's just a default reaction that people have. What is mine Always count instead of some because it shouldn't be doing that because Excel finks its text. Now that's one example of that happening there. If I just completely delete that she eaten stall again and has put a value in here. This copy that formula back down because one of the most common reasons that happens is not necessarily a blank cell in your range. The reason it normally happens is because people select the entire columns. Now, I'm not saying it's a wrong thing to do or a terrible thing to do, but that is white defaults to account. If I slept, the entire columns bear in mind. I'm selecting all this stuff as well all these blanks as well. So when I go to insert my pivot table and let's put a new one on a new worksheet. And that's true Scategory again. And maybe this time to be different or drag total South value into values. So ticket this time, just in case you thought that might have been the reason. And here we go. It's immediately gone to account, and I've also got this blanks areas well because of them. So once again I can change it to a some function. It's not a problem, but because you've selected the entire columns, that is why is the 14 to that count? And it's not quite on your wavelength. You probably want to come and fill throughout these blanks now as well. It is no major big deal, but I daily that kind of stuff shouldn't be happening if we highlighted it in a slightly more efficient way. Now let me delete that sheet. I do have one more example and those to a kind of the same. It was issue of blanks at, Ooi says. Two reasons. What has happened? The other one is possibly a miss type. Then look at this unit price area and they see this £2.50 here. Imagine a scenario where somebody is typing this fast as they can, and they're really stressed and busy at work, and they type that now. I'm majorly there's a problem over here, which you may notice, and that may immediate alert you to what's going on that I've talked in O not zero in the form of got here. It's, you know, it's not that clear, but it's not that hard to see, however, sometimes depend on the front being used and the size of the screen and all this kind of stuff not necessarily that noticeable on the over No. Zero right next to each other on the keyboard. Very easy to do. Not necessarily that easy to notice that kind of mistake, among others, and come on course problems. So this is the unit price column. Let me go in, insert a pivot table and let me bring in product category on Let me bring in unit price to be summed on surprise. Surprise! It goes into the count all because it doesn't realize that colonies numbers. Just that single cell that's have a blank or for some reason, format is text is enough to throw that pivot table out, and you will need to come in here and try and get it to some, those values that shouldn't be a problem with a very mine. That one sale was not being summed. Obviously, is not gonna understand that at one said it out as to these figures, us that figure down there. It's slightly incorrect in this data because of that. So if you do come across this issue off defaulting to account, you want to find out Roy because it was an example like this. That is the wrong answer on that terrible off the situation to be in. But in my experience, the most common reason is people selecting the entire comes. That's not necessarily that serious a situation, but we're going to look at how we can do this kind of stuff a bit more efficiently in the next video. 3. You Must do This One Thing - Excel Tables: hello and welcome to this lesson where we look at the one thing you must do before you correct your pivot tables, and that is to format your range of cells as a table. Now this beautifully follows on from the previous lesson where we were looking at typical examples why your pivot table counts instead of sums by formatted as a table that would have handled a lot of those issues and more to there are many benefits why you want to do this, which outweigh what I'm going to be able to cover in this video or in these videos. But the free main reasons we concern herself with when where worker pivot tables is that a table is dynamic, easy to reference and will also allow us to create a pivot table from multiple ships multiple tables with the use of its data model feature. So let's have a look of this. Have got this range Here. We can just click somewhere in the range or highlight it. Whatever needs to be done. This is a perfectly structure table, so I could just click anywhere within this on the home tab, click former as table and go for the one that you want. Now maybe I want something similar to what it's looking like in a moment. And so maybe I'll go for one of these kind of blue header ones. I'm going to, right mouse, click on it on a ploy and cleared of formatting. Just please notice in the background the opportunity to create your own styles here said, depending on why you're doing this. So maybe there at the company the work for you may have a specific color scheme or font scheme that you have to abide by. You can create these styles so that long term, it's doing what you needed to do. It's looking the way you want it to look. I'm gonna press a plane clear here, clear Any current formatting, apply what I have. It will double check the range and where your head is. Our this is all correct. So click OK on. We have the table. The first thing you're going to notice if you're new to this is the full matinee employers and a filter tall appearing at the top. These are not the most important things about this table. The formatting is great. I mean, That is one of the things we appreciate highly about it. The Children. You know, I don't need a table to do that in the slightest. We've got the opportunity to change the style in the top. Right here. You have this design tab under table tools. We can turn off the filter bun, weaken, Turn off the banded rose. We can do some formatting aspects, but another key thing in this a table is the very first option on design is to name it. No, I'm going to name this table, Ciouse. It's my cells table. So one of the big benefits of the table is how easy it is to work with and refer to within four meters. But more importantly for us that this part, of course, within a pivot table. So you. But according it sells, that's what it is. It doesn't matter what she term on anywhere in this book. I don't care how many columns it's got. How many roses got it? Simply sounds now, another huge benefit to tables if I was to scroll to the bottom is that if I was to sell more stuff, make more sales, let me toughen idea in the bottom here outside of the table as soon as I moved to my next sell the table expanse. So you know that if your pivot table is using this table when the data is updated, when that range expands, the table will expand with it. Therefore, your pivot table will also update and expand with that, so we will have this automated report that soon as data is added, it looks after itself. No more edit in the data source of your pivot table, which are here so often in my training. They're having to go in and edit it to adjust it to the range of your data. You shouldn't need to be doing that stuff if it is needed. There is a little blue corner is very quick to do in the table, still much better than edit in a data source manually. Just drag that. Make sure it's all up to date and is picking up the rows and columns that it needs to. That will also counter the problems we had in the previous lesson. I was showing selecting whole columns. Look, however, set whole column this time. Look at this. Notice that it stops at that row exhales fully aware, despite me, slipped in the entire column that the data stops there. It's so much more than intelligent way of working than with your static ranges when we work with things like pivots. Now let me get rid of that row. I do not really care for that. Pretend new Order an old school backup. We have this data in their table now. I can go and create my pivot table, that insert and pivot table. You see it picks up the table. If it didn't over just typing sounds at this point and on the new worksheet on or click OK , as I mentioned in this video in one of the other lessons later in this course will look at how you can create pivot table from multiple tables. That's another key benefit off this feature. There are many, many benefits, but I think they're the free. We're going to like the most dynamic nature of it, expanding with fresh data, the ability to relate tables within it and the ease off referencing because it is simply known a sows at the data source. Robidas, some big no dollar a dollar $1 K dollars to 156 kind of reference, which is confused, and it's not easy to work with. And, well, one easy life here don't mean easy and accurate an enjoyable life. 4. Grouping Dates and Times: hello and welcome to this lesson where we look at a very important pivot table skill, which is to group your data. Now you can group any numeric field in a pivot table, but their classic example will be dates and times. So that is what I want to focus on in this lesson, beginning with the tights. So I have the same table from the previous lesson, this south table with cells over about two years worth of time and you can see column B here. The order date field has the exact date in it. Now if I go and create a pivot table from that south table on a new worksheet and look at bringing that order date filled into my rose area. Now I am using Excel 2016 right now. So as I move that ordered eight filled into Rose immediately groups the data inversions prior to 2016. It will not do that in 2016. What it does not always a big fan of it doing that, but it's what it does now. And in this example, I guess saving me some effort, let me move on for the moment and put toe tour South value into my values area and then old looking, expanding this stuff. It's immediate. Grouped into years there. Yeah, I can see how much are made in the two years with the data, which is nice, but with my pivot table active on my analyze tab at the top will be options in previous versions of Excel. Why? I'm going to click on this accurate expand button expand fuel button your green plus the economy would expand it. So now I can see my groups and so in my quarters and expand again. I'll see the mumps now over in that rose area on the right. I'm going to get rid of quarters. I'm not interested in quarters off Chuck that out of there. So I just got the mumps now. I mentioned that in 2016 immediately grips your data. It will not in previous versions, so you will need to right click on one off the dates because in previous versions it would just come straight in with the date and look a group from the options. And then you can specify how you want it. I have months, quarters and years at the moment, although I have removed quarters from my pivot table. Currently, I could turn it off from here, and it will just get rid of it completely. Let's imagine I don't wanna keep that and you've got a full date range. There. Now, I have this data. Let me format those values because that is gonna hurt. Have a nose on screen, and then we get rid of its decimal places. Don't need that detail. That will do for now. So 2016 simplifies this, I guess. But you can always right click one of the values and go into group has we want to date values to get the options you want and choose them. It's important I keep the years here because I have multiple years and I needed this to distinguish different months across those years. Now we want to take this step further. Could Mel going crate, maybe a pivot chart from this uncredited nice lying graph? Now we've got this data in its axes area of the chart. Four million owners rose if I'm in my table but now referring to is axes and I can create a nice line graph showing the sowles over time. I can go to the field, filter off the chart or indeed from the table, and choose to only show data from this year on. It's fantastic that pivot tables make it this easy to group and present a date related information, which is so common for Pitt, would have to do. We're looking at Sowell's or finance or training data or stock levels. You want to see how you're doing this week compared to last week, and you want to see what the last six months have been like and with the brilliant filtering options you get in pivot tables, the charting capacity and the purpose of this lesson, the group inability allows us to get that information very quickly. And very simply now, we've just looked at how we can present styles data over time by group in the date. Now we're going to look at group in the time, so I have a large list off times. I'm imagining that this is call centre data. So got a time that various kind of staff have accepted phone calls off some kind, and I'm going to create a pivot table quickly on a new worksheet again, Notice has no table set up here. So the pivot just grabs the range. You've got 5000 rows worth of data here or pretend court into data. Okay, Okay, to create that and I'm going to move the time at field into the rose area and the name field into the values area. Now, remember, I'm using 2016 on immediately. Once again, it groups my data. I've got hours and minutes and then another time. And if I look at expanding this information with that expand field option from before I keep expand in each one, she has got right down to the minute. Now, that's not information I'm concerned with So I could ever remove it from my pivot, but keep it. But maybe I know I'm not going to use that stuff so I could Right click. I'm going to my group options and just get rid off seconds and kind of minutes here and say , Look, I'm just interested in the hours. That's all I need. Oh, click OK knows it's a whole day's work for data here in the first phone quarter. Last click. OK, and I just got my hours and I've got on how many phone calls within the hour. Now I could take this a step further. For one, I could bring the name of the individual into Rosa's well, and now we'll concede. E how many phone calls each staff member took. We've been knows hours off the day I could. Then maybe right click one of the values for South Member. I could sort it largest to smallest. So we've in each hour slot. I can see who the best member staff was if we judge them on volume of phone calls. Except I can get this information so quickly. Once again because of pivot tables, ability to group numeric data once again, ever wanted to know could remove name quickly. I can create a pivot chart, fullness, to shoot some kind of values of the time he is. McCollum Chart is a nice line graph show in this data, so these can all be formatted, just like we spoke about before to try and look for these. Kinda. These trends just a throw in another pivot table skill. If I'm looking at the Times taken a two o'clock when they're our, I could double click of value, Emilie, that will take me to the phone calls, and I can see what time they were taken and then the other related information on may have about them. If this was really time data from a real course in a database, I can probably see who that phone calls were and how long it lasted and maybe an I D. Record and stuff. Let me get rid of that for now. Now group in. But times can be somewhat limited. S o. I feel it's important to mention on the original sheet of data the raw data you might want to look at use in some kind of round in function or something to potentially group them how you want so that when you get to the pivot table, you can use those because her the pivot tape herbal was more than capable. You noticed. I just had hours and minutes and seconds, and it might be that you want to group them into I'm kind of parts of a day or you want to see them flat every 20 minutes or something. What's going on? So sometimes you may have to use their ability in a work shape, especially with formulas, before we get this far as a pivot table. If it is a more nish example 5. Using the GETPIVOTDATA Function to Pull Data from a PivotTable: Hello and welcome to this video tutorial from computer guard guard dot com. And in this video, we're going to look at how to use to get pivot data function off Excel. Now the get pivot data function is used to query and extract data from a pivot table, so it's kind of a pivot table. Look up, that's what it is. Now. This feature is turned on by default in Excel, and that is because it can be extremely useful for what we need to do. So take this simple pivot table I have at the moment, and I'm going to use dysfunction to extract the data that I want, and we'll see some of the benefits off why you might want to use it. We're then going to see a second example off where it doesn't really help us, and we want a standard reference instead on we look at how we can turn this feature off. Okay, let's look at the benefits of it to start with. No. And if I just click in any sell off this spreadsheet and start a formula by typing equals and click on one of the cells off the pivot table and immediately in that cell they write the get pivot data function on what this is telling me at the moment. The first part of function tells me that is extracting data from the total sows value field . So just to make sure we're happy, that is the name of the field of numbers because it mentioned in top Left, that is the name of that field. That's my values area, if you will now a free that is a cell within the pivot table. Any cell, it can be a now let's to sell in the top left hand corner. And if you ever find yourself right in this yourself, you're encouraged to click on that one because it's reliable. And if the pivot table changes in height and width, we know that. So we part of it. Theoretically, though, it could be any off. The cells in here doesn't have to be a free, and then they just chooses the field to use it from you. See, you've got Field one item one field to Item two, just radium for this box down here. When I mentioned what I just mentioned so in the product category filled, it's gonna be a grains and cereals product from the years field. It's gotta be the year of 2012. And from that extract the correlation value from total sales value. And that's what it does is very specific in what it wants. But the benefit off that so you know, a pivot table generally change over time. It's gonna maybe get bigger. Maybe it's smaller. Maybe people are going to sort this data by, like the values or something, and that's gonna move the values around and these things will happen. So by using a function, I get pivot data. It would always find you what you want. It would always find the grain concealers value from 20 tells, for example, no matter where it is, because look up. No, that's a very good thing about it. A lot of kind of haters of Get pivot tape Table data get to the data will say that because it's so specific, it's so structured that is a negative. So I've got an example here where to combat that in cell G four got a little drop down this little data validation list off the different products, and let's imagine that this is probably on a different shape. This it's a whole different report. In a typical real world example, it's on the same sheet here so that Regan, for learning purposes, see what's going on. The people want to be able to choose a product and automatically return. What a total ease on Because I've got a pivot table. It makes sense to use that Robin Interrogate in the big list. So in here, if I was to go ahead and do that, if I taught, equals and I've got produce at the moment. So let's click on the quinto to full court juice, and it's going to write this in extract data from total salvage value. Are you free? Is a cell in the pivot product category field projects And that's what I wanted to do with the press. Enter surprise, surprise. It's got it from that cell. But as I was not be 11 no nose No. 11. That is the produce data, the grand total produce because get pivot data is involved, and that's good. But I want to be able to choose this drop down list above juice confections or just dairy, and I wanted to change it. At the moment, it's not. So let me revisit my formula and where I've got produced on the end in these inverted commons, I'm going to select Cell G four and press enter. And now, when I change that drop down list above two condiments or produce or to see food, you can see that value changing. So I've given my get pivot data dynamic element by referring to that cell on By doing things up that you can make the most off that function, it could be really helpful function for you in your kind of summary sheets, reports and dashboards. Whatever you got going on, if you have got pivot table data, it can help you extract the date that you want from it. But yet you can still keep the dynamic nature to. It doesn't have to be that structure now. That's an example where it can be helpful. But let's click on sheet to hear another good example where maybe it's not gonna be so helpful for us. So same data, but a different pivot table got a filter for a country top company, Switzerland, didn't there? I've got these values sorted largest to smallest so unify change it. So dairy atop condiments at the bottom but changed the filter for Argentina. So different here, confections that the topic of rains at the bottom or the date was moved around is actually a bit smaller as well as any seven categories. I switched that back roll Elevens got the totaling. I switched out back to Switzerland, the totals in 12. So it's a completely different table now. So let's imagine for some reason, possibly in a different sheet, and that that I want to return the value from the best product at any point in time in the pivot table. Now there's no point in me at the moment, usually might get pivot data because that will put dairy products in. So if I was to stop equals and click on that value, it writes, my get pivot data functioning. Get the dairy product category from tell so easily, but don't want dairy product. I want whatever the best one is, and because I've got that sort going on. That could be a different product. It depends what the country is and also depends what's doing. Well, you might still be Switzerland, but in a month's time. It's a different products doing well, so that doesn't really help me. What now? So what I'm gonna do instead is simply type in Oprah equals of this type before, and that will allow me to use a standard sheet reference instead off one of those get pivot data functions. It's not helpful for me right now, and they will press into when I get my value. And then I feel to my pivot to Argentina, for example. Now I'm returning. Whatever the best selling product is, I've always got the best selling product. No, dairy is not always gonna be dairy and thats example where it's helped me to avoid. Get the data. Now, if that is what you're gonna be using the pivot table. If you don't think you're going to use, get pivot data and it is to stand, it is the default thing. You've got a type in your references to kind of avoid it to kick in there. It's going to use it. What I could do. It's okay, Quick and the lies tab under pivot table tools at the top, it might say options if you're in 2010 version, excel on the far left of gun options drop down list and in their generate get pivot data, and it ticked but then off quickly to talk with off. Now, when I use references and click inside that table, it uses a normal sheet reference. So you don't think you're gonna use that function? You can disable it or stock great detail, but by default you can turn it off. That is our guest, your choice. And it works for me. This example. But remember in a previous example when I was talking about how specific it waas from the years and from the categories, the pivot table is gonna change in height and stuff changing whip. These are different years. There'll be other years coming, or these days of the week or even weeks, it's gonna change more regularly. You may need that. Get poor data to get the specific thing you're after 6. Going Beyond the Sum - 3 More PivotTable Calculations: hello and welcome to this lesson where we explore three extra calculations you can do in your pivot tables. Now most people use their pivot tables to summarize the values by summing or counting or averaging. And obviously that's great. No, some is the primary use for most people to view totals, but pivot tables have many other calculations built in that are not necessarily noticed or even used by pivot table users. So I've got this simple pivot table on my screen just simply having grouped by years like we spoke about how to do in a previous lesson. And I've also got the totals at the moment off the category south in those years. Now let's start off with our first example by shoving these category values as a percentage off the total. So I can see, for example, 104 grand for Dairy 2016 50 grand condiments 2016. But how is that represented? As a percentage? No. Let me start by in the field list. Drank in the total sales value filled intel a values area a second time. Now I could just change the field of God. I could do that, but I want to keep that and I want to see the percentage. In addition, so if the new column on now have I can right mouse, click one off the cells contain the value I will choose show values as so. A lot of people are familiar with this. Summarized by we can get you typical sums and counts, but not necessarily this area here show values at now. The two wanna explore to start with here on a percentage off parent row total on a percentage of parent total. I see well, that loads of good stuff in here. Now let me click the parent row total to start with. If I choose this one, you see the road Toto is 100% of the bottom. I still have value because I dragged that field in that second time but now concede represented as 100%. I can see the years representatives of percentage 50 58% really coming from 2017 sows. I could also see the categories represented inside that so can see 20% of the South beverages. It was slightly higher it as a percentage in 2016. You will see the value smaller 128 thousands 1 60 But it's actually a higher percentage of sounds volume for them in that year, so we got this useful information to explore as well. Now this is showing the percentages of the years. In addition, if I did right mouse click, go back to show values as do percentage of parent total, not row total instead of showing these as values, that technique will prompt me for what the parent is, what the base field is. I believe that as years now, the years are represented as a percentage still got that useful information I mentioned a moment ago, but I no longer have the grand total on another percentages of the years anymore. Off lost that. So maybe an example like this. I think the preference was the row total, but they've got a few different options. They're on order to distinguish the difference between those two. Especially now. Another useful extra calculation is to use the running totals in your pivot table. So I changed my pivot table around to show the sows for each month off 2016 so I can see how much was made each month. Let, but let's say I want to see the accumulation off that money month on month throughout the year. Now that is a running total. Slip me begin like before by Dragon Total sows value in again. Now you don't have to do this. I could apply it to the current field, but let's imagine I want to see both and then that's right. Mouse Click on one of the values in the new column show values as just like before, that we have two options. Here we have the Running Toto win values on a run in Toto as a percentage, let's have a look at both of them Running total in values. Let's give that a click that would double check. What based feel would I want to use for the running total Now old a month are so order, date even. Are the months down economy I have been grouped. Just click on OK for that And maybe if I quickly apply formatting so that makes degree of readability right now and these are the running totals, they can always remove these grand totals at the bottom. If you don't like, the family appears to mention its wife or doesn't appear it does. But, you know, in two different instances here, we've got a value slowly accumulating over time, and he can see how quickly increases the massive jump in this kind of area. And you can see that because of that 71 grand here. But you can see a the accumulation off those values. Now this is showing them as values. So remember, I can go back to where I came from and show as a percentage instead. Two coming up to 100%. Here based value is the order date. Here we see them as a percentage rather than a bunch of figures. How quickly accumulates to get to that 100% for the entire year that £571,000. So that is the running totals in your pivot tables. Now the last calculation or want to demonstrate is being able to rank the values in your pivot table. Now we'll believe the rank function came out in 2010 for pivot tables. So if you are using a version of Excel prior to 2010 I do not believe you have this calculation. But assuming that you do what I will do here is I've got two years worth of values for the South DRIPs, and I can read those values, but I don't want to sort that. Mr. I'm quite happy with the list sorted in alphabetical order by name, but I would like to easily see who the better sales reps were in each year. So I'm going to drag the total sows value field into the values area again to generate another column, and I'm going to right mouse click on one off those new total sows values filled, introduced so values as and we has rank as largest to smallest. I can choose that that will ask me what my based field will bay, and I'm happy with that to be the South rep in this data, and there we have it. I can then label these columns appropriately largest type rank in one of those that will update you have one for me and probably reduce their wit for little bits. That's a little bit crazy in a moment for value as small as that, and they're also probably get rid of these totals on the ends here are quite like the ones at the bottom, but I don't need those so designed tab on the pivot table tools, grehn totals and also on for columns only that will get rid of those at the end. We may be changed with that little bit, and this is what we have, so I can see that in 2016 Margaret Peacock was ranked number one and Nancy in 2017. I'm reasonably easily actually number two for both years has been Janet, and number free has changed. It was Nancy, first year government, but to Margaret next year. This is really just mentee Margaret changing place in the top three there. So that is how we can introduce a ranking which might be quite a useful calculation for you in your pivot tables. In addition to showing things like the value if you don't want to change the sort order or what it's come to used it now, just like I said in the previous calculations, I don't need the value as well. I could just have the ranking, could've various fields and how he sells apps or Rankin for them. But here have gone for both sigh conceding monetary amount and easily where that would rank without having to interpret those values quickly. 7. Calculating the Difference to Previous Years/Months/Weeks: hello and welcome to this lesson where we look at calculating the difference to the previous year's months or weeks. Now, in this demonstration, I have a list of my sales reps and I've got two years. So I want to see the difference between you know, this year and the previous year. But this technique can be used for whatever kind of dating time groupings you have now. The approach I'm going to make is similar to the previous video. This is going through one off those calculations in that list we saw. But due to what I feel is its popularity now common, it is to see trend and growth on variants. In this manner, it justifies itself to have its own video. Now this pivot table has already set up. As I say, all I need to do is bring in my total sours value filled again, and then in this new one that's introduced, I will right mouse click on the field. I will go to show values as and we've got the option off difference from now. We have difference from we always have percentage difference from Let's start with difference from Give that a click. What is the base field in the base item? What a base filled is going to be the years on the base item Old leaves. Previous was a difference in the previous year. So when I click OK, this is what I have. Now let me shrink somebody's fields a little bit. You go this one as well, and let's rename them as well. Let's put, um, difference year difference. Artist. According difference changes the two of them, and it's changed. Those two look like they should. That's changed him into a monetary full matter about decimals. Okay, so this is what we have at the moment. So I've got the 2016 values, the 2017 values. And here's the difference. You see, there's a negative shown for for Margaret. I can see something a tiny bit better that value for Michael there, but this could certainly do have some tidying up now. First of all, I've got this blank column here because there is no previous year to 2016. So what is the relevance of it? There's no is there, so I just can't right mouse click and hide that column and these totals on the end. I mean no relevance in his total one as well in the end. So I'm just going to remove both of them more designed tap grand totals. And I'll say, for columns only now, not really too interested in this total down the bottom for the difference I ever I don't think might. Maybe maybe it's quite a nice figure, but those grand totals definitely weren't relevant. Their out of here and in my last little change of think on this is to go back into the formatting for that field of introduced and because there is one next value and it's not that hard to see. There's only nine sales reps, but it's also know that clear. So let me go into my custom area and let's find a format that would show that in a red font as well. So here we go. Here's one of them. So as accounting, you can see the pound sign and also in red there posted your value to the left of the semi colon, negative to the right, and we click. OK, I never got rid of my decimals there. Did I like I did. In a previous example, let me take them out. Harry Guy City sister Format I one. We can see that Margaret is the only one who, despite performing very strongly, is actually, you know, heavily down on the previous year. Whereas people that Michael would also Steve or that have improved, I said, it's a minor improvement when compared to the other cells ups, slates, a demonstration of beyond to show the difference from something it doesn't have to be date in time periods. You know, I could have compared everyone to Laura as a sales rep or that kind of behavior compared to a different product. But this is a typical behavior to see this week, the last week, you know, and to show the difference. I wanted to show some formatting to highlight that negative, nice and clearly, and it was useful to do a little bit of tidying up, getting rid of some, you know, redundant columns as I saw it 8. Create your own Calculated Fields: hello and welcome to this lesson on creating a calculated filled in your pivot tables. So in your pivot tables, you can do your own calculations. So if there is data not in your original data set and you needed them in your pivot table, you can do it straight from here. Two would kind of work as a virtual column. You wouldn't have it in your own, your real data set only for your pivot. Now what I want to do is an example. Here is I have my sales reps and I've got how much they have made this month. So I've got my order date grouped. It's in the columns area, but I have filtered it to only show the data for this month, So it's always going to show the current month. Next month will be December that would look after itself with that filter in play. Now I want to know how much I have to pay these cells trips as a bonus. This month, they get 5% off their cells as a bonus, as an encouragement to sell as much as they can. Now, this is going to be my calculated filled to do that. You just click on, analyze at the top in the Excel 2010 or previous that will say options instead of analyze up there, I then click on fields, items and sets and calculated field That opens up a little area for me where first of all, I can name my field. So I would like to call it a bonus. Let me talk that in. And then my formula and I was just simply going to put equals. I'm going to reference a filled from my pivot table, which will be the total South value. I just double clicked on that field there to insert it, and I'm going to multiply it by 0 00.5 So what is 5% off that value in that field? Now we will have mawr calculated fields to add. I could click on the add button right now, then do another. As this is the only one. A mayor's real quick. Okay, just before come out of here in this formula, there are certain things you cannot do. You cannot refer to named ranges, and also you're limited to the types of functions you use. It won't allow you to create ever come around in function in here or to use V. Look up. You know you can't do any reference in within here. These fields will simply be summed. So there has to be the kind of field that can produce those calculations. Here is a simple 5%. I will click OK, that is both created and added to the pivot table. So I have my some of bonus so I can see that Steve making 210 gets £10.50 as a bonus, that is 5% off Twitter 210. But those who have performed exceptionally well, like Laura and Margaret was getting slightly better payments for their talent and for their effort. You see the values that is summed at the bottom, so going to see how much I am given away in total as a bonus as well. 9. Using Conditional Formatting with PivotTables: Hello and welcome to this lesson where we are going to use conditional formatting in your pivot tables. Yes, you can use conditional formatting in your pivot tables. What fantastic news to let's see how it may differ toe how you would apply a conditional formatting to a typical cell range. Now I'm just going to highlight the category values off this pivot table of God on screen. I've got a pivot table we can see of Grouped by mumps. I'm only showing this year's were for data 2017 0 for data. And yes, I got cats Retail's oh, hold on those values. Go to conditional formatting and look at setting up a role. Now, as an example rule. I want to show Eddie South that were greater than, let's say, £7000. I want to show them in green. Maybe that's a good target range for May. I want to see which product categories exceeded that value. So I'm using the great and an option I'm typing in 7000 and I'm going to choose a lovely green color. You go less of a fuel color. A nice green. Um, maybe we light in that and it's click. OK, thinking about making bold then or should I? Should I? Maybe that's good for a bit of boulders. Well, let's go for it. I have persuaded myself a do love, a bold fun. Just lost my green. What am I doing? Here we go. Cold green. I mean, I can see the preview in the background. It is working. Only dairy is changing color are Do not want the others to change. Let's click OK and confirm that rope. Now I'm sure you're thinking this, Alan. You're doing it to the pivot table. You have only highlighted a small range of sales. What about the other months? And you'll be right. I should have highlighted those as well, but off taking a bit of a lazy approach because now that's done. I'm going to click on my conditional formatting button and choose Manage Rose. Now notice in here on the applies to column that is only applying to that range. So although I'm in a pivot table, it is specific about arranged. Be five to B 12. So if this pivot table is going to change over time, this conditional formatting rule is not going to be very dynamic is not really gonna work for me if I start setting different product categories, ATM or less. This is no adjusting for me. So what I will do is click on Edit Rule Above and look at these two gem off a features at the top. Do it for a water cells in the pivot table. A lot of cells showing that some of the south value for the category. Now that's the one I want. Otherwise, if I choose, the middle one is going to do it for the monthly totals as well. I want to avoid those monthly totals only for the categories. This is now going to do it for the entire pivot, which is wired and waste my time highlighting those in the build up or do have 11 months here. But more importantly, from this point going forwards, this is now taking care of. If this pivot there will grows or shrinks or different categories appear different, values appear this can handle. This is going to update and do the conditional formatting rule. Now forever, I click OK to confirm each window. A look at how that's now done it for all of the months. I can see straightaway. February. Very good month. Loads of green going on all the way down here or some good bumps. Look at these months, August was fantastic. Well, maybe I just put the value a bit low, although dance in November, but it's excluding the totals for the mumps. Now, for one lost demo, let me go over to my original data set on that shoots a bottom off this list and pretend we've got a new cell. Let me put an idea in. Now, let me put a dating 4 December. There was not December yet, but let's pretend it is maybe type in the date. I'm not gonna worry about the formatting too much right now. Guys on. I don't really need to worry about these fields, but I'm just replicating them. They could be the same product. So that Medicare Ah, yeah, Gordon be become doing sewing care. Let's make sure it's a value that's going to be and sell. 100 of them would be good. So whatever 100 £100 says made 10 grand, that is more than 7000 isn't it? Uh, that's my Nancy. Sell it now. Let's go back to my pivot table. December is not appeared, but I haven't refreshed it. Let's go to analyze at the top or options if you in a previous version of Excel and Refresh the pivot table. And as soon as I do that, look at December data comes in four condiments, 10 grand. Most important of all, it's green. The conditional format in has it's still working there. The table on the other shake expanded like a showed in the previous lesson and in the pivot table adjusted and a conditional for minis there. So the key difference when using conditional formatting with pivot tables is Make sure when you're in the role that you've got the right options highlighted up here, you get that right, you are golden. 10. Show Top Ten Results: Hello and welcome to this lesson where we look at showing a top 10 results off your pivot table. Now, this can be a useful technique for your excel reports on your dashboards when you only want to show the best perform in once the best performing products from regions and you simply don't have the space to show a riffing. So in my list, here on my pivot table, I have the cells for all the products. Now there are 77 products, so shoving the south for all of them in alphabetical order by their name is not necessarily easy and quick to view what you need to view for your reports. So what we're going to do is begin by sort in the list. I said, I want it sorted by the name of the product. I'm going to sort it by the value sold, so know the right click of value and sort it largest to smallest. So now I have the best at the top and the not so best towards the bottom. I now want to show only the top 10. So if I go to the filter arrow for row labels and I for go for value filters in here. Among some other good stuff we have top Ted. Now, please don't be misled by that name. Because although I am doing the top 10 you can adjust this for the top 15. The top five, top 20. Whatever you need, you can even show the bottom 10 or you can show it based on the percent of the top 10%. So this is quite a flexible option that when you first see that that label top 10 is not necessarily giving that away. Now I am going to use the top 10 items by the summertime to south value. So if I click OK, that is it. I only have the top 10 products, Robin, all 77. I'm with the ones at the top being those that are alphabetically superior. That is not very helpful. So this is what I have. Please be aware that if my cells data set on the other sheet, if that was to change in time, this top 10 would automatically update in the just. You know, the filters, the sorts that you have on a pivot table are still automated. This living, breathing, reporting tool. It's different to the way that you would sort filter in a typical excel at range. This is different to that, so I can rely on this in my Excel reports now only show the top 10 and reduce the clutter, but also update itself as and when needed. 11. Five Useful PivotTable Settings: hello and welcome to this lesson where we look at five useful pivot table settings. Now there are many useful settings for your pivot tables, and we have covered some of them during the lessons off this course already. But let's look at five other, particularly useful ones. Now Let me quickly create a pivot table from a usual source data hit. And let's position the south drip and category in the Rose area and the totals about salvage value in the values area and a former them, as I have done in some of these videos. Now, the default layout here is what they call the compact form. And within this pivot table, if we look at the design, have at the top, which has some very useful sections on it, the 1st 4 off interest now I have dabbled with some of these during the lessons is being quite brief, as have quickly changed it to suit ah and needs at the time that these sub total useful is options. Subtitle settings. A useful by default, they're going to the top of the group here. Sometimes they do appear at the bottom, and he may have to adjust them. I always prefer them at the top. Personally, you have similar options for grand totals, which I have been changing during these lessons. But then the report light out, and there's that compact form. Now you have to other forms now, and some people like the tabula form that is of use. If I choose that now it puts it into a typical tabular layout. And there's also these options at the bottom off the report layout button to repeat the item labels so ever is. It mentions Andrew once here for this group, if you will, I can ask it to repeat it. And now I have Andrew on every line, and this could be quite useful if you're setting up the pivot table for extraction into a different kind of table or database, because now we'll look like some kind of flat file list that can easily be put into a different shape, excel or any other kind of database or spreadsheet, or maybe document on. Once again, these totals can always be removed. If they're in a way, I can remove these sub total simply. It might look a little bit ugly here, but I'm getting to the kind of data that may be looking at extracting elsewhere, removing the totals, removing that compact form. So get rid of blank sales. And essentially never. I just return that back to compact form. And I want to have a little change around of these fields again. So I'm going to move. Um, I think category in two columns and I'll replace rep with country. Where is country? Here it is. Okay, that's not too bad, but a finger might change category for sales rep. Okay, this is more like it. I'm looking for some blank cells and there were some previously but is a lot more here Now . Blank cells are not a terrible thing, but they're not great to look at, so be quite common for people to look at. Replacing that with something more meaningful may but maybe and plug the gaps. They have an option for that. For your pivot table. Let's quickly analyze at the top options on the far left. And in this layout and former area we have for empty cells show so I can just stick. Maybe I hyphen in there, you know, I could top zero whatever makes sense for yourselves. Click OK, and now our blank sales are covered by little hyphen that comes within a currency symbol, indicating that no money was made robin. That ugly, blank cell selection required common and useful settings. Now another one very close to that one for go back into My options button is toe also fit the common whips and update. So the data changes. Now Mawr countries were introduced. Exhale will automatically adjust a columnist, and that sounds pretty useful. And I'm not necessarily saying it's not, but especially when you're creating these popular dashboard reports, they lay out off them can sometimes be very important to you. And if you have taken some time to prepare in advance, changes like that can be very frustrating. Every time the pivot updates, excels automatically adjust in your column. Whips is the kind of thing a lot of people have already taken the time to do so I know it sounds good. Sometimes that could be very irritating, and he can come here, switch it off. You have ultimate control, and, you know, exhales not going to adjust it, even though it has your benefits in mind, is trying to look after. You don't always do what we want it to do now. The last two settings I want to mention here are both on the data tab. These are two of the most popular settings of all. We have refresh data when opening the file. Very common for people to check that box. Pivot tables do no update automatically. You have to update them. You have to refresh them and you have a button on the analyzed tap to do that but very common to check this box. So whenever the file is opened, a refreshes performed. It will be idle while it is open unless somebody clicks. Refresh. But at least you've got that kind of auto update every time the spreadsheet is opened Very popular for people to take that box. We have enable show date house above that is always checked by default. You can uncheck it if you find that to be an irritating feature and what that is if I check it for one moment and click OK is in your spreadsheet. If you were to double click a value So this 10.5 grand for and you're in Brazil If I double click that value. It will create a new worksheet with those sounds on it, so they oughta sows that made that. I think it was 10.5 grand. Uh, for Andrew in Brazil, that is them. So it drills to the detail. It shows you that detail behind that summary figure. You can keep that table but most likely don't want to anymore. So you have to delete the sheet at the bottom on because that is operated by double clicking. You can see how people may do that accidentally. And if they don't find that to be a useful feature anyway, there's no need heavy on you just asking for trouble when if you do have a habit of double clicking accidentally or maybe a colleague's, your customers do, and then you get his data back with this extra redundant worksheets on. That could be quite frustrating. Eso Why no uncheck The feature is not gonna bother you anymore. However, if you do find it useful feature, make sure it's checked, which it should be a standard unless one of your colleagues or someone has has disabled it for their own benefit. But you, being a fan, want to make the most off that functionality 12. The Power of Slicers: hello and welcome to this lesson where we will be looking at the power off slices. Now a slicer is a feature that came out in Excel 2010. So I'm sorry, but if you are still using aversion previous to that, you do not have slices yet and they are a new way of filtering data. They are very visual, very quick and very versatile. So let's have a look at these now. I've got to pivot charts on my screen, and these are just showing some data from my Pivot Tables tab, where I have a top 10 products list. Like in a previous lesson, we looked at how to do and have also got sales reps just sorted, largest to smallest. It's all have done of crater to pivot charts off them, removed two monsters sheets and you're just done a little bit of tidying up. Really. Couple of bar charts pivot bar charts, and we want to see what slices conduce you. So let me click on the first pivot chart, so we do this through a Thai below for a chart. It doesn't matter, and I'll quickly analysed Tab on the ribbon underneath. Pivot chart or indeed pivot table tools, and I will click on the insert slicer button. There are many ways of creating slices, but yeah, I guess being in the pivot chart, this is the approach that makes sense. They're going to ask me what I want A slice of four. Now I'm going to choose country. I could choose more than one field. At this point, you can have as many slices as you want. Live a creek. Okay, that will just be dumped in the middle off my, uh, my sheet. So my first job is to drag into a bit of space, and that's what have left this left hand side open for, so I could move into their that maybe resize at my slice of a little bit. So it kind of fits into the like Gap provided. Maybe because I've got the space are wide in the a little bit and And so what? So I have this slicer here and at the moment every time my clicker country, it filters my first chart. So this is connected to the first pivot table on the other sheet, I see what the ringer roast brat first at the moment and ever come over there. It is at the top there to come back, so it's filter that table that is affecting the chart. Look, a sells off that compared to the other products in Ireland as a quick around. Now I can choose mawr than one country by simply holding my finger on my control. Key should be able to slip multiple countries at the point, and when I release, it will filter for all of them. Now I'm using a laptop in a moment, so I have access to a keyboard and during that kind of thing is quite easy. But if you are a touchscreen device in Excel 2016 we have this extra button at the top working turn on the multi select. And now when I click a country ads in or if a cricket country, it removes it. So that is more appropriate, I would say with a touchscreen device, Where is when you have a keyboard? You know, really getting much benefit for next is quite easy. Just hold control and click with the pattern of mouth so you have Sophia Turner off. It's quite a luxury, really free to navigate without the need for that in select multiple countries without the need for that. If I used the Red X in the corner, that will clear the entire filter, bringing me back all of the countries. No can come back in and use it to go. So that is what our slices doing. It's a new way of filtering. It's very visual because you can see what the user selected. It's very fast. It's a one click, unless it needs do a little bit scrolling at the moment, and I can clear it in one. Clear. Can I have the multi select, extremely useful talk? No, it may be that I want to slices in here. Remember, I could have as many slices as I want. See what? I just click on my chart gain and I'll go back and the lies and I'm coming insert slicer again. Maybe this time I'll go for a product category slicer, and they would dump that right in the middle again, and maybe I'll try and move it into a bit of position at the top. Now we're struggling now for a little bit of spice, so really useful set in with these slices up on the options tab. On the far left is a columns. Set him. So if I increase the columns of this product category 24 and then let's look at re sizes to fit in, that would work quite nicely here we g O that fits in quite nicely, no above the area. And I could do the same to these countries on the left. I have this scroll bar at the moment because there are 21 countries. But if I select that slicer options tab, increase it to maybe two. And now that looks quite nice. That fits in quite nicely. No net longer. Do we need a scroll bar every country's one click away every categories, one click away. Now these slices I could talk to each other. So when you choose a category or when you choose a country, only the options that are relevant in the other slice of will show. So if I was to choose meat and poultry in category, you notice here that the Argentine, a country is shoved to the bottom on is kind of slightly grayed out because not relevant, we haven't sold any meat and poultry toe any customers in Argentina on this spreadsheet virtues, grains and cereals. Poland and Norway are down there, said, always moved to the bottom. The ones aren't relevant and kind of look expired. They go very light grey, where it springs up beverages. We have made sales in every country. To a degree, you noticed the top products chart is changing as well. When you show things like produce, there are only four different products have been selling in Brazil, so they only show for where's as you can change. Around France has been five different categories sold. Beverages all clear at least 10 different products sold to customers in France. So it's adapting. It won't always try and show 10. If there aren't tend to show just going to be a maximum 10. I'm showing in there due to the filter I have on the table now, one of the biggest strengths to these slices. Yeah, many people say the biggest strength of them is that you can connect them to multiple tables. And I think you probably saw that coming because I've got two charts and I haven't spoken about the other chart yet. Now, if I've got my first slices. Selected on the far left of the options tab is a report Connections Button in Exhale 2010. It's called pivot table connections that would just order pivot tables that I have access to right now. Now they're just named his pivot table for Pivot Table six, and they are on the sheet called pivot tables. This technique would work regardless of what shape there on how called is that You could have 14 different shapes of a pivot table on on this would filter it. Now it is possible to name your pivot tables name and then pivot. Table four and six is probably not that helpful. Or they would have only got to. I think I know where ones they are. If I can't sort is for one second go over to the pivot tables tab, click in a pivot table and go to the analyze have at the top. It's called options in Excel 2010 and there's the name on the far left like call this Top Products and then click on my other pivot table and name that one by threat. So sows boy rep. If I go back to my slices tab and back into that report. Connections Button. I now have meaningful names to know which one's which. So now we're comtech by rep and click OK, I want to make sure the other slicer is doing the same so that now as I filter, it's affecting both chance. You can connect these slices to as many pivot tables. Quest. Impetuous many pivot tables Andi or charts acts you need to, as he's relevant on extremely powerful filter into all that they talk to each other to only show relevant options. You don't get that with your standard filters. You have to know where the option makes sense to choose in or you'll get no data. And if anything connected to multiples, even if they're in different shapes, just takes your filter into a level that, you know before Excel 2010. We didn't really have so we sort of fantastic addition to Excel has been out a few years now. Here, seven years at the time, was doing this video, but as far six versions spoken about Ronnie, we're only talking about free versions at the time of doing this video. And so it's still reasonably knew, too, that the family A lot of people don't know about this feature on. If you're involved in credit reports, you know you want to know about this Feature slices extremely powerful, extremely useful. 13. 7 Slicer Settings you Will Want to Change: hello and welcome to this lesson where we explore the slices fervor to see what other useful settings there are Now. What better place to start then, by looking at changing the theme off the slices so they come in with this default Microsoft theme. It's always the same. It's always blue. You may want to change from that to put their brand image of your company or just your own creative uses to it for your reporting, your dashboards, etcetera. So if I select a slicer and all my options tab at the top, you have a gallery off slices styles that Microsoft provide. But if you come into these styles, you have the opportunity to create your own slice of styles so you can create one. You can provide a nice name for it. Eso I don't know what to call it for now, let's just call it Excel. Tricks of the name off this course you've got all these useful settings that you can get involved with and you can use to format them, and it's a check box. At the bottom is well to make it the default style for this document. If you put any more slices in. This is what we use. So there's a quick demonstration, but chose whole slicer on Went into maybe putting the board around this stuff at the moment . It's got this light blue border. I wanted to match the black borders they have got on my charts. I can go and do that and I can add a board around it. Only kick this quite briefs weaken, get onto the other Set in sober click. OK, notice how it goes bold so that I'm aware that I have made an edit to that part. We're going to head up. I could put a nice gonna header fuel color in here. What kind of pep we got? Try match up. The perp would have got on the bar chart. That's close enough. If it's no, it. I don't have got the what I looked like when you hover over a selected item hover over and UN selected item. We have data, you know. I showed you what they looked like when it didn't have data earlier in the previous lesson with the kind of light grey font we can change all this stuff. A selected items got data. Let's give that one these orange colors. I know. Yeah, And then that will do for now for a quick demonstration. But click OK to come out of these. There it is. And if I apply it, I can, including this one and apply it. And now with gold, our own style applied to these slices matching possibly with the colors used in the two bar graphs Here I can just slept my country's now I've got that orange asl e selected. We've data color. I didn't choose anything for the without. So for doof, go for grains and cereals. Norway and Poland don't look any different at a rest right now. That's obviously bad news. We should definitely change that. But it's just showing that when you create one, you're going completely from scratch. Your control is your slicer, but that's good. That means we can put our own personal style on it, or a company style on it, and take it beyond the de facto marks off stuff which we should be doing if we're doing Advanced Excel. So if we should ever own personal touches and it's not going for the marks off standards, that should not be you were doing. So that's gonna be the good place to start for, you know, some other settings for your slices to look at these slice of styles. But some other useful settings can be found when you right mouse, click on your slicer and going to the slice of settings in here. Some very useful stuff. We have the opportunity to change the captions displayed, so it's always taken the field name. You can add your own stuff to that. Now she could just add to something like Choose country or any other message that you may want to put in there completely adaptable. We can change the order that they listed. No, you know, it makes sense to have alphabetical here. I'm not gonna look at changing that, but depending what we're dealing with its open open for change. Now, what I do think is very useful to options are do you think of really useful? Are these on the right hand side? So at the moment I showed you how it visually indicates when an item has no data were certainly used to before I put my style on it and it had that, like great fonts and it moved them towards the bottom. It still does moving towards a bottom. It does that now. We have an option here to hide the items that have no data, and I think that is extremely useful. I definitely think that's the way to go. There is an option if you choose not to hide them, to not show the items deleted from the data source. I never really got the point off that. You know, if you've removed information from the original data source, then why do you want the option to continue to show in this slice? It makes no sense. So I would have that UnTech if you're gonna persevere of their visual indication. But I've wise if I'm gonna hide, only sold a little bit of relevant if I click OK, Norway and Poland have just disappeared from this slicer And I don't if you remember from the previous lesson Argentina is a country that we have not made any meat and poultry sows in. She have a click on meat. Poultry. Argentina is no longer shone, so they are some very useful settings. Especially, in my opinion, the Heisman of data that's not relevant thing that's really useful, noted now says, Choose country at the top, you know, very basic edit, but showing that you've got control over those captions. Also, to completely put your twist on this. Now, just a couple more useful settings. Far right clicker slicer again. We also have some size and properties settings. Now, if we go into here, there's a couple of like in here. One of them is to don't move or size with cells. Now, this is something we can do to the Putin, to the to pivot charts as well, something we can do big fan of this when you cried in your reports, you don't want these charts. The slices your affected by the insertion of columns or the widening all of them is data moves and changes. So I'm always a fan of say, no, don't move this stuff When things are moving around, you know I'm sorting lists that could move your charts and in this case, moving slices. Now, in this specific example on screen, I don't think of really gonna worry about that. Nothing that's gonna happen on this at the moment. It's just two charts, but it is something I commonly select. So I'm going to go for it here. Now, up in the position and layout Ariel, off this, the settings another really useful one is to take the disable resize in the moving disable , resize the move, then become out of here and show you what that does. So if I click on way slicer for product category, I get the resize handles and stuff, and I could move it. Let me undo that. If I try and click on my other slicer now, I don't get the resize handles and try and drag it. It doesn't any longer move. So once I've set up this Excel report, I would typically go around ticking those boxes, choosing those options so they are robust and they're going to stay in place no matter what happens. I've got my style and I've got my options. So now that shoes country cannot be moved. If you miss the button, you miss Venezuela and click outside of it. No resize handles appear or anything like that anymore on the big fan off that setting. So once your reports have finished, they are durable, are now protected, and they're gonna work the way that you intended them to work