Google Sheets - Pivot Tables from Beginner to Expert | Adam Steinfurth | Skillshare

Google Sheets - Pivot Tables from Beginner to Expert

Adam Steinfurth, Teaching Modern Tech

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
14 Lessons (58m)
    • 1. Welcome to the course!

      1:13
    • 2. What are pivot tables?

      3:55
    • 3. Why do we use pivot tables?

      1:35
    • 4. Preparing your data

      6:26
    • 5. Your First Pivot Tables

      5:38
    • 6. Add and rearrange columns and rows

      3:12
    • 7. Available functions

      3:39
    • 8. Sort and filter

      5:27
    • 9. Slicers

      8:26
    • 10. Drill down

      2:09
    • 11. Calculated fields

      3:57
    • 12. Group data

      1:56
    • 13. Summarize data

      2:27
    • 14. GETPIVOTDATA

      7:44

About This Class

Bring your data to life with easy-to-use pivot tables.

  • Summarize large tables of data in seconds.
  • Sort, filter, and SLICE your pivot tables!!!
  • Prepare raw data for use in a pivot table
  • Prepare raw data for use in a pivot table
  • Sum, average, perform statistical analysis, and more
  • Use calculated fields
  • Group your data inside your pivot table
  • Summarize your pivot table by week, month, year, etc.
  • Pull data back out of your pivot table with the GETPIVOTDATA function

This course is for beginner and intermediate level spreadsheet users who need to create Intermediate to Advanced level Pivot Tables.

Transcripts

1. Welcome to the course!: Hello, everyone. And welcome to Google sheets pivot tables from beginner to expert. So what we're going to do in this course is we're going to just start out talking about what a pivot table is and why you would use it. And they were going to take some real world scenarios where you get a really large table of data. But it's kind of a sloppy table, so you need to clean it up, prepare it for a pivot table, and then you'll create your first pivot table. Once you've created, your pivot table will go through how to add a rearrange columns and rows, all the different functions that you can use. Sorting in filtering the data and even using slicers to pivot the data on the fly to get into some more advanced functionality will show you how to do calculated fields. We'll show you how to group the data inside of the pivot table and even summarize it by year, month, quarter, etcetera. Then what you're all done with Pivot table will also show you a useful function to pull the data back out into your functions. So sign up today to get lifetime access to these videos, links to the actual files that were used in the video says you can copy them into your Google drive and follow along notes for the videos, quizzes and one on one support I'll see in the course Thanks. 2. What are pivot tables?: Okay, So to start out, we're just going to spend some time talking about the basic concepts of what a table is and how that's different than a pivot table. So we haven't created a pivot table yet. I'm going to create a small one on the screen in a minute, and then we're going to talk about the differences. But basically this pivot table is going to be a summary of a table. So you have to have a table first in order to have a pivot table associated with it and the pivot tables always going to be smaller and it's going to be dynamic, and it's going to have some controls on it. It's all just insert one here quickly would go up to the menu, left, click on data and click on pivot table. Then this dialog box comes up because it wants to know what they there. You're going to make this pivot table from so you go over to the right. In this grid is something that is waiting for you to left. Click on it and you can select the range. So here my table, I know goes all the way down to the bottom, so I'm just going to select the columns. If you look at this box, it contains the name of the sheet. So down here it's called Raw data is what we're looking at right now. Ignore these other two sheets. For now, we're just gonna be working on raw data in the columns. A through D Now would also be OK if we said a one through and we'll scroll down to the bottom and say the 109 You can specify the exact rose like that. Sometimes I just leave these off because if you add something at the bottom, you could pick it up. If you don't put the numbers in there, so we'll click. OK, now. So this is going to just tell us a Google sheets. That's the table I want to use. And then we're going to say, Let's put it in this existing sheet. So I want to see it right over here so we don't have to switch back and forth between sheets. It's probably a lot of times where you want to put it on a new sheet, but to show you it's better just to have it all on the same sheet, and it's saying, Where do you want me to put it in here? So let's left. Click are great again and we're just going to say, Put it enough to click OK, and then when you click create, it creates a pivot table. Now this pivot table isn't doing anything for you because you haven't told it how to arrange the data yet. And by the end of this course, you'll have a full understanding of what all of these mean. Ah, but we'll take a little shortcut for now, and we'll do what's called suggested. So Google Sheets is using some AI here to think, and maybe they want to do the average of sales for each ship mode. Or maybe they want to do this sum of sales. Well, let's just left click on this 2nd 1 to show you some of the possibilities, so right away you'll notice it. A pivot table is smaller than the original data, just kind of by definition, so pivot tables summarized data. They can add it, they can average it, and all of those operations are going to summarize what's in the table. So it's almost always going to be smaller, and it's changing as well. So if I don't want to show, the totals will come over here and left click show totals and this is a dynamic table. It just updated. It didn't change the raw data, and it didn't change anything else in the pivot table. But this pivot table is live, and it can be changed by you telling and to be constructed differently. Or it can update if the raw data changes. Right now, this is summarised by sales rep, and Pam has $8300. If I come over here and change, one of the sales for Pam will just make it big, so you can notice it was 8300. I'm going to hit, enter, and it goes a 27,000. So that's the basics of what a pivot table is. And next we'll talk about why you want to use them or different scenarios 3. Why do we use pivot tables?: Okay, so now that we've talked for just for men about what pivot tables are, we're going to talk about why you'd want to use them. And I tried to set this table up as you just a good way to explain it. But this is pretty typical. So one of the things that's going on with this table is that it's just too long to glean any useful information out off. All right, So if I want to see now, let's see what this paper tables telling me The sum of John Sayles really can't look at it and tell that the second thing is is that a pivot table can extract this information without you entering a single formula. So, for example, if I wanted to some John Sayles, I could do that. I could do a summit formula. So yeah, you can do the same thing, right? So this is the sum of John Sayles now, right away. You can tell I'd have to label this because you don't know what that result is and what else have to format it. Because this pivot table was smart enough to keep the same formatting of a currency with a dollar sign by it. But the other thing is this pivot table is flexible. So if someone wanted them by, pay em too well, that one happens to be in here. But if they wanted average instead of some, you'd have to change this formula on the fly. But with the pivot table you do is click in it and you go over to the right hand side and move things around so you don't have to know a single formula and you can change it on the fly. So if you're giving a presentation, people are asking you questions you can use pivot table. You can answer things dynamically in real time. 4. Preparing your data: Okay, so after you click that link in your course, you should see a spreadsheet like this. And if you go to file, make a copy. That should give you your own copy that you can save in your Google drive in this spread. She just has lots of little fixes in it that we're going to go over to make it ready to make a pivot table. So you can't just start with any data for a pivot table. You actually have to have it really close to just a fixed set of attributes. They all have to be in line. But once they are pivot table works. Great. So the first thing that you want is that your data is his columns of, like, data altogether. So we're going to say this is a sales register and this is the location of each sale. This is the date. This is a type of what you sold. And then this is the currency. How much you charge for it. The data needs all being columns, and the columns need to be in like data. So you see, Colin D is all text descriptions column Me is all currencies, but If we look a little bit closer, this is the first thing that you can fix. Your currencies should all be the same, right? So if you're trying to summarize these in your adding euros $2 that's not going to be very helpful. So we're going to fix. I just made them blue so you could see them easily in this table goes really far down. But I just made these first couple fixes on the top to keep it simple. And we'll say, We know the conversion rates of 14 62 euros is is $15 so one you changed the amount to. If you get a more formats down to the bottom or formats again and more currencies, we're going to change these to the U. S. Dollar. So let's apply that to this one. This one will say, is $1000 going to go to the four, grab that format, we're going left click on paint format and changed this one as well. Okay, so now we have all us currencies. Let's change that font color back to black. And as you're looking through the locations, you see ah, these air all compass directions right, west, south to the right. It's not a compass direction. So that's one more thing that I put in here. That's just it's not like data. It's saying the same thing. You know that's West. But if you summarise this, you're going to get totals or averages or whatever you do for West, and then the ones to the right are going to be separate. So let's just copy down the value of West Control seeing Control V. And we fixed that. So because I know those were the only ones that I switched. They may take you more time in the real world. And while we're checking to make sure all of the state is the same type, you could also have something slightly different. So this should be a date, but it's not a valid date, so this wouldn't summarize well, either, because if you're summarizing, let's say by the month of November, this isn't going to get picked up because it's not valid. So there's two ways to look for broken dates. One of them is just Oftentimes they shift to the left, so it's a valid date. It just naturally shifts to the right. This one's to the left. You can just eyeball it and see that that's wrong. Or you can do something more advanced. In this case, If you want to see if it is a date, that's just do the is date function. It's a little outside of the scope of this course. I just want to show you from one of the ways you can deal with this. So that would show a false for B three. Why you put the same formula down a row. It would show a true So let's fix this November date. Let's just make in November 18th and we'll get rid of our little data validation there and let's go to our next steps. So pivot tables always want descriptive headers. So when you're working with your pivot table, the values that you see when you choose what goes in the rose and what goes in the columns are going to be designated by these headers that are at the top of your data. So if you call this one region this one order date item in sale amount. Now you have descriptive headers, and I was doing that. She noticed that there's a column that's empty. If I hold down my control key impressive down arrow. I go all the way down to the bottom and there's nothing there. So what we'll do is we will left click on column C right click and just delete the column so you don't want any empty columns, but you also don't want any empty rows. So if I go in the column, see, we're going to hold down the control key again impressed the down arrow. So what that is, that's a shortcut where it takes the active cell. It's going to move it all the way down until the data ends. So in this case, I did that in it ended at Row 56 because there's a blank row 57 so we need to get rid of that. Otherwise, a pivot table might just stop at 56 because it won't know that you want to skip this. It will think that this is your table, right click on Row 57 Select Elite Row. We'll do it again to see if there's any other blanks, so you have to use a shortcut Keys for this, that control in the down arrow. If you have a long list because if there's 10,000 lines, you don't want to scroll through all of them. I just did that again. I didn't find any other blank rows. So we've gotten rid of the blank column and the blank row. And the last thing is, you don't want any totals or averages or anything else extra on this table because your pivot table might pick it up is it's not going to know it's extra. So let's go down to the bottom. I'm going to hold control again and hit the down key and their sin totals on this list. So these need to go away. We're going to right click on road 9994 and just delete these. That's what your pivot tables for you want. Totals counts, averages, you name it. We're going to do that with pivot tables to take them off of your table. And there you go. So we're ready to create a pivot table, so see you over in the next lesson, and there were going to create your first pivot table 5. Your First Pivot Tables: Okay, so now we're going to be using the file called your first pivot table, and make sure you grab a copy of it from your course so you can follow along. And where we're going to do is just go through the steps and create a brand new pivot table . This is the data that we're going to use, obviously, and I'm just going to put the pivot table. I'm going to start it in H two so that you can see it on the screen, and we don't have the switch back and forth between worksheets. So let's go to data and then pivot table, and this window comes up and it's just asking you to things. It's asking you where the source data is and where to put the pivot table. So first we'll tell it where the source data is well left. Click on this grid in Select the data with their mouths and you'll notice it does have ADDers. There's no blank rows, no blank columns. Click OK, and then we want to insert it to this existing sheet, and that's just going to keep it here to keep things clean and all again, just click the picture of graph and I'll select H two. Now you've told it to two things that needs to know to create a pivot table you click create and there we have it. So this is your first pivot table, obviously, is not saying very much right now, but we'll go through Ah, what these different choices mean in an, ah, high level overview of how to use them. And there's also something else going on here. It's called Suggested, which Google Sheets is using a I to guess at what you might be looking for. So if you're looking for let's see the 2nd 1 some of each for each category left, click this. It builds a pivot table for you. Okay, It's not what you're looking for. I don't think it's the each price. It's not even the total price. Um, but if you do see what you want in there, go ahead and use it. It'll save you some time. But we will undo that this one to share that you were going to build it from scratch so that you learn how to do it, and then you can customize it going forward. So let's say what we want here is for the rose to be the categories and in the values we want to have a total. We're not gonna do columns right now. We can do some of those later. Um, but the dollar amounts will come in this first column. So the rose when you left click, you can see why you use descriptive Petters. Because now you know exactly what you're looking at. And what you want is category for the rose and for the values. And here you're going to add and they'll be total. So there's different ways to come up with the value we said we wanted to totals who were going to summit. You could also count it. And what that would be is the number of lines for the sales for each of these categories. So this these two sales would be to want to be five. You'd have to some the quantity for that. So the council just saying kind of the numbers of transactions and there's lots of other options here is well, you almost always be using sums. Maybe some counts. So we're going to stick with that. Maybe using average also or do some and let's add a filter. Let's say we only want to see it for office supplies and technology right now. So say you're distributing this report in the person that those furniture, um, doesn't need it for some reasons to do filters, and we're going to filter it based on category. Drop this down and where we will just uncheck furniture. Click. OK, there you go. So that's a great demonstration of how pivot tables are dynamic. It did not change the source data, but it updated the pivot table and you can undo it right here, so they're super flexible. Let's put one more in here to illustrate what it would mean to dio columns. So let's say we want to see, um, again the category of sales and then each of their ship modes. So let's do data and pivot table again. We're going to do it in the existing sheet here, and my data that I want is here again now, something to consider if you're going to be using this first pivot table a lot, so it'll be getting larger and smaller. You might want to take this second pivot table and put it further away or maybe on a new worksheet. But for this video, since I know we're not going to be messing with this first pivot table again, I'll just put it right below Click Create gives me a blank pivot table again. And when we said we wanted to see what we use columns for, So when the columns let's to ship mode and in the Rose will do category. So this is going to tell us how much furniture was shipped by standard class in second class for the values. Let's Teoh um, quantity a most some the quantity. So this will tell us the numbers of each, and we don't need any filters on this one. So that's two examples of pivot tables, a super simple one and one that's a little bit more complex. And now that you have the basics down in these following videos, we're going to get a little bit more advanced. 6. Add and rearrange columns and rows: okay. And to show you some of the flexibility of pivot tables and really why they're called pivot tables is that you can just move the data around. The first thing that will dio nice and simple is, let's just say we want this pivot table toe have thes items, the big truck, the lawnmower as column labels instead of road labels. If you come over to the right, you'll see now that items is in rose. If you left, click and hold it and just drag it down to columns and automatically rearranged the pivot table. Now you can see that doesn't really look right this way because the values, if you look, are still as column. So if you take that drop down and you make the values as Rose, you've taken this entire pivot table and just turn it on its side. Let's put it back the way it was before. I think it looks a little bit better, and we're back to where we started. So let's do something else. Let's say that she wanted Teoh. You still want to look at the items, but you want to look at the sales also by region so you can without redoing things. You can just add this on top. And but there's a couple different ways to add the regions. The first thing that will do is we'll add it as a column. So if you left click on add in pic region, it will drop it in there. So this may be what you wanted. It really wouldn't be how I'd want to look at it. What this did is it still doing the same functions? But it's breaking them out by region. So there were three functions. It's repeating it for the one mile left the north, and it repeats it again for the East. This is kind of ugly to look at, I think so. What we really want to do is we want to take the region and we just want to add it to the Rose. We're going to put it, but low item, it takes big truck and it gives you the region breakdown here, and it's grouped nicely and it also gives you these controls. So this is a minus right now. If I left click on, it's a plus and that's saying this has been collapsed. We can do it for the other rose as well. And you can hide the detail. And if you want to show the detail again, you just left click on the plus so you can do this. They say if you're presenting this data, you can do this live and you can show in high things as you go along. One other thing that you can do to clean it up a little bit is take off some of the totals if you wanted to. So this big truck is getting totaled here. It's also getting totaled here. Let's just take off show totals for each of these and this. Clean this up a lot. If you like it better this way. Turn it back on if you don't. Now, if you wanted to look at this by region and then by item, that's easy to. So you just take the region and you drag it on top of item. What that saying is sort by region first and then by item. Same thing can be done for the values. If you wanted to show the percentages first, just trying that on top rearranges everything, so that's if you will, the way that put the pivot into the pivot tables. You can just move things around on the fly like that and have it arranged any way you want . 7. Available functions: Okay, so we're going to go back to some of the data that you've used previously in this course, and it's just a simple sales ledger and has dates, different types of items, regions in which they were sold and the the amount of units that were sold. So let's changes to units for this example, and I have a pivot table here. We're going to go through a scenario where we're going to add values. But then we want different types of values. And for this let's say we want the total amounts sold for each item and we want to see the percentage of that amount. Then we want to see the percentage of the total that each of those amounts is. And then we want to see the counts of the number of sales. Previously, we've just used, sometimes to the point of this is going to show you different types of functions that are available. So for the values we talked about using units and the 1st 1 is okay, so this is showing the some of the units. The some function just adds values together. Look at this and the largest amount of sales by units was for the big trucks. But if we want to see what percentage of the 13 66 is of 42 07 let's add another value. So we'll go to add will do units again. And if you look at this, drop down by show as we want to show the percentage of the amount for the column, because we can see here that big truck sales were 32%. But if you want to use another function, maybe you want to see the council. You want to see the number of times it was sold. If you had a sale of 34 and five, you want that to show as three. Because there were three sales. There is a function available for that. We will go to add when do the units again. So there's three different counts available. Count A would count the number of non blank cells, so there was the number three the number three and then spelled out T h r E u. Account that as well. If you use just count, it's counting. Only the valid numerical amounts you count unique will count the number of times that number comes up that hasn't come out before. So if you have sales of 344444 and five, it would come up with three because there's only three different numbers there. So that's your different counts. Will just use regular old fashioned count. And if you watch over here where says some of units, that's one that we're on, it's going to change to count. I just want to touch on a few more of these functions that are available so I will click on the summarized button again, and the next group of functions is average max men in median. This is really to analyze and, well, you know well, literally, What was the average medium? A. Was what was the middle value and Max would be the largest value of men would be the smallest, and you could multiply them. So this is going to give you a really large number very quickly. There, this example is not really where you want to do the product, but you may want to do some statistical analysis, and if you did, you have the standard deviation. This one looks at the sample one with a P on the and looks at the population and the variants of sample variance of population. So there's a decent number of functions to select from here, and you could build your table from all of these. 8. Sort and filter: So for the sort and filter, I picked a table with some more columns in it of different types of data, so that we can see a few more things when we're sorting and filtering. And if we go to the pivot table right now, I have a pivot table that has the the rows of category in subcategory, so furniture would be a category subcategory bookcases, chairs, etcetera. That's what we have for the Rose and for the columns we have ship mode. So when we're doing this analysis, we wanted to see what mode these different categories and sub categories worshipped by for the values. We just have the some of the quantity. So this is saying we shipped 1222 furnishings via standard class. All of this data can be sorted and the war filtered and then come over to the right, and the first thing that we're going to do is go over the sorting capability, which is in each item that we've picked in the Rose and for the columns. And then we'll go down to the filters that we can add so we'll look at sub categories for the rose. So this is this column here, and we can sort this in several different ways. Of the the easiest thing to see is that now it's sorted ascending, though that's alphabetical ascending B C f. If I just change this to descending, it flips all of these around. We'll change that back. So if you wanted to sort now remember, we're still a sub categories that this is sorting these and then it will separately sort these and then these. But we'll keep them together with the totals here, so let's sort it by the some of first class. So before I collect this, come over here and look in first class isn't necessarily going in order. The tables are at the bottom. But if we sort by the summer first class, this goes in numerical order. By that criteria, you could do the same things in the category. So we will turn this off by just changing this back to sort by subcategory says the South Medical again will go to category and we can do the same thing here so we could sort by the some of first class. And that has changed thes so that its source. By this amount, 6 37 is the first, then 6 98 then 2128. It's the same data. These air the same totals, of course, hasn't changed the underlying source data, but it is moving all of these around. You could do the same things with columns, so this is sorted alphabetically, left to right. I won't do all the exact same things because you just saw it. But let's just say descending flips him around, but it keeps a grand total of the end. You could turn that on and off if you wanted to, just by using this check mark. But we'll leave it on for now. So that's how you sort the date. And once you have it in the pivot table now the other thing that you can do is a powerful features. You can filter this data, so if you don't want all of the source data coming into the pivot table, you can filter it before it gets in here. So that's the difference. If you use this filter on the menu, it just filters the view. But we're going to use this filter in the filter table and it's going, Teoh, you're gonna even just picture it in your mind of the filter. The source data is going through it. Some of its getting captured by this filter is not coming through. So let's say for this analysis, same day shipping is irrelevant. We just don't want to look at it. So let's filter by the ship mode and then you get a box here in the status right now is showing all items. Absolutely. If we want to do a filter, we don't want to show all items. That's the point. So let's left click on that, and then you have a few options here on how to get this done. So since we know that we just don't want to show same day, we could remove the check mark by same day. Click OK, and that column just disappears. The grand total has been adjusted not to include that column. So all left click on the X to get rid of that filter. So I'll do another type of filtering here to show you a concept of more dynamic type, so we can also filter based on date so we'll collect the order date right now. it's showing all items left. Click on that. We're going to filter by condition, and when you do condition, you get a drop down of all of these in a pre built functions that it can do for you. And let's say we want anything, what the date is after. So the data is after, and if you left click on the box below it. You have thes three built in options today, tomorrow and yesterday are all relative. So if you do this filter today and use today and you look at it again tomorrow, it's going to change because it's aware of what the date is. But we want to choose an exactly So let's click exact date and let's do 6 30 17 Right now it's showing 23 7 45 as a total. If I click OK, pay the table updates and it's 8000 and eighties, so it's only included dates after 6 32,017 So that should be a good primer for you to show you how to use a filter in the sort functions that are specific for pivot tables. And we're not talking about data sort up here or data creative filter. We're talking about the sort methods here in the filters down here 9. Slicers: Okay, If you're using Google Sheets, let's say you have a table of data like this, and you want to be able to create a pivot table from it and filter it quickly and easily. Going to go through the steps to create something like this and add slicers to it in these lectures will cut through the data that they'll do it a different way than the built in filters will. So we'll go through what that means in how to do it. This worksheet that we're looking at, we're going to keep the before worksheet like this with just the source data on it. We'll keep that after worksheet like this, looking like when we're done and we're going to work on this one in the middle. So what we're going to start out with is just this table of data. It's transactional data in each row is a unique record, and it has nice headers on it, and there's no spaces in the data. So this is ready to go to be made into a pivot table, and I want to take this data and I want to look at it a few different ways, and I'm actually going to make two different pivot tables and you'll see why in a minute. So I want to show the sales by item. I want to summarize them. That's why I want to do a pivot. And then I'm going to create another pivot table to show it by item and then by date, so we'll drop the's to pivot tables in here relatively quickly. Will do data hit the table and our source is going to be this table, including the headers. We'll click. OK, we're going to put the pivot table on the existing sheet. We'll just drop it over here in G two. So the rose will be the item description like we had talked about and the values will be. Let's say we want to look at the quantity sold with the quantity, and we want that to be sound so we'll leave this drop down box at the value of some, and we have a nice, clean pivot table here that's just summing the quantity of sales by the item description. We're going to drop another one to hit. The Harris will go back today to pay the table. We're going to also do that on the existing sheet, we're going to use this table as the source. We're going to put this in. We'll leave a little bit of room because pivot tables expand and contract based on what you do with them will come down to, let's say, Ah, G 13. That should be enough padding. Create that. First, we'll add a row with the date going to go in and right click in group these dates, we don't to look at every day. So if you're watching this as part of a course, there's another module that covers us. But for now, let's just suffice to say that this is just different sections of a date by which you can group things. Let's just do it by the month. We'll add our values in right now. We'll still do quantity, and we also wanted to add the items to this second pivot table. So we'll drop in another ro, the head of item description here ago. So we have two different pivot tables. They're both showing 336 items. They're showing it in a different way. So we built to, because we're going to show you that this way of filtering called slicers, will act on both of these pivot tables, as long as both pivot tables air in the same sheet and they're working on the same source data. The slicers were also worked for charts. This is just about pivot tables right now, but if you do it with charts in pivot tables, as long as they're on the same sheet, it's going to be the same thought process. So the first way to filter these, if you weren't using a slicer, would be the left click in the pivot table. So you get the pivot table editor, go down to the bottom. You can add a filter. Let's say we want a filter by item description when you're using a filter. If you left click on the status right now says, showing all items you can either filter by condition or by values that's going to be the same in a slicer. We're going to take the garden hose out, but now we're going to talk about some differences. So one difference is it only applied to this pivot table because filters are specific to one pivot table. The other difference is you don't see this being filtered unless you left, click on it and look at the editor. So if you're showing someone a pivot table and you filtered it, maybe you did that on purpose, and you don't need to show that. But if you do it in a slicer, it will be obvious that you did it. So it depends on whether or not you want to prepare it and have someone be able to change it or if you prepare it and you just want someone to view it after it's filtered. So let's take this filter out. It was only applied to this topic that table. If you remember, we're going to hit an act so you'll see and you'll see when we remove the filter that the garden hose is going to come back into the table. So let's left click on this X. You have the original pivot table back now, instead of doing filters were going to do the slicers, and you're going to see how it operates differently. They will go to data slicer, and now it's asking for the data range. So pick the table as the data range. Don't pick the pivot table that we're going to be actually slicing the source data. We'll click. OK, here's the slicer. We're going to move it over to the right and let's take a look at a few of the options right now. The first thing is that we already selected this, but it's filled in. Here is the range. So I picked the table. I included the headers so the headers will come up when we use the slicer. You'll see that in a second, and we'll skip the column for now and want to make sure that this is check to apply to pivot tables. Yeah, that unchecked stock reply Paper tables, right? So pay attention to that, and then you can also customize it. You can give it a title, say maybe, Ah, filter by item here in the color in the fountain and all this stuff. So let's leave that the way it is. For now, let's say we want to filter by item descriptions that we're going to choose a column. It can only do one column. You have to add a separate slicer to do another columns. We will dio item description and the first thing that you're going to notice if someone comes into this pivot table, The slicers clear right. It's not buried inside the settings of one of these pivot tables. It's here for everyone to see. And if I ago you have the same options of filter by condition or values, so you filter by condition. You could do something like select only the dates after July 1st, 2019. Or you could do something more events like selecting only things that have the word hose in it. But Arliss has kept pretty simple here on purpose of what we're going to do. We're going to do the same thing again. We're going to take out garden hose. Look at this. First there's four items. This has four and four click OK in everything updates, even the source table updated, so that data is still there. But it's hidden right now. If we also want to filter by date, you just add in another slicer. So let's say data slicer. Uh, and that gave me an error. Let's go ahead and select the range and then do data slicer so it doesn't get mad at us. All right, we'll move it over to the right so all the slicers on one sheet have to work on the same range and there, she specifics. So if there's a pivot table on this data on another sheet, it's not going to be filtering it. And another thing to keep in mind with slicers is the changes that we've been making are only going to work and are used to profile with our sheet. So if you're sharing this spreadsheet and you want thes settings to apply, when someone else comes in here, you have to add it to slicer in pic set Current filters is default when someone else uses a sheet, then they'll see the same filters that you have only if you do that, so we have to select a different column. Let's due date. We're going to drop this down. We're going to filter by condition. We're going to say we only want it if it's after, let's say August 1st, 2019 now, before I click OK, this table you can see has July values in it. In August, this is totaling 2 80 will click OK, updated both of them. So only has August in this has much less Stan had before, and now it has thes both of these filters applied 10. Drill down: anything in a pivot table. I'm going left. Click in it. That's a value can be drilled down into and how you can tell what our values is. Obviously what's under the darker headers to the right, but also, if you are left like you get the pivot table editor and you go down So we have rose. We have columns that's blink. We don't actually have any columns in this table, but then you have values. You see quantity in total. Here's quantity. Here's total. Both of these can be drilled down into. So if you come into here and you want to see what the 20,485 consists of, so you want to see an output of the lines from this table that our furniture shipped by standard class, it's all double click on it. And quick is that easiest by it. Output all three of lines. And if you ah, look at these totals, you use a shortcut. I usually if I want a quick some, I'll just highlight the range and then I'll look in the lower right hand corner and it will show me the some. But that's probably a little bit hard to see, so we can also just do a some formula. And there you go. 20,000 for 85. This is all the lines and it contains all of the columns from this pivot table. Now, one thing to keep in mind is I want to suggest creating a pivot table just to then be able to drill down into it. So this table here, you wanted to see only standard class I've already applied to filter. You can see that by the fact that these arrows air showing up. But I went to data on and I turned to filter on and you want to see the amount of standard class furniture shipments, go to the category and remove everything except furniture and click OK to do the same thing where that total is 4 20,086 Let's remove that filter. The purpose of that was to show you that drilling down is great if you're working from a pivot table, but just don't create a pivot table to only drill down because that's an extra step that you don't need 11. Calculated fields: Alright, If you're watching this video, you're probably trying to figure out how to add a calculated field to a pivot table. So I'm gonna show you that. But first, we're going to try to start at the beginning so we can understand the process. We're looking here at the raw data then and do the pivot table on. Obviously, I kept it really simple so that we don't get distracted from what this is is a table of sample of salaries and the number of years of college that the people have taken that have these corresponding salaries like any table of raw data. This is only so useful. What you want to do is great. A pivot table with it so you can figure out what the date is telling you. Order. Do that. I'm gonna go to data, do pay the table. There's lots of different ways to do these pivot tables, but I'm just going to do it in one particular way just because I think it's easier to show it to you this way. I'm gonna put the years as the rose and then I'm going to put the they're not going to make the values that salary. But if you watch young and actually make it the average salaries because that's going to because that's gonna work more for what we're gonna use a calculated field for. The salaries got added in this horrible number formats. So I'm going to change it to number and then decrease in decimals by two. What I want to do with the state, as I want to say, how much shallower you getting per year of college at this point in. In order to do that, you have to divide that salary averages that I haven't called B by the number of years of college having A. In order to do that, you want to use a calculator field, go down to values, you're gonna add a field, and you're gonna to select the bottom option, which is calculated field scroll down so you can see it. Let's name at something that is meaningful and said a calculator feel one. So let's say salary per year of college. How's that? The formula is going to be salary to fight a by years of college, and this part's a little clunky, I think is I can't use my malice and go up here and click in it. I have to remember the field values than the one that salary I can see because it's up here but the year of college. Not sure that's exactly what the tables, the raw data, said. Let me go to it years of college. So I had to type that in as my other field. And it doesn't work whenever you have spaces. Need that wasn't gonna work is like practices. Hopefully, of course, this actually isn't working because they're spaces in the name. So you have to tell Sheets. Look, this is a string of characters that starts here, and it ends here. So put in that little single quote sign and then you see it, figure it out and again and put in that ugly number format. So just tell it it's a number and that you only want one decimal point like liking to decrease twice. And here you have to see you have a pivot table with summarized data from the table before , and a calculation on the summarized data at this calculation on the right isn't calculating from the raw data it's calculating from the summary of the raw data that you created, so there's infinite combinations that you can do here, average some, and then the calculation of those is be anything as well hopefully that help walk you through the basics of entering a calculated field into a Google spreadsheet. 12. Group data: okay, if you're using Google sheets and you want to create a pivot table, but you want to create groups within that pivot table, you can do it. Now. It's just a couple clicks. So we're looking at this table here and we can see we have different regions, right way of west, east, north and just one mile left of north. We want to group together the compass directions and then have this guy in another group because he's a little bit different. So we want toe. Analyze him differently. Let's just say so. Let's create the pivot table first, so you go to data pivot table, and as long as you have a cell selected within this table, it's gonna figure out that you want the whole table. Just make sure you don't how many spaces in it make sure you have good headers. Here's my pivot table, I said. We want to analyze the region's by groups, So first, let's make Rose with the regions in it, so we'll go to values, and we're gonna say the value that we want is the amount it's going to assume that you want to summit, which is right so it Build a stable out, right? And, well, maybe just at another value here to make it look a little bit more informative. Well, ah, We also care about the item, right? Poor item. Okay, here we go. Now we said we want to look at these three together and then this one on its own. If you want to do highlight the three that you want to group separately, right, click and create a pivot group. Now it's put the three compass direction, territories together, and then one oddball together. So these really should be analyzed your soul together. Just collapse this group, collapse this 12 and just look at the two groups together. Hide all the other data. That's not important. Lots of way to group data and a Google Sheets pivot table. 13. Summarize data: okay, If you have a table of data and Google sheets and you want to look at it by a certain type of date, let's say if you want to look at it by month or you want to look at a bye week, the first thing that you want to do is create a pivot table, so you'll notice that I have selected a cell within this table of data that I want. The table goes down, but Google Sheets will figure out that that you want the whole thing as long as you're inside of it and make sure you don't have any blank rows. Do ah data pivot table, And this is going to summarize the data that's on sheet one, but it creates it on a new worksheet. What we want to do is we want to start out with having the dates down the left hand side, and then we're gonna group the dates, look at them in different ways, so we want the rose to be dates. So do add date. You want to look back at my table. This is a lot easier if you just have descriptive better, so each one of these is Tell me exactly what's in the column. So I my dates on the left hand side, and then I wanted to it by item in each column. So let's add items to the columns and then she eats wants to know what to put in here. So I'm gonna tell what put to some of the amount for each day in here because I'm gonna look at the sales. So for the values add amount, and it's smart enough to assume that you probably want to some. And if you don't want thes Korean totals on here, you just take this check mark off. Did not show totals. We're gonna leave mon for what we're doing, but they're easy to customize. We're just gonna do months, but it's gonna work the same way as if you do weeks come over to the column that has the days on it. Select any one of the days right click and to create pivot date group doesn't matter what day you pick. It's going to do this for all of them, and you can do this by week by month. You can even do it by day of the week. But just to keep things easy. Well, Dio Month, and it summarizes all of the data from sheet one into each month. It's only work if you have them as valid dates. So if you have, if you have things in this column that are working right, go back to your data and make sure that these are valid. There's several different ways to get validates and check to make sure that they're working . So that's mostly it. We've summarises data by month with just a few clicks. 14. GETPIVOTDATA: Okay, So to give you an overview, we have, ah, table of source data here, and it's a listing of transactions of people selling pet supplies. And then we created a pivot table, and it has two values. It has some of the total price in a some of the quantity. So this table is telling us the dollar value of selling these cat toys in how many we sold . You come over to the right and you're just wanting to tell a little story in these boxes here. You just want to extract Hey, the leashes that we sold. Let's break them out between brick and mortar and online. So what you think you would do and will work if you don't change anything is you would just hit unequal sign and then come over to the pivot table. And let's say we want leash sales for brick and mortar left click in Cell D 11. It enter, and there you go. So you have $2537 that matches here. If you want it for online, you can take your mouths. Go into the lower right hand corner of the cell that you have selected until it becomes a plus sign. Hold your left mouse key and drag it down. Yes, both of these values coming up, but that's what she wanted. And you're never going to change this pivot table. Then you're fine. Just leave it like that. But if this pivot tables dynamic going to show you how would break these formulas and then what you can do to get around that? So first, all left, click into the pivot table. And now bring up the pivot table editor on the right hand side and we'll scroll down to these two values so we have them in this order. Total price and quantity. Let me just left Click on one and I'm going to take the quantity. I'm going to put it above total price that this is going to rearrange my pivot table, and it put the quantities on the left in the total price on the right. If you look at my formula here that was pulling the price now, it shifted the quantity. The formula didn't change. If I left click in, it's still pointing at the 11 but the 11 has changed. It's not returning the same amount so there is a way to get around this where if you build it formula looking at the pivot table, you can specify exactly what value you wanted to return. I'm just going to control Z just to get things back to how they were and will come down into Cell H 10 and going to type the equal sign. That's how you always stored a formula. And then I'm going to start typing the name of my formula, which is going to give get pivot data going to come down left. Click on that with my mouth and Google sheets like it always does is going to give us this little helper text to try to help explain what's going on. The first field that it's trying to look for is value names of value. Names are the darker ones here at the top, some of total price or some of quantity. I'm wanting some of total price, and since this is a string, you started with quotes and I would say some of total price. Now that's not case sensitive, but I capitalize it the right way just so it looks nice. I think that's important, right and then you do a comma because they're ready for the next value in the next value is just any cell in this pivot table. Now the best practices to choose one near the upper left hand corner. Because if you change his pivot table a lot and it shrinks and grows and you choose the value down here in the lower right hand corner, it may not be part of the pivot table if you shrink to fit the table. So we're just going to specify Be four and we're going to put dollar signs before the column in dollar sign before the roe and not just fixes of reference. And since our pivot table has a little bit more detail below, you're going to need to specify these two columns. So you're going to say the product is leashes and the location, his brick and mortar. We needed to be leashes at the brick and mortar location, So leashes, if you look over to the left, are the product in the brick, and mortar is the location, so force. We're looking for products, so Stuart quotes again. Product comma is leashes and leashes is the value that I typed in each night so I could type in leashes. But I'm going to make this formula a little bit more dynamic because if I come in here and I want to just type over leashes and tight fish food, this formula will still work. So we're going to say, instead of typing leashes, we're going to click on hte nine and I'm going to go into this formula and I'm going to fix the row so I could drag this formula down. You'll see that in a minute that said, the products leashes. Then I need to say that the location his brick and mortar, which is in G 10 again I'm going to use cell reference there. But I'm not going to fix the road that specifies the product, because when I drag this down, I'm going to one G 10 increment to G 11. So all type enter now and then I'll go back to H 10 going to copy it. I'm going to paste it into H 11 and an increment it down. So this is the gift pivot data function again. But instead of leashes and brick and mortar, it's looking at leashes, an online so If you come over to the pivot table leashes online, 20,000 won 43. So if you watch me do that and you see these return the exact same amount, you would say, Why would you put so much more effort into it just to get the exact same amount? Right? Who cares? Well, there's so many times that you can build a forming lead that relies on a value, but then that value changes. So if I come into this pivot table, so let's just come in here again and change the order of the values and see what happens. So if you look at the formulas, the formulas that we're simple cell reference broke, they're not returning what you wanted, so that's returning. 10. That's the count of the quantities. That's not a dollar value. The formatting stayed the same, so it still looks like it's dollars, but it's not. But the get pivot table function still works because it's looking for leashes in brick and mortar, and it also knows that you want the some of the total price. So as with any formula, there's a few things to keep in mind. One of them is the sum of total price. If you left collected that and you just customize that header, let's just say we've called it, UH, total dollars. It's going to break the pivot table function because there is nothing called some of total price anymore. As it's looking at the customized better. I just want to change it to say total dollars, and it works with the custom header, so we'll drag down the new formula. And there you go. So there's one more I'm just going to delete thes. For now. I don't like looking at a broken formula. Another thing to keep in mind is that if you change the pivot table, you can rearrange things as long as he's values that you're referencing and the give pivot data stay there. But if they're not shown in the pivot table, they won't return. So they get pivot. Data function is built to pull things out of a pivot table if they're visible. So if you take out the total price value, this function is not going to work anymore. So it's not calculating the total price. All it's doing is pulling it from the pivot table and the values not there. The function doesn't work. Undo that change in there it is. So as you're building out formulation, you can put this function inside of other formulas to pull these results out, and that will work better than just a cell reference because it updates as the pivot table updates.