A pivot table is a game changer when it comes to making sense of and analyzing large data sets in Google Sheets. Thanks to its ability to reorganize, analyze, and summarize large data sets in a meaningful way, it’s no surprise that many data analysts and professionals rely on this powerful tool for their projects.
Today’s guide will give you a step-by-step tutorial on how to create pivot tables in Google Sheets. But before we get into all the exciting details, let’s look closely at what a pivot table is and what it is intended for.
What is a pivot table?
If you’ve ever wanted to get the most out of your data and take your analysis to the next level, you might want to look into the power of pivot tables. But what exactly is a pivot table?
A pivot table is a powerful tool in Google Sheets that allows you to seamlessly reorganize extensive data sets in a way that appears meaningful and easy to comprehend. With pivot tables, you can group and reorder data, which makes it easy to look at from different angles.
The exciting part is that you won’t have to go through the hassles of filtering and sorting data manually, as the pivot table does all the hard work.
From creating a summary of your data to finding patterns and relationships that aren’t obvious in raw data, pivot tables help you make sense of large amounts of data.
If your job involves dealing with large data sets and you’re looking for a powerful tool in Google Sheets that makes your job easy, you won’t be disappointed when you explore pivot tables.
Pivot tables: What can they be used for?
Imagine that your job is to find relationships, trends, and patterns in large data sets or to do in-depth and comparative analyses of these sets. In that case, we are sure you’ll easily relate to the following instances where pivot tables in Google Sheets come in handy.
- Seamless interpretation: By aggregating and summarizing a large data set, pivot tables allow users to focus on crucial information. It also makes it easy for users to understand better the data they are dealing with.
- Hassle-free trend spotting: By sorting and rearranging a large amount of data, pivot tables make it easy for users to spot patterns and trends.
- Reliable decision-making: Pivot tables make it possible to rearrange rows and columns, making it easy for users to compare data in a meaningful and straightforward way. With this in place, users can make an informed decision as they now have a holistic view of their data.
Having shown you some of the most remarkable ways to use pivot tables in Google Sheets, it’s time to address the elephant in the room- how to create pivot tables in Google sheets.
How to use pivot tables in Google Sheets
Google Sheets has a pivot table tool that is powerful and easy to use. Here is a quick rundown on how to use it, followed by a more detailed step-by-step tutorial.
- Open a Google Sheets spreadsheet housing all the data you want to analyze and select all the cells containing data.
- Click on the Insert tab and then choose Pivot Table.
- You should get a prompt asking whether you’d like to create a new sheet or utilize the existing one. Choose whatever works best for you. For our tutorial, we will choose the create a new sheet option.
- Google being Google, will typically provide a suggested pivot table analysis. Check to see if that gets the job done.
- If not, you can create a customized pivot table by clicking on the “Add” option next to Rows and Columns and choosing the data you want to analyze.
- Click on the “Add” option opposite Values to choose the values you want to display within the rows and columns.
- Finally, click on Filters to display values that meet specific criteria.
We know this sounds a bit ambiguous and confusing, so we have put a detailed tutorial to guide you through every step. Let’s get started, shall we?
Creating a Pivot Table: A step-by-step tutorial
Assuming you already have a spreadsheet filled with raw data, you first want to create a pivot table, which is pretty straightforward.
- Start by selecting the cells containing all the relevant data. You can do this using the command or Ctrl + A shortcut on your keyboard. After executing the shortcut, you can navigate to the “Insert tab” and choose “Pivot table,” as illustrated below.
Note: If your data set doesn’t have a title for columns (i.e., the topmost row), we suggest you name these columns. From the image above, you can see our topmost row has clear names, all marked in green. Please do the same for yours.
- After selecting the Pivot table option, you’ll get a pop-up on whether you’d like to create a new sheet or use the existing one. For our tutorial, we chose the “create a new sheet option.” This action will create a new spreadsheet called “Pivot Table,” which we will work with.
- If you opted for the “Create new sheet option,” here is what you should get:
Understanding the pivot table editor
Now that your pivot table has been generated, you are set to kick off your analysis. To begin, you’ll need to use the pivot table editor, which is designed to let you build different views for your data. The pivot table editor is typically located on the extreme right-hand side of your spreadsheet.
The editor provides two ways to analyze data. There is the Google “Suggested” option or the manual option. For a complete experience with pivot tables, we will talk about both options. Let’s start off with Google’s suggested pivot tables.
Suggested pivot table
Since Google is all-knowing, it can predict exactly what you want before you even get into it. So under “Suggested Editor,” Google provides you with different analyses per your data set.
For example, Google gave us the following analysis as per the data we provided:
- The average hours spent on each project type
- Clients name for each project type
- And how much is billed for each project type.
Google Sheets will automatically make a pivot table with analysis when you click on any of the suggestions. Let’s say; for instance, you decided to opt for the second option (Count of Client Name for each Project Type); you’ll see the project types in Column A and the number of client names in Column B. Here is what it should look like:
If Google’s suggested analysis isn’t what you’re exactly looking for, or you want something different, you’re welcome to explore the manual option offered by Google Sheets. Thanks to the manual option, you can do more.
The manual output typically provides you with four options you’ll find on the extreme right of your spreadsheet. These options let you add different data to your pivot table. Here, check them out:
These four options let you add more depth to your data analysis. And to make your job easy, we will give you a detailed walk-through to show you how to maximize these options. But before you do, you want to first remove the existing Google Suggested analysis we generated earlier. You can do this by simply clicking the X for the Rows and Values options. Here is what we mean.
If you followed our instructions correctly, you should now have a clean pivot table, just as it looked when we started off. Our objective with this pivot table is to analyze:
For each client featured in our raw data, across different project types, what exactly did we bill in 2017?
Since that’s the case, we will be looking very closely at four things
- For each client
- Across different project types
- The entire amount billed
- In 2017
From what we have highlighted, you’ll agree that each of those four pieces corresponds to one of our elements: Rows, Columns, Values, and Filters.
- Rows and columns are an important component in Google Sheet pivot tables. Using rows and columns, we can seamlessly create a two-dimensional data set. We can then use the details generated to calculate a third-dimension value. For our example, the base data would be Client Name in the Row section and Project Type in the column section.
- As per the value, what would like to have in the cells where Client Name and Project type cross is the Total Amount Billed
- We are sure by now you’ll be wondering where we will show the data from only 2017? Well, that’s where the Filters option comes in. Using the Filters option is a brilliant way to only analyze a specific subset of data.
With all this figured out, you can simply click “Add” for any of the four options, and Google Sheets will generate a dropdown with the column names curled out of the raw data sheet you started with. By clicking any column names, Google Sheets will add the specific data in your chosen format.
Creating the report
We are sure you want to get straight into the actual part. So let’s cut to the chase and get into building this thing. Keep in mind that our objective with this pivot table is to figure out how much we billed in 2017 for each client across the various project types. For easy comprehension, we will break everything down into very simple steps.
#Step 1: Adding the rows
First thing first, we need to tweak our table so it features both the list of clients as well as the different project types. To do this, you need to click on “Add” next to rows. After that, choose the Client Name option. This action will pull the data together.
With the selection done and applied, you should see a list with your clients’ names in the pivot table. If you did everything correctly, it should look something like this:
A look at the image above shows that the action we executed took a part of the raw data we earlier had, deleted all duplicates, and left us with data that is easy to digest. A close look at Column A shows a list of our clients in alphabetical order.
That said, what we have been able to achieve so far is adding an existing column to our pivot table. So we need to take this further by adding more data, especially since our objective is to get a comprehensive insight from the report.
#Step 2: Adding columns
After successfully creating a column for “Clients Name,” the next thing we need to do is add the “Project Type” columns. And yes, this is just as straightforward as the first step. As you did for the step, click on the “Add” option next to Columns, after which you can select “Project Type.” Here is how it should look.
#Step 3: Add values
Having created our rows and columns, the next important thing we need to do is bring in calculated values for the respective cells in the pivot table. This is important since we want to know the total amount billed. To do this, head straight to the pivot table editor and click on the “Add” option next to Values. With that done, all that’s left is to select the Amount Billed.
To ensure that you get the total amount billed (against the average amount billed), you’ll need to choose Summarize by, and select SUM. Sounds confusing? Check the image below for clarity.
Here is what your pivot table should look like after selecting “Summarize by” SUM.
Now, we have a piece of amazing information we can use for our analysis. A close look at the image above shows the total amount charged for each project type we completed for different clients.
Another thing you’ll notice is the grand total, which Google Sheets added and calculated automatically. This allows us to see clearly the total amount we billed each client. Not just that, we also have the total amount we billed for specific project types across all our clientele.
#Step 4: Adding filters
From what we have covered so far, you can clearly see how insane a pivot table is. However, what we have created thus far hasn’t answered our question, as we haven’t filtered the data in our pivot table to only show values for 2017. So we will be doing that now.
First, you must click the “Add” option next to Filters on the pivot table editor. Here you’ll need to select the year in view. And since we are only interested in values from 2017, you’ll need to define that. By default, you should see both 2017 and 2018 (the two years in the raw data we started with) checked. Now, uncheck 2018 and proceed to click OK. This action will update the presenting you with only data from 2017.
After applying the Filters and specifically choosing to show values from 2017, here is what your pivot table should look like:
Finally, we have a pivot table that gives a comprehensive answer to our question: For each client we had, across different project types, what was the exact amount we billed in 2017?
Tips: You have the option to filter data from any column in your original data set. This is a brilliant option if you’re looking for specifics.
Understanding the pivot table up close
If you have read to this point, you’ll agree that the pivot table has provided us with everything we need to do a comprehensive analysis.
With what we have in our sample pivot table, we can answer almost any question relating to the data. And because we want you to completely understand what it feels like to use pivot tables in Google Sheets, we will delve a little deeper. Here are two excellent examples we want to consider.
Which of our clients paid us more in 2017?
To find an answer to this question, we need to simplify our report. And guess what? It isn’t complicated at all. All we need are the names of our respective clients in rows, followed by the SUM of the amount billed to each client as values.
To get started, you’ll need to remove the project type from the generated data. To do this, simply navigate to the pivot table editor and click “X” in the Columns section next to Project Type. Here is a visual on how to accomplish that:
Now, visit the pivot table editor; under the ROW option, you should find “Client Name.” Click on the option and Sort by SUM of the amount billed. If you did this correctly, you should see the pivot table reorder itself to showcase data for the information you requested for. To avoid any confusion, here is exactly what we mean:
Having made the changes we requested, our sample pivot table should look something like this:
The data in our pivot table clearly shows that in 2017, Questindustries paid us more as they were billed a total of $1,700.
Which project type did we charge the highest hourly rate?
For this analysis, we intend to shift our focus from considering the total amount billed. Our objective with this analysis is to find the highest average hourly rate charged for the respective project types we executed in 2017.
To get started, head straight to the pivot table editor. Under the Row option, switch the Client Name option with Project Type by clicking the “X” to clear your initial selection. With that out of the way, you can choose “Project Type” as your new Rows value.
So you don’t get confused, we have detailed the step out pictorially, so you know exactly what to do. Here, check it out.
Still, in the pivot table editor, navigate to the “Values” option and proceed to replace Amount Billed with Hourly rate instead. Finally, tweak the Values settings from SUM to average. Why we are doing this because we are interested in knowing the average amount billed, not the sum.
With these changes effected, our pivot table should be updated to answer our question. From the image below, you can clearly see that Copy Editing had the highest average hourly rate in 2017.
To wrap up, pivot tables in Google Sheets are a vital tool for anyone who works with insane amounts of data. If your job entails making sense of large data sets, you should take advantage of the amazing perks offered by pivot tables.
What is especially unique about pivot tables is their ability to seamlessly and quickly aggregate and summarize information, making it easy for users to make informed decisions and analyses based on the information available.
By following the step-by-step tutorial detailed in today’s post, you should be able to create your own pivot tables. For those using pivot tables for the first time, you don’t need to fret as it isn’t as complicated as you imagined. Simply follow our guide to the latter, and you should be fine.
One thing is certain, using pivot tables for your analyses will make your job easy and completely change how you analyze large data sets.
Frequently asked questions
What are the benefits of using a pivot table in Google Sheets?
Google Sheets pivot table is a valuable tool for reorganizing and summarizing large data sets. Thanks to pivot tables, it’s easy to understand and draw insights from large sets of data. From allowing you to group data by specific columns to calculating aggregate and values to creating charts and graphs, there is so much you can do using pivot tables.
If you’re looking to save time and effort, especially when dealing with large data sets, you won’t go wrong in exploring pivot tables.
Is it possible to change the layout of a pivot table?
Google Sheets makes it possible for users to change the layout of a pivot table. All you need to do is drag and drop columns and fields in the “Rows” and “Columns” section located on the extreme right of your spreadsheet. Not just that, you can also leverage the Values section, especially if you intend to specify how you’d want to summarize your data.
Can I use multiple data ranges in a single pivot table?
Unfortunately, you can’t use multiple data ranges in a pivot table. Notwithstanding, Google Sheets allow users to create multiple tables based on different data ranges. You even have the option to place them either on the same sheet or different sheets, depending on what works for you.
How do I format my pivot table?
Formatting pivot tables in Google Sheets is straightforward. While you can leverage the different built-in formatting tools provided by Google Sheets, you can also use the pivot table editor to make changes as you deem fit.
Other Google Sheets Resources You May Find Useful
- How to Create Data Entry Form in Google Sheets
- How to Make a Bell Curve in Google Sheets
- How to Create a Combo Chart in Google Sheets
- How to Create a Timeline Chart in Google Sheets
- How to Create Dynamic Chart Range in Google Sheets
- How to Create an Area Chart in Google Sheets
- Google Sheets Conditional Formatting Custom Formula (with Examples)
- How To Create Drop-Down List In Google Sheets (With Examples)