Microsoft Office Tutorials and References
In Depth Information
Counting Records: A Way to Break Out Text Data
Row Labels area. But what field is going to go into the Values area? The database in Figure
8–1 consists of exactly two fields: Guest and Table. And believe it not, even though the
Guest field comprises text—that is, the names of the guests—we can still drag it into the
Values area, because Excel will go ahead and perform the only mathematical operation it
can on text; it will count the guests by their table numbers.
And that’s how it works. If you drag a text-based field into the Values area, its contents
will be counted, and broken out by whatever field populates the Row Labels area. Thus,
with the dinner example, the guests will be counted by their table assignment—that is,
how many have been assigned to Table 1, Table 2, and so on.
But let’s try to illustrate this point with our salesperson data. Suppose we now want to
determine not how much money each salesperson has earned, but how many sales
each one has recorded. This exercise will demonstrate two important aspects of
PivotTables: your ability to place a text-based field into the Values area, and the fact that
you can apply the same field to a PivotTable twice.
First, make sure that the Salesperson field is positioned as usual in the Row Labels area.
Nothing new there; but now, we’re going to also drag and drop Salesperson into the
Values area, demonstrating that you can use the same field twice in a PivotTable. Once
you maneuver Salesperson into the Values area, you’ll see the data shown in Figure 8–24.
Figure 8–24 . Counting, not adding, sales. Note the Salesperson bars populating both the Row Labels and Values
The Salesperson bar in the Values area is recorded as Count of Salesperson, again
because Excel has no choice but to count text data (it can’t be added or averaged). In
effect, what we’ve done is count the number of times each salesperson’s name appears
in the database—which is another way of stating how many sales each one has
executed. And again, this will work the same way even if each salesperson has made
hundreds or even thousands of sales.