10.4 PivotTables

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.

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.

Figure 10.34 The PivotTable wizard can help to create clean, interactive tables from raw data. (Used with permission from Microsoft)

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.

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).

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).

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.

Figure 10.39 The data is summarized in a simple table. (Used with permission from Microsoft)

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.

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)