Unit 29: Pivot Table

What is a Pivot Table?

Excel’s Pivot Table is probably the most useful and time-saving tool for analysing data that’s in table format. In the simplest Pivot Table, one identifies a row value, a column value, and a data value. The data value (usually a numeric value) in this simple Pivot Table is automatically summarized at each row and column intersection. The data can be organized into a Pivot Table where Region values are the Pivot Table’s row field, Product values are the column field, and Revenue values are summarized in the data field. The Pivot Table invites data exploration and analysis: “slicing and dicing” large amounts of data is easy. The example Pivot Table above shows revenues for only three of the 14 products in the data. The Pivot Table can help a user spot trends and patterns in the data and allows for easy comparisons. Using a Pivot Table one can zoom in on particular data or parts of the data for micro-analysis or zoom out for macro views. Most of the features of the Pivot Table are intuitive for the user comfortable with the Windows/Office interface.1 The Pivot Table invites experimentation. After a few sessions with Pivot Table basics you’ll feel comfortable exploring the more advanced features on your own.

Basic Summary Calculations

Along with the default summary operations SUM and COUNT, the Pivot Table has a variety of other summary operations. To see the list of these additional operations, rightclick the label for a value field and choose the Value Field Settings… option from the context menu that displays. Excel opens the “Value Field Settings” dialog for the selected value field. On the “Summarize by” tab in this dialog are the summary options available: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp.

Through the creation of an Excel pivot table, you can quickly summarize lists of data by category in a tabular format. Furthermore, this data can be “pivoted,” or rearranged, so that the same data can be examined from a different angle or dimension. A pivot table can summarize data into categories using functions such as SUM, MAX, MIN, AVERAGE, COUNT, as well as other Excel functions. You can even display pivot table data as a percentage of the grand total for the data being examined. A pivot table is an interactive data-mining tool that can be used to extract information from the raw data that is being examined.

All areas of business (accounting, marketing, finance, management) use pivot tables as part of their data analyses. Employers recruiting students from universities for internships and post-graduation jobs include the skills of building pivot tables and being able to interpret the data found in pivot tables as part of their desired skill sets. This is further seen in business advisory board meetings conducted by university departments where board members indicate the need for student pivot table skills and improved student pivot table skills.

Despite this importance, many students wonder “what are pivot tables?” and “how do you build a pivot table?” often indicating that “I have never heard of pivot tables before.” Contributing to this problem is that many textbooks that cover spreadsheet skills include minimal pivot table coverage. Pivot table coverage is often toward the end of the textbook because textbook authors consider pivot tables to require “advanced skills.” The goal of this tutorial is to overcome that.

In order to build a pivot table and conduct your data analysis, the following dimensions of data should be specified.

  • The field to be used to create row items in the pivot table.

  • The field to be used to create column headings in the pivot table.

  • The field or fields to be used as data items.

At its most basic level, a pivot table is composed of rows, columns and data. Once the basic concepts of pivot table creation have been mastered, more complex and advanced pivot tables can be created.

Examples of more advanced and complex pivot tables include:

  • A pivot table that has rows, but not columns. ? A pivot table that has columns, but not rows.

  • A pivot table that can be filtered using an additional data field.

  • A pivot table that contains multiple fields as data items, often displaying data being summarized using different function operators

As part of this tutorial exercise, you will gain experience building pivot tables, starting with simple pivot tables and then progressing to more advanced and complex pivot tables.


There aren't any posts currently published in this category.