Elements of a pivot table

Previously, you learned that a pivot table is a tool used to sort, reorganize, group, count, total, or average data in spreadsheets. In this reading, you will learn more about the parts of a pivot table and how data analysts use them to summarize data and answer questions about their data.

Pivot tables make it possible to view data in multiple ways in order to identify insights and trends. They can help you quickly make sense of larger data sets by comparing metrics, performing calculations, and generating reports. They’re also useful for answering specific questions about your data.

A pivot table has four basic parts: rows, columns, values, and filters.

The rows of a pivot table organize and group data you select horizontally. For example, in the Working with pivot tables video, the Release Date values were used to create rows that grouped the data by year.

The columns organize and display values from your data vertically. Similar to rows, columns can be pulled directly from the data set or created using values. Values are used to calculate and count data. This is where you input the variables you want to measure. This is also how you create calculated fields in your pivot table. As a refresher, a calculated field is a new field within a pivot table that carries out certain calculations based on the values of other fields

In the previous movie data example, the Values editor created columns for the pivot table, including the SUM of Box Office Revenue, the AVERAGE of Box Office Revenue, and the COUNT of Box Office Revenue columns.

Finally, the filters section of a pivot table enables you to apply filters based on specific criteria — just like filters in regular spreadsheets! For example, a filter was added to the movie data pivot table so that it only included movies that generated less than $10 million in revenue.

Being able to use all four parts of the pivot table editor will allow you to compare different metrics from your data and execute calculations, which will help you gain valuable insights.

Using pivot tables for analysis

Pivot tables can be a useful tool for answering specific questions about a dataset so you can quickly share answers with stakeholders. For example, a data analyst working at a department store was asked to determine the total sales for each department and the number of products they each sold. They were also interested in knowing exactly which department generated the most revenue.

Instead of making changes to the original spreadsheet data, they used a pivot table to answer these questions and easily compare the sales revenue and number of products sold by each department.

They used the department as the rows for this pivot table to group and organize the rest of the sales data. Then, they input two Values as columns: the SUM of sales and a count of the products sold. They also sorted the data by the SUM of sales column in order to determine which department generated the most revenue.

Now they know that the Toys department generated the most revenue!

Pivot tables are an effective tool for data analysts working with spreadsheets because they highlight key insights from the spreadsheet data without having to make changes to the spreadsheet. Coming up, you will create your own pivot table to analyze data and identify trends that will be highly valuable to stakeholders.

Using pivot tables in analysis

In this reading, you will learn how to create and use pivot tables for data analysis. You will also get some resources about pivot tables that you can save for your own reference when you start creating pivot tables yourself. Pivot tables are a spreadsheet tool that let you view data in multiple ways to find insights and trends.

Pivot tables allow you to make sense of large data sets by giving you tools to easily compare metrics, quickly perform calculations, and generate readable reports. You can create a pivot table to help you answer specific questions about your data. For example, if you were analyzing sales data, you could use pivot tables to answer questions like, “Which month had the most sales?” and “What products generated the most revenue this year?” When you need answers to questions about your data, pivot tables can help you cut through the clutter and focus on only the data you need.

Create your pivot table

Before you can analyze data with pivot tables, you will need to create a pivot table with your data. The following includes the steps for creating a pivot table in Google Sheets, but most spreadsheet programs will have similar tools.

First, you will open the Insert menu from the toolbar; there will be an option for Pivot table.

Generally, you will want to create a new sheet for your pivot table to keep your raw data and your analysis separate. You can also store all of your calculations in one place for easy reference. Once you have created your pivot table, there will be a pivot table editor that you can access to the right of your data.

This is where you will be able to customize your pivot table, including what variables you want to include for your analysis.

Using your pivot table for analysis

You can perform a wide range of analysis tasks with your pivot tables to quickly draw meaningful insights from your data, including performing calculations, sorting, and filtering your data. Below is a list of online resources that will help you learn about performing basic calculations in pivot tables as well as resources for learning about sorting and filtering data in your pivot tables.

Perform calculations

Microsoft Excel	Google Sheets
Calculate values in a pivot table: Microsoft Support’s introduction to calculations in Excel pivot tables. This is a useful starting point if you are learning how to perform calculations with pivot tables specifically in Excel.	Create and use pivot tables: This guide is focused on using pivot tables in Google Sheets and it provides instructions for creating calculated fields. This is a quick how-to guide you can save and reference as a quick reminder on how to add calculated fields.
Pivot table calculated field example: This resource includes a detailed example of a pivot table being used for calculations. This step-by-step process demonstrates how calculated fields work, and provides you with some idea of how they can be used for analysis.	All about calculated filed in pivot tables: This is a comprehensive guide to calculated fields for Google Sheets. If you are working with Sheets and are interested in learning more about pivot tables, this is a great resource.
Pivot table calculated fields: step-by-step tutorial: This tutorial for creating your own calculated fields in pivot tables is a really useful resource to save and bookmark for when you start to apply calculated fields to your own spreadsheets.	Pivot tables in Google Sheets: This beginner’s guide covers the basics of pivot tables and calculated fields in Google Sheets and uses examples and how-to videos to help demonstrate these concepts.

Sort your data

Microsoft Excel	Google Sheets
Sort data in a pivot table or PivotChart: This is a Microsoft Support how-to guide to sorting data in pivot tables. This is a useful reference if you are working with Excel and are interested in checking out how filtering will appear in Excel specifically.	Customize a pivot table: This guide from Google Support focuses on sorting pivot tables in Google Sheets. This is a useful, quick reference if you are working on sorting data in Sheets and need a step-by-step guide.
Pivot tables - Sorting data: This tutorial for sorting data in pivot tables includes an example with real data that demonstrates how sorting in Excel pivot tables works. This example is a great way to experience the entire process from start to finish.	How to sort pivot table columns: This detailed guide uses real data to demonstrate how the sorting process for Google Sheet pivot tables will work. This is a great resource if you need a slightly more detailed guide with screenshots of the actual Sheets environment.
How to sort a pivot table by value: This source uses an example to explain sorting by value in pivot tables. It includes a video, which is a useful guide if you need a demonstration of the process.	Pivot table ascending and descending order: This 1-minute beginner’s guide is a great way to brush up on sorting in pivot tables if you are interested in a quick refresher.

Filter your data

Microsoft Excel	Google Sheets
Filter data in a pivot table: This resource from the Microsoft Support page provides an explanation of filtering data in pivot tables in Excel. If you are working in Excel spreadsheets, this is a great resource to have bookmarked for quick reference.	Customize a pivot table: This is the Google Support page on filtering pivot table data. This is a useful resource if you are working with pivot tables in Google Sheets and need a quick resource to review the process.
How to filter Excel pivot table data: This how-to guide for filtering data in pivot tables demonstrates the filtering process in an Excel spreadsheet with data and includes tips and reminders for when you start using these tools on your own.	Filter multiple values in pivot table: This guide includes details about how to filter for multiple values in Google Sheet pivot tables. This resource expands some of the functionality that you have already learned and sets you up to create more complex filters in Google Sheets.

Format your data

Microsoft Excel	Google Sheets
Design the layout and format of a PivotTable: This Microsoft Support article describes how to change the format of the PivotTable by applying a predefined style, banded rows, and conditional formatting.	Create and edit pivot tables: This Help Center article provides information about how to edit a pivot table to change its style, and group data.

Pivot tables are a powerful tool that you can use to quickly perform calculations and gain meaningful insights into your data directly from the spreadsheet file you are working in! By using pivot table tools to calculate, sort, and filter your data, you can immediately make high-level observations about your data that you can share with stakeholders in reports.

But, like most tools we have covered in this course, the best way to learn is to practice. This was just a small taste of what you can do with pivot tables, but the more you work with pivot tables, the more you will discover.

Hands-On Activity: Explore movie data with pivot tables

Question 1

Activity overview

In the previous video, you were introduced to pivot tables as a tool for quickly comparing metrics, performing calculations, and generating readable reports. In this activity, you will create and work with pivot tables using the movie spreadsheet from the video to draw new insights into this dataset and create visualizations to share with stakeholders.

By the time you complete this activity, you will be able to apply pivot tables in your own analysis projects. This will enable you to draw insights and create reports directly from your spreadsheets, which is important for your career as a data analyst.

What you will need

To get started, first access the movie spreadsheet from the previous video.

Click the link to the movie spreadsheet to create a copy. If you don’t have a Google account, you may download the data directly from the attachments below.

Link to movie data: movie data starter project

OR

Download data: Movie Data Started Project

Create your pivot table

Once you have the movie data spreadsheet, you can create a pivot table to run calculations and generate reports.

Open the spreadsheet. Use the Insert menu to create a pivot table.

Insert your pivot table into a new sheet. Click Create. The data range should already be filled in as ‘Movie Data’!A1:N509.

Rename your new sheet Summary.

This will open the Pivot table editor, where you will be able to edit your pivot table and add custom calculations.

Using pivot tables to answer questions

Now that you have created your pivot table, you can use it to answer specific questions about your data quickly and easily. For example:

• What is the average budget for each genre?
• What is the average revenue for each genre?
• Which genre is generally the most profitable?

Pivot tables are a useful way to draw these kinds of insights directly from your spreadsheet data.

1. To get the average budget and revenue for each movie genre, first you will use the Add button next to the Rows section of the Pivot table editor and select Genre (1) from the dropdown list.

2. Next, you will use the Values section to add the average budget and average box office revenue. Click the Add button next to Values and select Budget.

The pivot table will summarize these values from the original data by SUM automatically. Change it to AVERAGE using the dropdown menu.

Now you should have a column in your pivot table titled AVERAGE of Budget ($). Repeat these steps to create another column for AVERAGE of Box Office Revenue ($). Your pivot table should now appear like this:

Now you can easily find the average Budget ($) and Box Office Revenue ($) for each genre.

3. In order to find the average net profit for each genre, you will need to create a calculated field. Use the Add button in the Values section and select Calculated field from the dropdown list.

Input Custom under the Summarize By option and paste this formula to get the average profit:

=AVERAGE('Box Office Revenue ($)')-AVERAGE('Budget ($)')

Mark Summarize by Custom to avoid creating an error. Title the new column you created with the calculated field AVERAGE Profit in cell D1. Your pivot table should now appear like this:

4. Finally, you can use the Sort by option in the Rows section of the pivot table to sort and organize your pivot table. For example, try sorting by the AVERAGE Profit values to see which genre generates the most profit on average.

Visualizing your data

You can create some basic visualizations based on your custom tables to share your findings with stakeholders.

Select any cell in your pivot table and then navigate to the Insert menu. Select Insert Chart.

This will create a chart in the same worksheet as your pivot table. Move it next to your pivot table. In the Chart editor, select Bar chart. This type of chart makes it easy for your stakeholders to compare the different genres.

Input A1:D18 for the Data range to select the meaningful values from your pivot table. Set the Y-axis as Genre(1). Add AVERAGE Profit to the Series list and check Use row 1 as headers and Use column A as labels.

Finally, change the title of your visualization so that your stakeholders know exactly what you’re communicating. You can also customize the color palette using the Customize menu in the Chart editor pane.

Confirmation and reflection

Which movie genre generates the most profit on average?

A. Thriller

B. Adventure

C. Comedy

D. Fantasy

The correct answer is B. Adventure. Explain: To find out which movie genre generates the most profit on average, you created a pivot table with a calculated field and sorted the data accordingly. Going forward, you will be able to use pivot tables to quickly summarize data to draw insights and even create visualizations directly in your spreadsheet. This will help you in the future as you continue to work with spreadsheets as a data analyst.

Question 2

In the text box below, write 2-3 sentences (40-60 words) in response to each of the following questions:

• How can using pivot tables directly in your spreadsheet help you analyze data in the future?
• What are some of the benefits of being able to summarize data directly in your spreadsheet?

Explain: Congratulations on completing this hands-on activity! In this activity you created a pivot table and some basic visualizations directly in your spreadsheet to gain insight into your data. A good response would include that this will allow you to analyze data quickly using one analysis tool.

This can help you quickly find answers for stakeholders and even generate shareable reports. For instance, you were able to answer specific questions about the data and share your findings. In upcoming activities, you will continue to analyze and share data using spreadsheets.