Category Archives: Sort and Filter

Show and Hide Pivot Table Details

In a pivot table, you can show all the summarized data, or focus on specific details. One way to hide information is with filtering, and that affects the pivot table totals. In the screen shot below, the Category field has … Continue reading

Posted in Sort and Filter | Leave a comment

Block New Pivot Items in Filtered Field

When you click on the arrow in a pivot table heading, you can manually filter the field’s items, by adding and removing check marks from the list of pivot items. That lets you focus the report on specific items, and … Continue reading

Posted in Sort and Filter | Leave a comment

Change the Report Filter Layout

By default, when you add Report Filters to a pivot table, they are shown in a single vertical list, above the body. If you add several filters, that list can get rather long, and it pushes the data down the … Continue reading

Posted in Sort and Filter | 7 Comments

Problems Adding Slicers in Excel 2010

Slicers were introduced in Excel 2010, and they make it easy to filter one or more pivot tables with a single click. In the screen shot below, “Bars” is selected in the product type Slicer. In the Product Slicer, the … Continue reading

Posted in Sort and Filter | Leave a comment

Create a Custom List for Pivot Table Sorting

Usually you sort a pivot table’s items alphabetically, or numerically. Here is our current pivot table, with the regions in alphabetical order. Sometimes you might want another sort order though, such as listing cities or regions in geographical order. In … Continue reading

Posted in Sort and Filter | 2 Comments

Hide Drop Down Arrows in Pivot Table Headings

When you add a field to the Row Label or Column Label area of the pivot table, its heading cell shows a drop down arrow. When you click the arrow, you’ll see Sort and Filter options, for the selected field. … Continue reading

Posted in Programming, Sort and Filter | 4 Comments

Create Worksheet for Each Pivot Item

Last week, Angella emailed me, to ask if there was a way to create a copy of a pivot table, for each item in a report filter. I suggested that she use a built-in pivot table feature, which is designed … Continue reading

Posted in Sort and Filter | 1 Comment

Filter All Pivot Tables for Specific Fields Only

On my Contextures website, there are a few sample files that let you filter one pivot table, and automatically change all the other pivot tables. You can find them on the Sample Files page, in the Pivot Table section. For … Continue reading

Posted in Programming, Sort and Filter | 4 Comments

Create Multiple Copies of Excel Pivot Table

In an Excel pivot table, you can put fields in the Report Filter area, like the City and YrMth fields in the pivot table shown below. If you need to print a report for a specific city, you can select … Continue reading

Posted in Sort and Filter | 4 Comments

Warning For Excel 2010 Slicers

One of the most useful new tools in Excel 2010 is the Excel Slicer. Add a Slicer to the workbook, and you can filter multiple pivot tables with a single click. In the screen shot below, there are Slicers for … Continue reading

Posted in Sort and Filter | Leave a comment