In Part I of our introduction to Microsoft Excel and Pivot Tables, we transformed raw data into a proper Excel Table and created our very first Pivot Table—only to discover that, by itself, it doesn’t do much.
In Part II, we finally bring that Pivot Table to life. This is where we start slicing, grouping, and summarizing data on demand—and where many QA professionals realize Excel can answer questions in seconds that used to take manual effort, formulas, or gut instinct.
As we proceed, you can follow along using the Excel workbook found here: GitHub. This PivotTableRowsColumnsValues.xlsx workbook was built specifically for this article.
First, notice that this workbook contains two worksheets: Sheet1 and Defects. The worksheet Defects contains the data in an Excel Table while the Sheet1 worksheet contains an empty Pivot Table beginning in cell A3 (which has been selected for you) and is named PivotTable1.
Notice that while a cell within the Pivot Table is selected, the PivotTable Fields panel appears on the right side of the worksheet. This panel is the primary user interface to the Pivot Table and is where we’ll begin our exploration.
At the top of this panel are controls allowing you to select which fields to add to the Pivot Table report. The Choose fields to add to report section shows all of the columns found in the Excel Table on the Defects worksheet as a list of fields that can be selected individually.
Beneath this is the Drag fields between areas below section—and this is where the real power of Pivot Tables begins to show itself. This section contains four areas: Filters, Columns, Rows, and Values. We’ll explore these sections next.
To begin, check each field one-by-one and observe how the Pivot Table changes. The DefectID field, when checked, changes the Pivot Table to display a Sum of DefectID header. The Pivot Table now shows the sum of all DefectIDs as its only row. Notice, too, that there is now a Sum of DefectID drop-down in the Values area of the Drag fields between areas below section.
Now, check each field in turn and notice the following behavior: when the Title field is checked, the Pivot Table changes to show each title as a row with a Row Labels header and a Grand Total footer. The header contains a drop-down that allows for sorting, filtering, and selecting which titles appear in the Pivot Table report. Also notice that the Title field is now shown in the Rows area of this same section. If you continue this process with the remaining fields, you will see similar behavior.
Next, check both the Title and Severity fields. Notice how you now have nested rows that are collapsible and expandable. This doesn’t feel powerful yet, but we’re getting very close. Up until now, Excel has decided where to place the checked fields in the four areas of the Drag fields between areas below section. We’re now going to exert control over this placement.
Start by clearing the checkbox before each field. The four areas should now be empty. As the name implies, a Pivot Table organizes data into rows, columns, and values, and the data can be filtered and sorted. The PivotTable Fields panel allows you to set a field as either a Filter, Column, Row, or Value. You do this by dragging and then dropping a field onto the desired area. For example, drag the Title field onto the Rows area. The Pivot Table report now appears similar to what you saw when you checked the Title field earlier. The key thing to understand is that you can drag almost any field onto any of the four areas and the Pivot Table will update accordingly. Therefore, to realize the full power of Pivot Tables, you need to understand what it means for a field to be either a filter, column, row, or value.
To understand this, we need to recall terminology from Part I. First, an observation is just a row of data in an Excel Table. It is one recorded instance or event. Next, a dimension is a column of data that describes or categorizes an observation. One or more dimensions provide context for each observation. A measure, on the other hand, is a calculated numeric value that summarizes data, such as a count, sum, or average.
It’s important to understand that values are aggregations where Excel has calculated a sum, count, or average, for example. The Values area is where measures belong. In other words, you place fields containing numeric values that can and should be counted, summed, averaged, or some other aggregated calculation.
Dimensions, however, naturally work as a row, column, or filter, depending on context. Dimensions slice the data. They describe how values are grouped, filtered, and organized within the Pivot Table report.
A field is just a column from the source Excel Table or range of data. A field becomes a dimension or measure based on where you place it.
At this point, you should have a solid mental model of how Pivot Tables work. Fields don’t have fixed roles—rather, they become dimensions or measures based on how they’re used. Rows, columns, filters, and values are simply different ways of organizing and aggregating the same underlying data.
In Part III, we’ll put this understanding to work. Using a new dataset, we’ll start building Pivot Tables intentionally—designing them to answer specific questions instead of exploring them by trial and error.
Leave a Reply