How to use Pivot Tables in Google Sheets

Using pivot tables in Google Sheets is one of the best ways to analyze your spreadsheet data. You can learn what all those rows and columns really mean no matter how much data is in your Google Sheet. In this video, Scott Friesen shows you how to get started with pivot tables even if you are a beginner.

Click here to get more sales with Pipedrive

Transcript:
​This video is brought to you by Pipedrive. Pipedrive is the easy to use CRM designed to increase your sales. Stay tuned to the end of the video to learn more. When dealing with your spreadsheets it can be difficult to determine what all of this data means. So in today's video I'm gonna show you everything you need to know about how to set up your own pivot table, right here in Google Sheets. Hello, everyone, Scott Friesen here at simple activity helping you to get more done and enjoy less stress. And I know a lot of people tend to be intimidated by a pivot table, but I'm gonna show you how it is much easier than you think and how you can interpret this data in any way that you like. What we need to do is come up here to Data and about halfway down we've got the option to create a Pivot table. We're gonna select that. And the first thing that we need to do is select our data range. So we're gonna select this little icon here to select that data range. And what's most important is that you also include the headers. Because that is key as we create our pivot table. So I'm gonna come down here. I'm gonna select the data range that I want including my headers. And I'm gonna say OK. And the next thing we need to decide is if we want to put this pivot table on a new sheet or an existing sheet. Now I typically choose a new sheet. That's what we're gonna use today because it just gives us a lot more space. But if you want you can have a pivot table right over here, to the right. So I'm gonna say, Create, it's gonna open up a new sheet and now we've got a blank slate, something for us to start working with. Now here we've got our rows where we're gonna decide what types of data, what types of information we want to display here. And then on the top, we have our columns where we get to choose what we want to display here. And the power of a pivot table is it's gonna bring these two values together. And it's gonna show us all of this great data here in between. Now on the right hand side, we have our Pivot table editor and right off the bar you can see that it's giving us a few different suggestions. Now there's a chance that Google may have guessed correctly as to what you are after, but we wanna look and get comfortable with using and creating our own pivot table. 'Cause there's a good chance there's something specific that you're after. If you ever need to change the selected area you can always do that here as well. For example if your data set expands or includes more information but we're gonna leave that for right now. So first off, let's start with Rows. We're gonna hit this Add button and you can see we have all of our headers from our dataset. From everything here that we see on sheet one. So for myself, for our example, I'm gonna choose my sales Rep. So I'm gonna select Rep and here you can see they are now all displayed here on the left hand side. Now I can choose if I want to order them in a particular way, because we're dealing with names it seems to only make sense that I'm gonna use it in ascending order. And by default, we're gonna have this show totals checked. Now you can always uncheck that but in most cases, when you're dealing with a pivot table you're gonna want to see this grand total data at the bottom as well. Now, the other thing that you should note is that it's not as if this Add button becomes disabled because you can actually layer on additional rows and additional columns as a part of creating your pivot table. So for example, if I hit add rows again, this time I could choose something like Items. And here you can see, now I've got a second row, which is breaking out the products which these sales reps have sold. So I can see, for example Andrews, has sold binders and pencils, but someone like a Jones has sold binders, pens, pen sets, and pencils. So we can start to layer that information as well. But let's keep things relatively simple. If you hit the X here, that's going to remove it from the pivot table. And I will do the same thing here as well. It's not minimized. It's actually going to remove it. Next up, let's choose what we want to be displayed here amongst our columns. So I'm gonna hit that Add button again and we get the same choices here except for our representatives because they're already listed here. And what I'm gonna choose in this case is I'm gonna choose the actual items, the actual products that my Reps are selling. I'm gonna select that. And here you can see, we've got our five different products. You know, again, going back to our original sheet, it can be hard to tell even how many different items we're selling or how many different Reps I have. But of course the pivot table summarizes all of that data right here for us. So I've got my products here across the right. I've got my representatives here across the left hand side. Now, next up, we want to go to our Values. What do we want to display in the intersection of our items and our representatives? So I'm gonna hit that Add button once again. And we've got a variety of different choices here. Now, in my case, I am gonna choose units. I wanna see who, is selling how many units. So I'm gonna select this units option. And immediately we've got all of this great data available to us. So for example, in a snapshot, I can see that all of my Reps have sold at least some binders, right? Everyone is selling binders. But then in the very next column I can see that only three of them have ever sold a desk within this particular timeframe. So, immediately I can start to break down complicated pieces of information, or complicated data set, and find out trends and find out information that I'm after here. Once again, we've got a few different choices here in terms of how we want to summarize this data. If I choose this option here I could summarize it by a few different ways including averages and counts and maximums and minimums. I'm gonna leave that one as some right here, but if I go over here I can also show it as a percentage, if I want to. So for example, maybe I want to show a percentage of the row. So in this case, you can see that a Andrews for example, most of his sales have been coming from pencils, right? 84% of his sales have been coming from pencils. And I can quickly see that although Smith was one of the few people, to sell a desk that only makes up, 1.28% of all of the units that she has sold in this timeframe as well. So again, a lot of flexibility in terms of how you can display that data. And because we've left those grand totals marks checked we have the grand total here both by the Reps on the right hand side but we also see it by the products as well. So I can quickly see that Jones seems to be my top sales person here. Selling nearly 400 items in total. He's also pretty even across, the things that he's selling here. Thompson, maybe Thompson is relatively new to the team, selling it quite a bit less than everyone else but also only selling two different products. Now, the last thing that we wanna look at here, when we're designing our pivot table is filters. So if we select Add again, we can filter things by another unit or another way of looking at things. So in this case, I'm gonna select Unit Cost. I wanna be able to filter out maybe some of my cheaper or my more expensive items. So I'm gonna select Unit Cost. Nothing's gonna happen immediately here because I need to decide how I want to filter out that information. So it's saying right now it's showing all items. I'm gonna select that. And here you can see it's got a list of all of the prices, all of my Unit Costs here. I'm just gonna say Clear cause I don't wanna display all of them right now. And maybe I just want to display everything that is $5 or less. So I'm just gonna select these first four here and I'm gonna select OK. And now you can see that my pivot table has shrunk a little bit. Because desks are my high end items. So it's not even displayed here. It's much more expensive than $5, but I can see in that $5 in less range. Okay now things are a little bit different in terms of grand totals and who's selling more and who's not selling anything at all in certain areas here. If I come back to the pivot table I'm just gonna hit Clear one more time. And maybe I just wanna take a look at my high priced items. So I'm gonna select those top two. I'm gonna select OK. Once again, my pivot table changes because my desk is the high priced item. I've only got three Reps who have sold a desk and here are their numbers as well. If I wanna kill that filter, I could either come here and just say Select all. So that's gonna include everything or I can hit this X and come back. Remember a pivot table is dynamic. You can keep coming in here and changing the values that you want displayed and the filters that you want to add as a part of your data. Now, if you wanna see your sales data clearly and feel organized every day, you should check out Pipedrive. Pipedrive is the easy to use sales tool, that you don't need an IT degree to work with. You can visually track your leads pipelines, and communication in one place, and never forget what to follow up about. Pipedrive automates your day. So you can spend more time focusing on your customers. You can try Pipedrive free for 30 days, and then get 25% off your first three months. Click the link in the description to take advantage of this special offer.