What is a pivot table? How to use in Excel and Sheets

How pivot tables work

When users create a pivot table, there are four main components:

• Columns. When a field is chosen for the column area, only the unique values of the field are listed across the top.
• Rows. When a field is chosen for the row area, it populates as the first column. Similar to the columns, all row labels are unique values and duplicates are removed.
• Values. Each value is kept in a pivot table cell, which displays the summarized information. The most common values are sum, average, minimum and maximum.
• Filters. Filters apply a calculation or restriction to the entire table.

The basic steps when using a pivot table include:

1. Select Data. Choose the data range or table to be analyzed. 
2. Insert PivotTable. Click on the "Insert" tab in Excel (or similar tools) to insert a pivot table. 
3. Arrange Field. Drag and drop fields from the data into the "Rows," "Columns," and "Values" areas of the pivot table. 
4. Personalize. Adjust formatting, calculations and filters to obtain the desired summary view. 
5. Analyze and Present. Explore and analyze the data, identify trends and directions, and build an image (e.g., a pivot chart) based on the pivot table. 

For example, a store owner might list monthly sales totals for a large number of merchandise items in an Excel spreadsheet. If the owner wanted to know which items sold better in a particular financial quarter, he or she could use a pivot table. The sales quarters would be listed across the top as column labels and the products would be listed in the first column as rows. The values in the worksheet would show the sum of sales for each product in each quarter. A filter could then be applied to only show specific quarters, specific products or averages.

Uses of a pivot table

A pivot table helps users answer business questions with minimal effort. Common pivot table uses include:

• To calculate sums or averages in business situations. For example, counting sales by department or region.
• To show totals as a percentage of a whole. For example, comparing sales for a specific product to total sales.
• To generate a list of unique values. For example, showing which states or countries have ordered a product.
• To create a 2x2 table summary of a complex report.
• To identify the maximum and minimum values of a data set.
• To query information directly from an online analytical processing (OLAP) server.
• To identify trends and directions, such as monitoring sales trends or identifying behavior swings by customers.
• To compare data from different variables, such as product performance or monthly sales across different regions.
• To monitor financial performance (e.g., expense activity versus budgets) and for analysis of spending patterns.
• To prepare and deliver reports, especially interactive reports, as well as dashboards that automatically update when new data is entered.

What are pros and cons of using pivot tables?

Both advantages and disadvantages can be identified when discussing pivot tables:

Advantages

• Easy to summarize data. Large datasets can be converted into useful insights.
• Dynamic flexibility. Users can easily filter, sort, and rearrange data as requirements change.
• Saving time. Various functions, such as sums, averages and counts, can be automated, reducing the time needed to compute results manually.
• Dynamic and interactive reporting. Reports and dashboards can be created that automatically update when new data is entered.
• Support for large datasets. Pivot tables can handle very large datasets without reducing performance.

Challenges

• Not easy to learn. Knowledge of Excel or Google Sheets is necessary to use pivot tables effectively.
• May not automatically update. Changes in source data may necessitate manually refreshing the pivot table.
• Customization limits. There may be limitations to how tables are formatted as compared to standard tables.
• Concerns about performance. If older and possibly slower computers are used with large datasets, performance may be slow and crashes may occur.
• Error resolution. Failure to place data in the correct fields or misinterpreting data can lead to questionable results.

How to create a pivot table in Excel

Here is how to use Excel to create a pivot table:

1. Select the data to be analyzed

• Data already formatted as a table will be acknowledged by Excel.
• If the data is not already a table, Excel will ask to select the data range.

2. Insert a pivot table

• Click on the "Insert" tab at the top of the Excel ribbon.
• Click on the "Pivot Table" button. 

3. Choose placement

• The dialog box "Create PivotTable" displays options for placing the pivot table:

• • New Worksheet. This creates a new worksheet for the pivot table.
  • Existing Worksheet. This inserts the pivot table into an existing worksheet.

• Select the preferred placement and click "OK". 

4. Drag and drop fields

• View the "PivotTable Fields" list to see all data fields.
• Drag and drop data fields into one of these options to launch a pivot table:

• • Rows. These are to be used as row labels. 
  • Columns. These are fields to be used as column labels. 
  • Values. These are fields for summarizing or calculating data (e.g., Sum, Count, Average). 
  • Filters. These are fields that filter the data in the pivot table. 

5. Customize and analyze

• The pivot table can be customized once fields have been entered; options include:
• • Sort, filter, and group data.
  • Alter how data is summarized (e.g., Sum, Average, Count).
  • Create a pivot chart to visually present the data. 
• Update the data source:
  • For a data source change, refresh the pivot table to reflect the new and/or updated data. 
  • Refresh by clicking the "Refresh All" button in the "PivotTable Tools" ribbon. 

How to create a pivot table in Google Sheets

Here is how to use Google Sheets to create a pivot table:

1. Select the data to be analyzed

• The data set must have clear headers and no blank rows or columns.
• Next, select the range of data to be analyzed by highlighting it.

2. Insert a pivot table

• Select and click on Data > Pivot table.
• Select if the pivot table will be in a new worksheet or an existing one.

3. Set up the pivot table configuration

• When in the Pivot Table Editor, enter the necessary fields:
  • Rows such as for names and categories.
  • Columns such as for months and years.
  • Values such as for total sales, quantity of product sold.
  • Filters such as for product type or geographic region.

4. Customize and analyze

• Use the Pivot Table Editor to sort and filter data.
• Set or change the summarization method (e.g., Sum, Count, Average).
• To create a visual image such as a chart, use conditional formatting.

5. Refresh and update

• Update the table when data changes by clicking on Refresh.

See how to use a no-code application program interface, or API, to simplify spreadsheet analysis at scale.