Excel 2019 Bible

679

C H A P T E R

30

Analyzing Data with PivotTables

IN THIS CHAPTER

Creating a PivotTable from non-numeric data

Grouping items in a PivotTable

Creating a calculated field or a calculated item in a PivotTable

Understanding the data model feature

T

he previous chapter introduced PivotTables. There we presented several examples to demon-

strate the types of PivotTable summaries that you can generate from a set of data.

This chapter continues the discussion and explores the details of creating effective

PivotTables. Creating a basic PivotTable is easy, and the examples in this chapter demonstrate addi-

tional PivotTable features that you may find helpful. We 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 Non-numeric Data

Most PivotTables are created from numeric data, but PivotTables are also useful with some types of

non-numeric data. Because you can’t sum non-numbers, this technique involves counting.

Figure 30.1 shows a table and a PivotTable generated from the table. The table consists of 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 PivotTable that counts the items rather than sums them. The

PivotTable (in range E2:H10) cross-tabulates the Location field by the Gender field for the 400

employees, and it shows the count for each combination of location and gender.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.