|
  
Amazon.com
 
|
Using Excel Pivot Table Top 10 Filters
Filter a Pivot Table for Top 10 Items
Modify a Top 10 Filter
Filter a Pivot Table for Bottom 10 Items
Clear an Excel Pivot Table Value Filter
Filter a Pivot Table for Top 10 Percent
Filter a Pivot Table for Bottom Sum
Pivot Table Tutorial List
View the steps in a short Pivot
Table Top 10 Filters video
|
|
| |
|
Using Excel Pivot Table Top 10 Filters
You can summarize your data by creating
an Excel Pivot Table, and then use Value Filters to focus on the
top 10, bottom 10 or a specific portion of the total values in your
data.
For example, insteading of showing the total sales for all products,
use a filter to show just the top 10 products, or narrow it down to
the top 2.
In the example shown at the right, the City field has been filtered
to show only the top 2 cities, with the highest sales amounts.
Or, if you want to focus on the poor performers, you can use a value
filter to find the bottom 5 products or sales regions. 
|
|
| |
|
Filter a Pivot Table for Top 10 Items
In the example shown at the right, there are 24 months of Order dates
in the Row Labels area. In the Values area, you can see the total
sales for the first few order dates.
To filter the pivot table, so it shows only the Top 10 order dates,
follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate
field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, leave the default settings as
is (Top 10 Items Sales):
|
|
| |
|
- Click OK, to close the Top 10 Filter dialog box, and apply the
Value Filter.
As shown in the filtered example at the right, only the top 10 Order
Dates are now visible, still sorted by order date.
- To sort the amounts in descending order, right-click on one of
the amounts, and click Sort.
- Then click Sort Largest to Smallest.
Modify a Top 10 Filter
After you add a Top 10 Filter, you can change it, to show a different
result.
- In the Pivot Table, click the drop down arrow in the OrderDate
field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the number of Items to
5.
- Click OK, to close the Top 10 Filter dialog box, and apply the
Value Filter.
The results change, to show only the 5 order dates with the highest
sales amounts.
|
|
| |
|
Filter a Pivot Table for Bottom 10 Items
The Top 10 filter helps you find the highest amounts, but sometimes
you need to find the lowest amounts, to focus on those. Even though
the filter is named "Top 10", you can use it to find the
bottom amounts too.
To see only the Bottom 10 order dates, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate
field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, select Bottom from the first
drop down.
- Click OK, to close the Top 10 Filter dialog box, and apply the
Value Filter.
The results change, to show only the 5 order dates with the lowest
sales amounts.
|
|
| |
|
Clear an Excel Pivot Table Value Filter
When you've finished analyzing the filtered data, you can clear the
Value Filter, to see all the data again.
- In the pivot table, click the drop down arrow in the OrderDate
field heading.
- Click Clear Filter From OrderDate Field, to remove the filter
criteria, and show all the data.
|
|
| |
|
Filter a Pivot Table for Top 10 Percent
In addition to filtering for the top or bottom items, you can use
a Value Filter to show a specific portion of the grand total amount.
In the screenshot shown at the right, you can see that the grand total
sales amount is 663,732.
Ten percent of the grand total is 66,373, and you can use a Top 10
filter to find the top or bottom dates combine to total at least that
amount.
To see only the top selling order dates that contribute to 10% of
the total sales amount, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate
field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the settings to:
Top 10 Percent Sales.
The results change, to show only the top 2 order dates, because their
combined sales are greater than 10% of the original grand total amount.
|
|
| |
|
Filter a Pivot Table for the Bottom Sum
Another way to use the Top 10 Value Filter is to find the items that
make up a specific sum. For example, from the order dates with the
lowest sales amounts, which order dates would combine to total at
least 100,000 in sales.
To see only the bottom selling order dates that contribute to 100,000
of the total sales, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate
field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the settings to:
Bottom 100000 Sum Sales.
The results change, to show only the 6 lowest order dates, because
their combined sales are at least 100,000.
As you can see in the pivot table shown at the right, the bottom
5 order dates only total 98,165, so the 6th lowest order date is also
included in the Value Filter results, achieve our 100,000 minimum.
View the steps in a short Pivot
Table Top 10 Filters video
|
|
