Search Contextures Sites

Excel Pivot Table Filters - Top 10

Amazon.com      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

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2011 All rights reserved.	Debra Dalgleish

Last updated: March 5, 2011