Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

pivot table recipes 

 

 

 

Learn how to create Excel dashboards.

 

 

 

160x600

Excel Pivot Tables -- Field Setting

  1. Manually Hide or Show Subtotals
  2. Programmatically Hide Subtotals
  3. Quickly Remove a Pivot Field
  4. Add All Remaining Fields to Layout
  5. Video: Show Items with No Data
  6. Show Items with No Data
  7. Missing Data in Pivot Table
  8. Show Top Items Only
  9. Include New Items in Manual Filter
  10. Video: Repeat Item Labels
  11. Repeat Item Labels
  12. Sort All Fields in Ascending Order
  13. Download the sample file

Manually Hide or Show Subtotals

To manually hide subtotals for a field:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. For Subtotals, select 'None'
  3. Click OK

To manually show subtotals for a field:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. For Subtotals, select 'Custom'
  3. Select one of the functions from the list, e.g. 'Average'
  4. Click OK

pivot hide subtotals

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   

Quickly Remove a Pivot Field

After you create a pivot table, you might want to remove a field from the layout. You don't need to go to the field list, find that field and remove its check mark, nor do you need to drag the pivot field out of the Row Labels area in the field list.

To quickly remove a pivot field from the layout:

  1. Right-click on an item in the pivot field. In this example, a cell in the Product field was right-clicked.
  2. Click Remove [field name]

    remove pivot field

The pivot field is immediately removed from the layout

To see the steps for quickly removing a pivot field, you can watch this short video tutorial.

Add All Remaining Fields to Layout

In the Pivot Table Field List, you can check a field name to add it to the pivot table layout. You have to do these one at a time though -- there isn't a "Select All" checkbox.

pivot fields list check boxes

With the following code, you can add all the unchecked fields to either the Row Labels area or to the Values area of the layout.

Put this code in a regular code module. Then select a cell in the pivot table that you want to update, and run the macro.

Add All Remaining Fields to Row Labels

If you have a long list of fields, you could manually add a few to the Values area, or the Report Filter area. Then use the following code to add the remaining fields to the Row Labels area.

Sub AddAllFieldsRow()
Dim pt As PivotTable
Dim iCol As Long
Dim iColEnd As Long

Set pt = ActiveSheet.PivotTables(1)

With pt
    
   iCol = 1
   iColEnd = .PivotFields.Count

    For iCol = 1 To iColEnd
        With .PivotFields(iCol)
          If .Orientation = 0 Then
              .Orientation = xlRowField
          End If
        End With
    Next iCol

End With

End Sub

Add All Remaining Fields to Values Area

If you have a long list of fields, you could manually add a few to the Row Labels area. Then use the following code to add the remaining fields to the Values area.

Sub AddAllFieldsValues()
Dim pt As PivotTable
Dim iCol As Long
Dim iColEnd As Long

Set pt = ActiveSheet.PivotTables(1)

With pt
    
    iCol = 1
   iColEnd = .PivotFields.Count

    For iCol = 1 To iColEnd
        With .PivotFields(iCol)
          If .Orientation = 0 Then
              .Orientation = xlDataField
          End If
        End With
    Next iCol

End With

End Sub

Video: 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.

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 Items with No Data

When you create a Pivot Table, it only shows the items for which there is data. In the pivot table shown below, not all colours were sold to each customer. You can change a pivot table setting, to see all the items for each customer, even the items with no data.

Show all the data in Excel 2007 / 2010

Make the following change for each field in which you want to see all the data:

  1. Right-click an item in the pivot table field, and click Field Settings
  2. In the Field Settings dialog box, click the Layout & Print tab.
  3. Check the 'Show items with no data' check box.
  4. Click OK

pivot items no data 2010

Show all the data in Excel 2003

Make the following change for each field in which you want to see all the data:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. Check the 'Show items with no data' check box.
  3. Click OK

pivot items no data

Missing Data in Pivot Table

To see items with no data in a pivot table, you can change the pivot table settings, as described in the section above.

However, that setting only displays items that are included in the source data at least once. For example, if you recently started selling a new product, but have no sales yet, it won't appear in your pivot table if you turn on the "Show Items With No Data" setting.

To show missing data, such as new products, you can add one or more dummy records to the pivot table, to force the items to appear.

dummy record in source data

For example, to include a new product -- Paper -- in the pivot table, even if it has not yet been sold:

  1. In the source data, add a record with Paper as the product, and 0 as the quantity
  2. Refresh the pivot table, to update it with the new data
  3. Right-click a cell in the Product field, and click Field Settings.
  4. On the Layout & Print tab, add a check mark in the 'Show items with no data' box.
  5. 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.

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. Click the Advanced button
  3. Under 'Top 10 AutoShow', select On.
  4. For 'Show', select Top or Bottom
  5. Click the Scroll buttons, or type, to enter the number of items to show.
  6. Click OK, click OK

pivot top items

Include New Items in Manual Filter

If you click on the arrow in a pivot table heading, you can filter the field's items, by using the check boxes. In the screen shot below, only two of the technician names have been selected, and the others will be hidden.

manual filter check boxes

However, if you add new records in the source data, or update the existing records, new names might be added to the source data.

Then, if you refresh the pivot table, those new names can appear in the filtered pivot table, even though they were not originally selected. Here, Smith appears in the pivot table, after that name was added to the source data.

manual filter new item

To prevent new items from appearing after a manual filter has been applied, you can change a setting for the pivot field:

  1. Right-click one of the items in the pivot field, and click Field Settings
  2. On the Subtotals and Filters tab, in the Filter section, remove the check mark for 'Include New Items in Manual Filter'
  3. Click OK

    manual filter new items setting

NOTE: If any new items were included before you change the setting, go back to the manual filter and remove the check marks for those items.

manual filter new items setting

To see the steps for changing the pivot field setting, please watch this short video tutorial.

Video: Repeat Item Labels

To see the steps for repeating labels in all fields, or a single field, please watch this short Excel pivot table video tutorial. The written instructions are below the video.

Repeat Item Labels

In Excel 2010, and later versions, you change a field setting so that the item labels are repeated in each row.

repeat item labels

This feature does not work if the pivot table is in Compact Layout, so change to Outline form or Tabular form, if necessary, before following the rest of the steps.

To change the report layout:

  1. Select a cell in the pivot table
  2. On the Ribbon, click the Design tab, and click Report Layout
  3. Click Show in Outline Form, or click Show in Tablular Form

To show the item labels in every row, for all pivot fields:

  1. Select a cell in the pivot table
  2. On the Ribbon, click the Design tab, and click Report Layout
  3. Click Repeat All Item Labels

To show the item labels in every row, for a specific pivot field:

  1. Right-click an item in the pivot field
  2. In the Field Settings dialog box, click the Layout & Print tab
  3. Add a check mark to Repeat item labels, then 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
   

Pivot Table Training

If you're looking for a video-based pivot table course,I recommend the following. Both are excellent quality, with clear, easy-to-follow instructions.

Xtreme Pivot Table course

By John Michaloudis, at My Excel Online.

Videos: 200+, total time, approx 9 hours, download

Sample Excel workbooks, to use with the video lessons.

Summary: Detailed coverage of pivot table topics, with in-depth coverage of topics.

Core Pivot

By Dave Bruns at ExcelJet.

Videos: 49, total time, approx 2.5 hours, online

Sample Excel workbooks, to use with the video lessons.

Summary: Short, to the point, coverage of pivot table topics, for rapid learning

 

More Pivot Table Resources

Tutorials:

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

 

Last updated: November 2, 2014 3:39 PM