If you're using Google Sheets and you have a long list of data, a very
easy way to extract information out of it is to use pivot table. I already made
one on this worksheet and this is where we're going to end up. You have the Sales
Rep on the left, you have the ship mode up top, and then you have it by year. I've
already filtered this year; we're gonna do that at the end. You can see how
it's set up over here. I'm going to delete this. This list here I have I
think is a good example of what makes good data for a pivot table. What
that means is that, one it has headers and the headers obviously pertain to the
data that's underneath it. The Sales Rep describes what's in this column.
There are no breaks in this data so, if I scroll down, there's no blank lines.
It's so much data that you can't just look at it and get the information.
Make sure that you've selected a cell within the table of data that you
want. This spreadsheet that I have only has a table, so that's not hard to do. You
go to the menu, select data, and pivot table. That gives you a blank slate
that you're going to work with. It will suggest different pivot tables. It's going
to use some artificial intelligence to kind of figure out what we want. Unless
you have very simple data, and you just want to have it summarized by one
dimension, these aren't going to guess what you want because there's so many
different combinations. But, if you do just try one of these and click on it,
it's going to build a table for you which can be really helpful if you have simple
needs. To show you how it's constructed, I'm going to take that off.
What we had decided we wanted to do is, we wanted to analyze it
by Sales Rep first and I imagine that going down the left-hand column. This
is where it's a little bit confusing. So, you want it in this column, but
you actually want each salesperson to be in a row, so that's why it says rows here.
You want to add the Sales Rep for rows even though it's going to be
a column. So you want each row we're gonna add, we wanted to be a Sales Rep
and it's built that portion out for me. It's put them in an alphabetical
order. It assumed that we wanted to sort by Sales Rep, right? Now, we can't sort by
anything else, so that's fine. On the columns, you want the Ship Mode, and again,
this is a little bit confusing, right? This is a row. It's going to be your row of
headers, but it's going to be, each column is gonna have the data in it so it's
called columns. Let's add the Ship Mode. You'll notice, on each of these,
it's asking if you want to show the totals or not. There will be a Grand Total for
the Sales Rep and a Grand Total for the Ship Mode. Now, in here, let's look at the
count of Sales. I don't want to see the dollar amount, I want to see the
number of sales. So, for values, values is going to be what it shows you in the
middle of your pivot table. It's the actual meat of the table. You have
your headers in here, now you need to put something in the middle. Let's go to
values and add our sales column. It's what had the dollar amounts in it. Let's
add the sales. It's summing them right now. I want to add each one of them
as one. We're just gonna do COUNT. We're most of the way there.
We have Sales Rep, and we have a count by each Ship Mode, and the last thing we
wanted to do is we wanted to look at it by year. I'm envisioning those being
on the left-hand side here under each Sales Rep. You remember this is called
rows, even though it's a column. Let's go over to the rows. Let's add another
column of rows, if you will, and we're gonna make it the date. This isn't
what you want, right? We want it by year but, in order to do that, you had to add
the dates. It's a value in here. What you can do is right click on
any of the dates, and create a pivot data group, and select year. This is going to
summarize it by year because Google Sheets recognizes that those are dates.
It can extract the Year and summarize by just that. Let's say
you're just looking for 2018. Let's go back to the right, scroll down to the
filters. We're going to add a filter for the date.
This will be a little bit tricky how we're gonna do this. Let's do a drop
down it said showing all items. We're going to select clear and then we're
going to type in 2018. We're going to hit select all. What we're doing here is,
we're saying unselect everything, show no dates. Then, if you type in 2018, it'll
show only the 2018 dates that are in the original table. Now, if you click select all,
it's going to select all that it's showing for 2018. Click OK.
You have this filtered by 2018. One last thing. Let's say Phil left the company so
you want to take him out. Just add another filter. Go to Sales Rep, right
now it says showing all items. We're going to come here and uncheck poor Phil
because Phil is no longer with the company. Click OK, and there you are.
A really easy to understand pivot table with just the data that you need and, if
you want to change anything, this is always live. I could just go back here
and customize it however you want. So that's all. Taking a good solid list of
data that has columns with consistent data types in it, no blanks, and we've
created this pivot table that gave you the exact information that you wanted to
see. I hope that was helpful. If you liked that video and you want to see
others like it, please subscribe to my channel in the lower right-hand corner.
Thanks!