Microsoft Office Tutorials and References
In Depth Information
Examples of Filtering Your Data
Examples of Filtering Your Data
At this point in your exploration of pivot tables, you know enough to start creating your own pivot
table and specifying unique views. In this section, we share a few ways we like to view the data.
Although you could specify these views by hand, using the pivot table feature saves you hours of
work and allows you to more easily update and maintain your information.
Producing top and bottom views
You’ll often find that people are interested in the top and bottom measurement of things — for
example, the top 50 customers, the bottom 5 sales reps, the top 10 products. Although you may
think this is because they have the attention span of a four-year-old, there’s a more logical reason for
focusing on the outliers.
Effective dashboards and reports are often about showing actionable data. If you, as a manager,
know which accounts are the bottom ten revenue-generating accounts, you could apply your effort
and resources in building up those accounts. Because you most likely wouldn’t have the resources to
focus on all accounts, viewing a manageable subset of accounts would be more useful.
Luckily, pivot tables make it easy to filter your data for the top five, the bottom ten, or any
conceivable combination of top or bottom records. Here’s an example.
Imagine that in your company, the Accessories Business Segment is a high-margin business — you
make the most profit for each dollar of sales in the Accessories segment. To increase sales, your
manager wants to focus on the 50 customers who spend the least amount of money on Accessories. He
obviously wants to spend his time and resources on getting those customers to buy more
accessories. Here’s what to do:
1. Build a pivot table with Business Segment in the Filter area, Customer in the Row Labels area,
and Sales Amount in the Values area (see Figure 14-34). For cosmetic value, change the
layout to Tabular Form.
Figure 14-34: Build this pivot table to start.
Search JabSto ::

Custom Search