Pivot Tables are great for quick Analysis WITHOUT using Formulas. You simply need to have your data organized in Columns and can start using the Pivot Table functionality in Google Sheets.
In this video we are going to discover how to use Pivot Tables for Data Analysis in Google Sheets
Sheet with Demo Data: https://docs.google.com/spreadsheets/d/1TeTfO6G0zj9aGSSVgh5nZ13FSXKl2JAkHxNMPQT4pps/copy
In this video, we’re going to talk about how you can use pivot tables within Google Sheets for your data analysis. All and more coming up.
Hey there and welcome back to another video of measureschool.com teaching you the data driven way of digital marketing. My name is Julian and today we want to take a look at pivot tables within Google Sheets for our data analysis.
Now this is a part of our ongoing series on Google Sheets and data analysis. And we already talked about the different mechanisms of analyzing data within Google Sheets. We talked about functions, we talked about sorting, we talked about quick sum functions and so on and so on. But pivot tables is another tool set that let’s us do quick analysis, which we want to discover today. Now we got lots to cover, so let’s dive in.
Organizing Your Data
All right, back in our Google Sheets, we have our data from last time here. In order to do pivot tables, the data needs to be organized in a certain way. And I just found that this data doesn’t really work too well with a pivot table, so I’ve come up with a little easier example here in a new sheet where we have just a few rows here and the data grouped by date, the item SKU that was sold, for example, the category, quantity, price, and revenue. Now your data needs to be organized in columns in that way because for the pivot table, the header is really playing an important part in how to organize it later on.
Configuring and Building a Pivot Table
Now in order to build a pivot table, you just need to click into one of the cells of your data. And then can go up here to data and click on pivot table. This will open up a new sheet here that you can freely rename if you choose so. And you see this new table, consisting of rows and columns. And these are freely configurable via this menu right here.
Now what can we actually do with this? We can first of all, choose our data that we want to build our pivot table on, and in our case, that is in our sales report right here. All that data was automatically pulled right in. We can then add rows and columns and we have our column headers here available. So for example, for the rows you would like to have the date, and in our column, we want to have the different categories that were sold. now we just need to fill this all with data and this is where the values come in. So click on add here and let’s say we want to have the full revenue.
Quick Pivot Table Data Analysis
And already, you can see that it gives us quick analysis here because we have this grand total row at the end where we can see what category has made us the most money in these given dates. So we can see here, from our three categories, the hats seem to be doing really well. Now we also have this grand total column here at the end. That actually let’s us see which day was the most successful.
So here we can go with the 11th where we made most of our revenue. And of course our full revenue for these different days over all the categories. So without using any kind of formulas, we have now been able to see which category performs the best, which day performed the best and the full revenue.
Drilling Down on Data in a Pivot Table
Now the cool thing is that we can actually drill down deeper into the data by adding more rows or columns. So for example, if you wanted to get a closer look at the categories and see what actual SKU numbers, what items were sold, we can click here on add and add the SKU numbers and that will give us these menus here where we can open and close our different categories. So for example, the hats here, we can look at the different SKU numbers and see how much revenue on which given day they have generated.
If you wanted to change this all around, because we actually want to look at this in a more natural way, that actually opens up to the bottom, we can simply switch the rows and columns and that is simply done by dragging and dropping. So we can put the date here. Let’s put our category here and our SKU numbers, as well.
Now we have that here opening up to the bottom and you can see quite nicely, how our pants performed, on which given day again, and which SKU number was most successful. There was this SKU number, on the 11th has sold this many of revenue. But also this is the total revenue of that day. You could also add the quantity here. For example, let’s say in the values, you would like to add quantity. So now we have even more overview, more detail in our data. You can see how many of these SKU’s, so for example, this pants here was sold one time and therefore generated that much revenue.
So I hope you can see how you can expand the data set, drill down even further, depending on the question you might have that comes up through your data analysis already. Now looking at this data, we could even go further into changing the actual calculations here.
Using Summarise by Setting
So this is all summarized data. And we might want to have the average volume here. So we could change this around. So now we are looking at averages. Or as you might see, minimum, maximum, the median, and so on. So really flexible, without using any kind of formulas to do quick calculations.
And the last menu point down here, we can see the filter menu. So if you only wanted to look at one given date, we could also achieve that. Let’s clear this all and just look at this date here. And our table would collapse into numbers that are only derived for this particular date. So really nice, flexible tool to combine and match data and get to your answer quite fast with a pivot table.
Things You Can Not Do in a Pivot Table
The one thing I need to mention is that you can’t actually change any data inside of this pivot table. You would need to go back to your sales report and actually change your underlying data in order to change data in this pivot table. It’s also not possible to actually change around the values here. You can change the category apparently. But once you have a grayed-out field, you need to change the underlying data. Now obviously, you could also build something around this pivot table. But again, if it expands or collapses, then that data might be overwritten. So really use this as a analysis tool rather than a visualization tool within your Google Sheets.
All right, I hope you can see now how pivot tables can really be another tool that you can use for data analysis. Ask a question, find out the answer with a quick analysis in pivot tables.
Now I would encourage you to actually check out pivot tables. Go ahead and take some data and then try out these pivot tables. Put it into rows, into columns and change around the data and see what you can do with the data. Really think through it. It takes a little bit of time to getting used to. And if you need a little bit of data, then I’ve linked up this sheet with the data that we’ve used today in this lesson down below as well. So that’s already it with this week’s lesson.
Now if you like this video and found it useful, then please give us a thumbs up and also share to a friend or a colleague. That would really help us out to grow this channel and make more awesome videos, as well. And if you want to follow along on our journey, then head over to the subscribe button right over there because we’ll bring you new videos just like this one, every week. Now my name is Julian, until next time.