You can hide or display data to show in a PivotTable report by using Filters. This way you can focus not only on a specific field or item but also the determine the criteria of displayed values viz. suppose you want to display sales of only a particular car model(s) and just the top 10 sales figures, you can use filters to narrow down the report by selecting the specified model(s) and choosing ‘Top 10’ in the value filters.

When you apply filters, it displays only a subset of the data based on the filter criteria and you can apply additional filter(s) on this already reduced subset. You can control to allow mutliple filters to be set or not by going to the dialog box of ‘PivotTable Options’, in ‘Totals & Filters’ tab, in the ‘Filters’ section select or clear check box of ‘Allow multiple filters per field’. Only if you allow multiple filters can you apply a further criteria to the already filter reduced subset of data. To display filter drop down buttons for fields along with the field captions (row & column labels) at the top of the Pivot Table – in the dialog box of ‘PivotTable Options”, click on ‘Display’ tab, and in the ‘Display’ section select the check box of ‘Display field captions and filter drop downs’. Note that you can apply filters even if you do no display filter drop down buttons.

Report Filter (appearing at the top) is the field on which the PivotTable report is filtered. In case the Year field is assigned to this area, the report will summarize data for either one or more years, as selected. Report Filters are used to display and focus on a subset of data in a Pivot Table report, for example, to display data by time period (viz. year), by geographical area (viz. countrywise), or products (viz. cars, trucks, … for an auto company), or plants (viz. Plant A, Plant B, ..). Using Manual, Label & Manual Filters narrow down the focus within this subset of data.

Manual Filter

Manual Filter will means when you can Filter by selection. You can quickly select all, clear all or select multiple items in the field that you want to filter by selection.

Row & Column Labels: You can choose to display all items in a field or to display selected items only, by clicking on filter drop down button appearing next to the Row or Column Labels (in the Pivot Table report) and selecting or clearing the check boxes in the list of items. ‘(Select All)’ at the top will select all items, clearing this checkbox will deselect all items and then you can select one or more items by checking them. Another way to filter by selection is that in a Pivot Table report you select the items in the field that you want to filter by selection, right-click and point to ‘Filter’ in the list of commands, and click on either ‘Hide Selected Items’ or ‘Keep Only Selected Items’.

Report Filters: You can choose to display all items in a field or to display selected items only, by clicking on filter drop down button appearing next to the field in a Report Filter. Clicking on ‘(All)’ at the top will select all items (default), or you can select one item at a time by selecting it and clicking OK or else to select multiple items, select the check box at the bottom ‘Select Multiple Items’ and then check one or more items. You can also display a report based on each Report Filter Item on a separate worksheet – in the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, click on ‘Options’ in the ‘PivotTable’ group, and then click ‘Show Report Filter Pages’ in the list which opens the ‘Show Report Filter Pages’ dialog box. Select the Report Filter Field here, each of whose items’ report you want to display on separate worksheets. If you have selected ‘Year’ as the Report Filter Field, and it has 3 items (ie. 3 years of 2010, 2011 & 2012), then 3 separate worksheets get created which display a PivotTable report consisting of a set of values which are based respectively on the years of 2010, 2011 & 2012.

Include new items in manual filter: If a Manual Filter has been applied to a field in a PivotTable report, and a new item gets added in that field in the source data, refreshing the Pivot Table displays the new item in the Pivot Table report wherein it automatically gets selected in the manual filter, even if it does not meet the filter criteria. This happens if the check box of ‘Include new items in manual filter’ is selected in the ‘Filter’ section of the ‘Subtotals & Filters’ tab in the ‘Field Settings’ dialog box. To not display this new item which is added in source data, clear the check box of ‘Include new items in manual filter’.

Label Filters

Label Filters apply criteria to filter Labels ie. item labels of row and column fields. To set Label Filters, click on filter drop down button appearing next to the Row or Column Label and point to ‘Label Filters’, you will get a list of filter commands (viz. Equals, Does Not Equal, ….). You can also select a cell in the row or columns area of the Pivot Table and right-click to access ‘Label Filters’ in re. of the field which is right-clicked. Selecting any filter command from the list opens the dialog box of ‘Label Filter (<Field Name>)’ where you enter criteria in the box on the right which appears under the caption ‘Show items for which the label’. This will allow you to enter criteria for item labels of the field (which Field Name also appears in the dalog box) and the items whose labels match the criteria will be displayed.

List of Filter Commands:

You can use the following Filter Commands for Label Filters, whether the Label is a Text value or say, month numbers.

Equals: Refer Image 1a which shows that ‘Equals’ has been selected from the list of commands, to apply the filter to the field ‘Country’ and the criteria entered in the box is ‘USA’. Image 1b shows the Pivot Table Report which displays data only in respect of the country ‘USA’ and hides data for the other 3 countries ‘Canada’, ‘France’ & ‘UK’. If filtering is done on months where the item labels are month numbers 01, 02, … 12, entering 05 in the filter criteria would show data for month number 05 only.

Does Not Equal: Entering ‘USA’ in the criteria box in the preceding example, will display the countries ‘Canada’ & ‘UK’ and not ‘USA’ in the Pivot Table report. If filtering is done on months where the item labels are month numbers 01, 02, … 12, entering 05 in the filter criteria would show data for all month numbers except month 05.

Begins With: Refer Image 2a which shows that ‘Begins With’ has been selected from the list of commands, to apply the filter to the field ‘Country’ and the criteria entered in the box is ‘u’. Image 2b shows the Pivot Table Report which displays data only in respect of the countries starting with ‘u’ ie. ‘USA’ and ‘UK’ and hides data for the other 2 countries ‘Canada’ & ‘France’. If filtering is done on months where the item labels are month numbers 01, 02, … 12, entering 1 in the filter criteria would show data for month numbers 10, 11 & 12 only.

Does Not Begin With: Entering ‘u’ in the criteria box in the preceding example, will display the countries ‘Canada’ & ‘UK’ but not ‘USA’ and ‘UK’ in the Pivot Table report. If filtering is done on months where the item labels are month numbers 01, 02, … 12, entering 1 in the filter criteria would show data for month numbers 01 to 09 but not for months 10, 11 & 12.

Ends With: Entering ‘a’ in the criteria box in the example, will display the countries ‘USA’ & ‘Canada’ but not ‘France’ & ‘UK’ in the Pivot Table report.

Does Not End With: Entering ‘a’ in the criteria box in the example, will display the countries ‘France’ & ‘UK’ but not ‘USA’ & ‘Canada’ in the Pivot Table report.

Contains: Entering ‘a’ in the criteria box in the example, will display the countries ‘USA’, ‘France’ & ‘Canada’ but not ‘UK’ in the Pivot Table report.

Does Not Contain: Entering ‘a’ in the criteria box in the example, will display the country ‘UK’ but not ‘USA’, ‘France’ & ‘Canada’ in the Pivot Table report.

Greater Than: Entering ‘France’ in the criteria box in the example, will display the countries ‘UK’ & ‘USA’ but not ‘Canada’ & ‘France’ in the Pivot Table report. In case of non-numericals the criteria calculation is based on ‘alphabetic order’.

Greater Than Or Equal To: Entering ‘France’ in the criteria box in the example, will display the countries ‘France’, ‘UK’ & ‘USA’ but not ‘Canada’ in the Pivot Table report.

Less Than: Entering ‘France’ in the criteria box in the example, will display the country ‘Canada’ but not ‘France’, ‘UK’ & ‘USA’ in the Pivot Table report.

Less Than Or Equal To: Entering ‘France’ in the criteria box in the example, will display the countries ‘Canada’ & ‘France’ but not ‘UK’ & ‘USA’ in the Pivot Table report.

Between: Entering ‘France’ and ‘USA’ in the two criteria box in the example, will display the countries ‘France’, ‘UK’ & ‘USA’ but not ‘Canada’ in the Pivot Table report.

Not Between: Entering ‘France’ and ‘USA’ in the two criteria box in the example, will display the country ‘Canada’ but not ‘France’, ‘UK’ & ‘USA’ in the Pivot Table report.

Using Wild Card Characters:

You can use the following wild card characters while entering crietria for Label Filters:

? (question mark) – Use ? to represent any single character, viz. entering w?nt will search want, wont, went, etc.

* (asterisk) – Use to * represent any series of characters, viz. *noon will search forenoon, afternoon, … and good* will search goodbye, goods, goodwill, …

To show the characters ? (question mark), * (asterisk) and ~ (tilde), use ~ before these viz. how~? will search how?.

Example: Refer Image 3a, where we apply a label filter on month numbers using the ‘Equal’ coomand and enter criteria as ‘1?’. Image 3b displays the Pivot Table report showing data for the months of 10, 11 & 12 only out of month numbers 01 to 12.

Value Filters

Value Filters apply criteria to filter values of items in row and column fields. To set Value Filters, click on filter drop down button appearing next to the Row or Column Label and point to ‘Value Filters’, you will get a list of filter commands (viz. Equals, Does Not Equal, ….). You can also select a cell in the row or columns area of the Pivot Table and right-click to access ‘Value Filters’ in re. of the field which is right-clicked. Selecting any filter command from the list opens the dialog box of ‘Value Filter (<Field Name>)’ where you enter criteria in the box on the right which appears under the caption ‘Show items for which’. In case of multiple value fields, you can select the value field for which to apply filter from the pull down on the left in the dialog box. This will allow you to enter criteria for filtering values in re. of the field (which Field Name also appears in the dalog box) and the items of that field whose values match the criteria will be displayed. Note that only numbers are need to be entered as criteria (you can enter ‘5%’ for a %age field).

List of Filter Commands:

Equals: Refer Image 4a which shows that ‘Equals’ has been selected from the list of commands, to apply the filter to the field ‘Country’ for value field ‘Sales- $’ and the criteria entered in the box is ‘7180’. Image 4b shows the Pivot Table Report which displays data only where the Sales of a Country equal to 7,180.

Does Not Equal: Refer Image 5a which shows that ‘Does Not Equal’ has been selected from the list of commands, to apply the filter to the field ‘Country’ for value field ‘Sales- $’ and the criteria entered in the box is ‘7180’. Image 5b shows the Pivot Table Report which displays data for all Countries where Sales are not equal to 7,180.

Greater Than: Refer Image 6a which shows that ‘Greater Than’ has been selected from the list of commands, to apply the filter in re. of the field ‘Country’ for value field ‘Budgeted Sales- $’ and the criteria entered in the box is ‘7500’. Image 6b shows the Pivot Table Report which displays data only for those Countries where Budgeted Sales are Greater Than 7,500.

‘Greater Than Or Equal To’, ‘Less Than’, ‘Less Than Or Equal To’, ‘Between’, ‘Not Between’: these Filter Commands are self-explanatory and are applied in the same manner as explained above.

Top 10: Selecting ‘Top 10’ filter command from the list opens the dialog box of ‘Top 10 Filter (<Field Name>)’ where you select criteria in the boxes which appear under the caption ‘Show’. The ‘Field Name’ indicates the field in re. of which the filter is applied. Note that Top 10 does not necessarily mean that you can filter only the Top 10 values, these could be bottom 5 also ie. you have options to choose either Top or Bottom values and also a different number than 10. The first pull-down has 2 options – ‘Top’ and ‘Bottom’ and you can choose to display either the highest values or the lowest values in the Pivot Table report. In the second box you can scroll up or down numbers which start from 1 (default is 10) and you can choose the number of Top or Bottom values to be displayed. The third pull-down has 3 options of Items, Percent & Sum. Choosing ‘Items’ will display the specified number of Items containing the Top or Bottom values, in the Pivot Table report. Choosing ‘Percent’ will display the items which comprise the specified percent of the Total ie. the items that combine to contribute to the specified percent of the overall total. Choosing ‘Sum’ will display the Items which comprise the specified Total of values ie. the items that combine to contribute to the specified total.

Example – filter by ‘Top 10’:

Refer Image 7a where we apply a ‘Top’ ‘4’ ‘Items’ filter on the field name ‘Month’ for the value field ‘Sales-$’. Image 7b shows the top 4 items (months) by value.

Refer Image 8a where we apply a ‘Top’ ’10’ ‘Percent’ filter on the field name ‘Month’ for the value field ‘Budgeted Sales-$’. Image 8b shows the filtered values – the total ‘Budgeted Sales-$’ for all 12 items (months) for the SubCompact model is 20,650 and 2 items (months) were required to be combined to contribute to atleast 10% of 20,650 ie. 2,065. However, the total ‘Budgeted Sales-$’ for all 12 items (months) for the Compact model is 23,120 and only 1 item (month) was required to contribute to atleast 10% of 23,120 ie. 2,312.

Refer Image 9a where we apply a ‘Bottom’ ‘9000’ ‘Sum’ filter on the field name ‘Month’ for the value field ‘Sales-$’. Image 9b shows the filtered values – the lowest value items which are required to combine to contribute to a Total value for ‘Sales-$’ of atleast 9,000, are displayed.

Filter by Date or Time values

Note that applying Manual Filters has already been explained above.You can choose to display one or more dates or times, by clicking on filter drop down button appearing next to the Row or Column Label (in the Pivot Table report) and selecting or clearing the check boxes in the list of dates or times.

Date Filters apply criteria to a field having Date or Time values. To set Date Filters, click on filter drop down button appearing next to the Row or Column Label and point to ‘Date Filters’, you will get a list of filter commands (viz. Equals, Before, After, ….). You can also select a cell in the row or columns area of a Date Field in the Pivot Table and right-click to access ‘Date Filters’.

Selecting one of these filter commands – ‘Equals’, ‘Before’, ‘After’, ‘Between’ or ‘Custom Filter’ – from the list, opens the dialog box of ‘Date Filter (<Field Name>)’ where you select/enter filter criteria in the box(es) which appears under the caption ‘Show items for which the date’. In the first pull-down list you can select from the options: ‘equals’, ‘does not equal’, ‘is before’, ‘is before or equal to’, ‘is after’, ‘is after or equal to’, ‘is

between’, ‘is not between’ and then select/enter in the box(es) to its right which also has a date selector.

If you point to ‘All Dates in the Period’ in the list of filter commands, another sub-list of commands opens containing the 4 quarters and the 12 months – ‘Quarter 1’ to ‘Quarter 4’, and ‘January’ to ‘December’, from which you can choose the relevant command. Image 10a shows that the Date Filter of ‘Quarter 3’ is being applied to the ‘Date’ field. Image 10b shows the filtered data for the 3rd quarter of the year viz. dates falling within the 3 months of July-Sept.

Dynamic Filter for Date Range:

You can filter Date Values with a Date Range which is relative to the Current Date. For example, selecting ‘Next Month’ as the criteria will display data for the month of November if your current date is October, and if you reapply this filter after a month in November, then ‘Next Month’ criteria will display data for the month of December.

Point to ‘Date Filters’, in the list of filter commands those which can be used relative to the Current Date are – ‘Tomorrow’, ‘Today’, ‘Yesterday’, ‘Next Week’, ‘This Week’, ‘Last Week’, ‘Next Month’, ‘This Month’, ‘Last Month’, ‘Next Quarter’, ‘This Quarter’, ‘Last Quarter’, ‘Next Year’, ‘This Year’, ‘Last Year’ and ‘Year to Date’. Image 11a shows that the Date Filter of ‘Year to Date’ is being applied to the ‘Date’ field on Sept 11, 2012 which is the current date. Image 11b shows the filtered data for all dates of the year till the current date of Sept 11 viz. dates falling within January 01, 2012 till Sept 11, 2012.

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 PivotTable 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’.

Multiple Filters on a Pivot Table Field

To allow more than one filter (ie. mutiple filters) to be set for a Field, open the dialog box of ‘PivotTable Options’ -> in the ‘Filters’ section of the tab ‘Totals & Filters’, select the check box of ‘Allow multiple filters per field’. Clearing the check box will allow only a single filter to be set.

You can set a Manual Filter, Label Filter and Value Filter together for a particular pivot field, but limited to a single filter of each filter type.

Refer Image 12 where multiple filters have been applied to the ‘Month’ field. First, we have applied a Manual Filter by selecting only even month numbers (02, 04, 06, 08, 10 & 12). Then a Label Filter has been applied with the criteria ‘Greater Than’, ’04’, which reduces the display to month numbers 06, 08, 10 & 12. Then a Value Filter has been applied on the value field of ‘Sales-$’, with criteria ‘Greater Than’, ‘1,500’ by which the Pivot Table report displays from within the already reduced 4 months only those months when sales exceed 1,500.

Excel Pivot Tables: Filter Data, Items, Values & Dates

Excel Pivot Tables: Filter Data, Items, Values & Dates

Excel Pivot Tables: Filter Data, Items, Values & Dates

VBA TUTORIAL LOGIN

Cond Statements in VBA Test

Text Strings in VBA

Custom Number Formats

Excel VBA Built-in Events

ActiveX & Form Controls

Application.OnTime Method

MsgBox & InputBox in VBA

Leave a Reply Cancel reply