btabayarea.blogg.se

In the next dialog box, you can specify that you want customers that do not contain Excel, Exc, or Exc* (see Figure 4-22).The above example spreadsheet on the right shows sales figures for different sales people over 3 months. To filter out all of the Insurance customers, you can apply a Does Not Contain filter (see Figure 4-21). Text fields offer a flyout menu called Label Filters. However, the Label Filters option enables you to handle queries such as “select all customers that do not contain ‘Lotus.’” Nor is there a Toggle All Filter Choices choice. What if you want to find all the Lotus 1-2-3 consultants and turn those off? There is no Select Everything Except These Results choice. It would not work to find “Excel or spreadsheet.” Filtering using the Label Filters option Label filters would work to find every customer with Excel in the name. If you need to reapply the filter, it would be better to use the Label filters as discussed in the following section. If you add more data to the underlying data and refresh the pivot table, this filter will not be reevaluated. įilters applied with the search box are single-use filters. The product is hidden from view (see Figure 4-17).

Open the Product drop-down menu and clear the Doodads check box.

The check box filter provides an easy way to hide these items. Every company seems to have these orphan sales that no one really wants to see. It might be an old legacy product that is out of line, but it still gets an occasional order from the scrap bin. In the present example, the Doodads product line is a specialty product with very little sales. You might have a few annoying products appear in a pivot table. If you have multiple row fields, it is just as easy to sort using the invisible drop-down menus that appear when you hover over a field in the top of the PivotTable Fields list. If your pivot tables use Compact layout, you see a drop-down menu on the cell with Row Labels or Column Labels.

The pivot table in that figure is using Tabular layout. In Figure 4-16, a Customer drop-down menu appears in A4, and a Product drop-down menu appears in B3.

If you have a field (or fields) in the row or column area of a pivot table, a drop-down menu with filtering choices appears on the header cell for that field.