Search Contextures Sites

Excel Pivot Tables -- Field Settings

Contextures Excel news by email Email: Name (optional): We respect your email privacy           Amazon.com	Manually Hide or Show Subtotals Programmatically Hide Subtotals Show Items with No Data Show Top Items Only Sort All Fields in Ascending Order Download the sample file
	Manually Hide or Show Subtotals To manually hide subtotals for a field: Double-click the field button, to open the PivotTable field dialog box. For Subtotals, select 'None' Click OK To manually show subtotals for a field: Double-click the field button, to open the PivotTable field dialog box. For Subtotals, select 'Custom' Select one of the functions from the list, e.g. 'Average' Click OK Programmatically Hide Subtotals You can use a macro to hide subtotals in a PivotTable. This example uses the PivotField property, to hide all the subtotals. To hide only the Row field subtotals, use the RowFields property. To hide only the Column field subtotals, use the ColumnFields property. Sub NoSubtotals() 'pivot table tutorial by contextures.com 'turns off subtotals in pivot table '.PivotFields could be changed to '.RowFields or .ColumnFields Dim pt As PivotTable Dim pf As PivotField For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields 'Set index 1 (Automatic) to True, 'so all other values are set to False pf.Subtotals(1) = True pf.Subtotals(1) = False Next pf Next pt End Sub Show Items with No Data By default, the Pivot Table shows only the items for which there is data. In the example shown below, not all colours were sold to each customer. You may wish to see all the items for each customer, even those with no data. Watch the Video To see the steps for showing all the data in Excel 2007 / 2010, you can watch this short video tutorial. The written instructions are below the video. Show all the data in Excel 2007 / 2010 Make the following change for each field in which you want to see all the data: Right-click an item in the pivot table field, and click Field Settings In the Field Settings dialog box, click the Layout & Print tab. Check the 'Show items with no data' check box. Click OK Show all the data in Excel 2003 Make the following change for each field in which you want to see all the data: Double-click the field button, to open the PivotTable field dialog box. Check the 'Show items with no data' check box. Click OK Show Top Items Only Instead of showing all the items in a field, you can restrict the Pivot Table to show only the top (or bottom) items. Double-click the field button, to open the PivotTable field dialog box. Click the Advanced button Under 'Top 10 AutoShow', select On. For 'Show', select Top or Bottom Click the Scroll buttons, or type, to enter the number of items to show. Click OK, click OK Sort All Fields in Ascending Order After adding new records to your data, new items may appear at the end of the existing data, instead of being listed alphabetically. The following code will sort all fields in all Excel Pivot Tables. Sub SortAllFields() 'pivot table tutorial by contextures.com On Error Resume Next Application.ScreenUpdating = False Dim pt As PivotTable Dim ws As Worksheet Dim pf As PivotField For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.ManualUpdate = True For Each pf In pt.PivotFields pf.AutoSort xlAscending, pf.Name Next pf pt.ManualUpdate = False Next pt Next ws Application.ScreenUpdating = True End Sub Download the Sample File Download the zipped Excel 2007/2010 sample pivot table file for this pivot table tutorial Download the zipped Excel 2003 sample pivot table file for this pivot table tutorial

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

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

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