Excel 2016 Bible

Chapter 34Analyzing Data with Pivot Tables

IN THIS CHAPTER

1. Creating a pivot table from nonnumeric data
2. Grouping items in a pivot table
3. Creating a calculated field or a calculated item in a pivot table
4. Understanding the Data Model feature
5. Creating an attractive report using a pivot table

The previous chapter introduced pivot tables. There, I presented several examples to demonstrate the types of pivot table summaries that you can generate from a set of data.

This chapter continues the discussion and explores the details of creating effective pivot tables. Creating a basic pivot table is easy, and the examples in this chapter demonstrate additional pivot table features that you may find helpful. I urge you to try these techniques with your own data. If you don't have suitable data, use the files available on this book's website.

Working with Nonnumeric Data

Most pivot tables are created from numeric data, but pivot tables are also useful with some types of nonnumeric data. Because you can't sum nonnumbers, this technique involves counting.

Figure 34.1 shows a table and a pivot table generated from the table. The table is a list of 400 employees, along with their location and gender. As you can see, the table has no numeric values, but you can create a useful pivot table that counts the items rather than sums them. The pivot table (in range E2:H10) cross-tabulates the Location field by the Sex field for the 400 employees and shows the count for each combination of location and ...