Excel Pivot Table Report – Clear All, Remove Filters, Select Mutliple Cells or Items, Move a Pivot Table
As applicable to Excel 2007
With the tools available in the Actions group of the ‘Options’ tab (under the ‘Pivot Table Tools’ tab on the ribbon), you can Clear a Pivot Table, Remove Filters, Select Multiple Cells or Items, and Move a Pivot Table report.
Excel Pivot Table Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
3. Pivot Table Report – Clear Pivot Table, Remove Filters, Select Multiple Cells or Items, Move a Pivot Table
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Pivot Table Options tab – Actions group
Customizing a Pivot Table report: When you insert a Pivot Table, a blank Pivot Table report is created in the specified location, and the ‘PivotTable Field List’ Pane also appears which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings. The ‘Options’ and ‘Design’ tabs (under the ‘PivotTable Tools’ tab on the ribbon) have various tools to customize the Pivot Table report and determine its structure, layout and design. In this section we discuss the tools available in the Actions group of the ‘Options’ tab.
Clear All & Clear Filters:
Clear a Pivot Table or Pivot Chart report:
If you are not satisfied with the Pivot Table report and want to start doing the layout, structure & design right from the start, you can clear all data in the report. This removes all fields, values, formatting and filters and makes it equivalent to the initial blank Pivot Table which appears while creating, but does not delete the report. To do this, in the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Clear’ and then click ‘Clear All’.
To clear a Pivot Chart report, select it and then in the ‘Data’ group (on the ‘Analyze’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Clear’ and then click ‘Clear All’.
Remove Filters:
To Remove Filters, click on filter icon on the drop down button appearing next to the Row or Column Label where filter has been applied, and then click on ‘Clear Filter From <Field Name>’. Another way is to click on the the filter icon and in the drop-down list select the checkbox ‘(Select All)’ at the top, which makes all data visible and removes filter from the field. You can also select a cell in the row or columns area of the Pivot Table (where filter has been applied) and right-click, point to ‘Filter’ which will show a list of commands in which click on ‘Clear Filter From <Field Name>’. These methods remove a filter from a specific field. To remove all filters in a Pivot Table report in one go, in the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Clear’ and then click ‘Clear Filters’.
Select Multiple Cells or Items in a Pivot Table report:
Select Entire Pivot Table report:
In the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Select’ and then click ‘Entire PivotTable’. This will select all cells in the Pivot Table report.
‘Enable Selection’ in a Pivot Table – Select Item Labels, Values or Both:
Select a cell in the rows or columns area in the Pivot Table report, in the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon) click on ‘Select’ and then click ‘Enable Selection’. If you have selected a single item label in the Pivot Table report, then all instances of that single item label and/or its values get selected by using the commands ‘Labels and Values’, ‘Values’ or ‘Labels’. If you have selected a row or column label, then all items under that row or column label and/or its values get selected. Clicking ‘Labels and Values’ selects both item label(s) and its corresponding values, clicking ‘Values’ selects only values, and clicking ‘Labels’ selects only item label(s).
Select all instances or one instance of a single item, or select multiple items:
Move your mouse on the left edge of a row item label till the pointer shows as a right arrow, and then click once – all instances of that item (and its corresponding values) in the Pivot Table report get selected. Refer Image 1a where the right-arrow on the left edge of ‘France’ was clicked and all occurrences of France and its Sales are selected. In the second click, all instances of ‘France’ and all instances of its next outer row item label (‘Europe’) also get selected – refer Image 1b. In the third click, all instances of ‘France’, its outer row item label (‘Europe’) and the next outer row item label (‘SubCompact’) also get selected – refer Image 1c. Because ‘SubCompact’ is the outermost row item label, it gets selected only once and this means that only a single instance of item ‘France’ gets selected. Clicking again, ie. the fourth click, will revert back to Image 1a where all instances of the item ‘France’ are selected.
For a single item, as explained above, move your mouse on the left edge of a row item label till the pointer shows as a right arrow, and then click once – all instances of that item (and its corresponding values) in the Pivot Table report get selected. To select multiple items with their corresponding values, keep the SHIFT key pressed (to select all items within the outer item row label) or keep the CTRL key pressed and then click on right-arrow.
Above we have explained how to select row field items. In the case of column fields, move your mouse on the top edge of a column item label till the pointer shows as a down arrow, and then click, and follow the same steps as explained above.
Select all Items in a Field:
To select all items in a field viz. only the items and not the corresponding values, move your mouse on the top edge of the row or column label till the pointer shows as a down arrow, and then click. Refer Image 1d.
Move a Pivot Table report:
To move a Pivot Table report, in the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Move PivotTable’ which opens the ‘Move PivotTable’ dialog box. You can move a Pivot Table report either to a new worksheet or in the existing worksheet itself by choosing the relevant option in the dialog box. Moving to a new worksheet will place the Pivot Table report starting at cell A1. To move in the existing worksheet, enter/select the starting cell where you want the Pivot Table report to be placed, in the ‘Location’ box.