Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo

Learning Objectives

By the end of this section, you will be able to:

  • Define a PivotTable
  • Organize and clean your data prior to designing a PivotTable
  • Design/insert a PivotTable
  • Understand and use the recommended PivotTable tool
  • Add a PivotChart to pictorially present the data

PivotTables help data analysts use raw data and organize it into an interactive tool for summarizing and analyzing the data points. WorldCorp can, for example, gather their manufacturing datasets of each machine’s daily performance and put this information into a table that the PivotTable tool can use to analyze the information. This type of organization would be useful for producing various metrics (such as machine performance) and company Key Performance Indicators (KPIs). The PivotTable tool can also divide data into a set of criteria, which will organize your data by variables you choose. This separation and division of data help you create business intelligence with ease.

Definition and Uses

Excel has various ways to store data and various tools that aid in data intelligence queries. You’ve learned the purpose of using What-If Data Tables, naming ranges, using comparison operators and writing statistical and logical functions—all of which can be combined with arrays (or other tools) to make interactive and automated tables. Excel has an even quicker way to make relational tables and automated business intelligence: PivotTables.

A PivotTable lets users create interactive charts and tables from raw datasets. They can process large, raw datasets and allow the user to choose how the data will be tabulated and summarized using easy drag-and-drop features. PivotTables also help summarize the data for a professional appearance and to show it in a more user-friendly way. Out of thousands of rows and columns of raw data, you can produce a simple, easy-to-read table with different options for displaying your metrics. Some applications of PivotTables might include producing metrics for website views and ad clicks, displaying demographic information for a county or city, or gathering information about small business revenue. In each of these cases, PivotTables can be used to process large, ever-growing datasets into readable tables.

Organizing and Cleaning Your Data

Let’s return to the example of shipped products from WorldCorp’s Portland, Maine, location. PivotTables can be used to aggregate values and create an actionable market analysis and business intelligence for the company.

The first step of creating this PivotTable is to organize and summarize the data. Having a digestible raw data sheet that Excel can read well enough to aggregate the data is critical. To clean the data, you should ensure that:

  • There are no merged cells anywhere in the data.
  • All filters are inactive.
  • There are no SUM functions on the bottom or sides of the worksheets.
  • All erroneous, duplicate, and/or blank values are deleted from the data.
  • All values in each column are of the same value (e.g., currency, date, text).

Next, convert your data into a top-down table, meaning that all of the data range headers should run along the top row of the sheet. This is the typical format for tables and will make using PivotTables much easier. In general, horizontal tables can be hard to follow. Cross-tabular source data (with headers on top/bottom and left/right) are even more difficult to read for the PivotTable generator. Designate a unique name for each top header; do not repeat any column header names. Figure 10.33 shows a correctly formatted worksheet. You can copy this worksheet into Excel, or you can use the data in the "pivot_table_data" tab of the downloadable Chapter 10 data file workbook.

A large spreadsheet with columns and rows of data, both text and numerical. All of the unique data range headers run along the top row of the sheet.
Figure 10.33 This small representation of data is a sample of what could be hundreds, thousands, or millions of individual shipments a month. (Used with permission from Microsoft)

Inserting a PivotTable and Rearranging Variables

To create a PivotTable, go to the Insert tab, choose the PivotTable icon located on the far left, select the entire dataset, and output to New Worksheet. (Alternatively, you may name your data ranges, as instructed in Defining, Selecting, and Naming a Cell Range, and simply select the named ranges for your data.) The Field List will activate and ask which columns to display, as shown in Figure 10.34. Select the headers (variables) in the order that you want the PivotTable to display them. In the WorldCorp example, to see the quantity each client is buying, select Destination, then Quantity, and then FOB $. As shown in Figure 10.35, the PivotTable neatly displays the data, with interactive features allowing the user to select different pieces of information as variables.

PivotTable Field sidebar displays options for Choose fields to add to report. Cell A3 is highlighted and a PivotTable pane reads: To build a report, choose fields from the PivotTable Field List.
Figure 10.34 The PivotTable wizard can help to create clean, interactive tables from raw data. (Used with permission from Microsoft)
Drop-down arrow located at right of first cell in Row 3 titled “Row Labels.” To the right in dialog box, the Quantity, FOB $, and Destination boxes are checked in dialog box.
Figure 10.35 The PivotTable displays the data. (Used with permission from Microsoft)

Now, to further delimit the details of the clients, add Date to the variables (Figure 10.36a). Because you chose Destination first, you are separating all the variables and ordering them by client to know what the client is consuming in volume and in dollar amount. Adding the date allows you to see total sales but also breaks down the data by month.

This is only one way to create a PivotTable with this data. If you were to change the order in which you selected the headers in the PivotTable wizard, the whole table would be different. Additionally, you can choose a different set of data to display, which changes how the information is grouped. To demonstrate this, uncheck Date (you have to uncheck Months, too) and instead check Product. As seen in Figure 10.36b, the table gives a totally different perspective. Instead of seeing the data grouped by Date, the grouping has changed to show the products the client ordered and the quantity of each.

(a) PivotTable displays information by location, then months. (b) PivotTable displays information by location, then product.
Figure 10.36 Selecting headers in a different order will change where and how they appear in a PivotTable. (a) The original table broke the data down by date, and (b) the new table breaks down the data by product. (Used with permission from Microsoft)

You can easily change the arrangement of the PivotTable using the fields in the lower half of the Field List. You can drag and drop the variables from one field to another to change how they appear in the PivotTable. For example, you can change the arrangement of the rows by putting Product first. This automatically changes the PivotTable completely, from being destination oriented (as seen in Figure 10.37a) to being product oriented (Figure 10.37b).

(a) Drag fields between areas below: pane displays options: Filters, Columns (Values), Rows (Destination/Product), Value (Sum of Quant…/Sum of FOB$). (b) Product, Quantity, FOB$, Destination selected for Fields to add to report.
Figure 10.37 Changing the headers can change the entire table. (a) You can rearrange the data so that product comes first. (b) The PivotTable looks completely different from the previous destination-oriented table. (Used with permission from Microsoft)

Let’s apply a filter to the results from Figure 10.37b. To filter by date, first check the Date and Month variables on the top half of the Field List. Then, drag and drop the dates from the Rows to the Filter field, as shown in Figure 10.38a. This will allow you to apply a filter to the dates and be able to select only certain dates. To see just the February sales, go to the Month drop-down menu on the PivotTable and select Feb. Now, only the sales from that month will appear (Figure 10.38b).

(a) Months is selected in Filters in PivotTable Fields sidebar. (b) PivotTable displays information for the month of February in table.
Figure 10.38 Filters are useful for when you want to see only a subset of your data. (a) You can choose a single month in the filters. (b) The table then shows you only that month’s data. (Used with permission from Microsoft)

Using the Recommended PivotTable Tool

If you are unsure how to organize a PivotTable for your data, Excel can recommend one depending on how the data is organized. Let’s see what using a recommended PivotTable might look like.

Go to the Insert tab and select the Recommended PivotTables icon. A dialog box appears with a handful of recommended charts. Scroll and select the one you like best. These recommended tables may be helpful to get you started, but they only compare one variable at a time. However, if you choose one, the new PivotTable will appear in another worksheet, and you can customize it there, adding or reorganizing variables until you have a PivotTable that presents your data in a way that makes sense.

Adding a PivotChart to Visually Present Data

Excel has a feature that allows you to create a PivotChart to visually present the data in your PivotTable. The first step is to select the entire PivotTable, which causes the ribbon tab “PivotTable Analyze” to appear. Next, select the PivotChart icon. You will use a small dataset from Figure 10.31 to create a chart of the information. More in-depth coverage of PivotTables is in PivotTables/Charts. Figure 10.39 shows the original data and Figure 10.40 shows the data summarized in a PivotTable by sales agent. To create a chart of the data summarized in the PivotTable, choose PivotChart from the PivotTable Analyze tab.

Cell A1 is selected in a spreadsheet with Date in the formula bar.
Figure 10.39 The data is summarized in a simple table. (Used with permission from Microsoft)
PivotChart is selected from Tools command group of PivotTable Analyze tab. Agent and FOB$ options selected to add to report in PivotTable Fields sidebar. Table displays information for agents and corresponding FOB$.
Figure 10.40 Placing the data in a PivotTable enables further analysis and filtering of the data to meet specific needs. (Used with permission from Microsoft)

For the data in Figure 10.40, a bar chart, pie chart, or clustered column chart would be optimal. These chart types are appropriate because they allow you to see the difference in scale between the sales levels of the agents. For this example, choose the column chart. With the column chart, the higher the column, the greater the sales totals for that agent. You can further customize the column chart by adding percentages or labels. The Agent drop-down menu in Figure 10.41 allows you to further filter or change the column chart.

A bar chart is inserted into the spreadsheet reflecting information in PivotTable at left. A drop-down rectangle in the lower left labeled Agent with a drop-down arrow at the right is visible.
Figure 10.41 You can further customize the PivotChart by adding features such as value labels or by sorting the information in a different order. (Used with permission from Microsoft)

Real-World Application

Using PivotTables to Track Spending

PivotTables can be a powerful tool for businesses in budgeting and creating other financial reports and analyses. You can also use PivotTables for your own personal budgeting. There are many budgeting services available online or through apps, but you can create your own powerful budgets that can help track all personal finance matters, including spending, without paying for an online service or app. As an individual, you might be able to track your budget fairly easily; however, as your financial matters become more complex, or as your family grows, Excel can come in handy to assist you in managing your budgets without the additional expense of a service or application.

To get started, you can download a CSV file of your transactions from your financial accounts. Most institutions will have this function where you review your account and transaction information online. You will want to collect all information, particularly from your spending accounts, such as credit cards. Once these are gathered, be sure to validate the information, then create a column heading named Category. You will then create your categories, such as Food or Entertainment. Some credit cards will auto populate these categories when you download your transactions, but you will want to verify these are accurate. From there, you will complete your PivotTables using the instructions you can find in this textbook. You can opt to create different timelines of your budget, such as daily, monthly, or yearly, depending on your own budget analysis needs.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Apr 15, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.