Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot

CHAPTER 2Summarizing and Presenting Data with a Pivot Table

So, you have your data ready from the previous chapter. Maybe you have a list of data that was already in Excel or a list of data that you imported from another data source. You might have a few rows of data or maybe thousands of rows of data or maybe even a lot more than that. Your data might be on one sheet or spread across multiple sheets. Now, it is time to summarize that data with a pivot table.

What Is a Pivot Table?

A pivot table is a powerful reporting tool that allows you to summarize your data just about any way you want. A pivot table can provide subtotals, subcounts, and many other calculations. It can be one‐dimensional, giving you subtotals for just one field, or it can be multidimensional, allowing you to do a cross tab or a cross reference for two fields, where one field comprises the rows of the pivot table and the other field comprises the columns.

In the pivot table, each row will automatically have a total on the right, each column will automatically have a total at the bottom, and there will be a grand total on the right side of the bottom row. The rows and columns can contain multiple fields to provide more layers of detail for your table and more subtotals. The pivot table can be sorted and filtered the way you want, and that can be changed dynamically, which will be covered in Chapter 4, “Sorting and Filtering the Pivot Table.” It can also summarize your data by second, minute, hour, day, ...