iTechtalk

molecule

Filtering Using a Report Filter Field in Excel 2007:-

To add fields to the report filter, you drag them from the top of the PivotTable Field List box to the Report Filter drop zone at the bottom of the PivotTable Field List box. You can drag multiple fields to the Report Filter section. However, you cannot drag a field that has already been used elsewhere in the pivot table.

For each field you add to the Report Filter section, you have a new drop-down in the PivotChart Filter Pane box. When you initially open this drop-down, you can select only one value from the drop-down. For example, you can select to filter the report to the east region, as shown in the first Figure (given below).



(Initially, each filter field allows you to select a single value.)

You can build filters that select multiple items. For example, to create a chart for two related customers, you would follow these steps:

1. Open the Customer drop-down in the Report Filter section of the PivotChart Filter Pane box.

2. In the lower-left corner, choose the Select Multiple Items check box. Then, annoyingly, Excel checks every customer in the list.

3. The top entry in the drop-down is (All). Uncheck this box, and all the other check marks are cleared.

4. Select the customers of interest, as shown in the second Figure (given below). Click OK.

The pivot chart is redrawn to show totals for only the selected customers. The Customer field in cell B2 shows the somewhat cryptic (Multiple Items) entry. At this point, you should add a title above the chart, perhaps in cell D2, to identify the customers shown in the chart.



(By using the Multiple Items check box, you can create a report for related customers.)

Any filters defined in the Report Filter field will persist when you later drop those fields in either the Axis or Legend fields. In the third Figure (given below), after pivoting the report, Excel presents a report comparing two customers.



(After pivoting the filtered Customer field, the filters persist.)