Scroll Through Filter Items in Excel Table

To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading. Someone asked me if there was a way to scroll through filter items, instead of opening the filter list each time. The technique described below uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of filter items.

Scroll Through Filter Items Demo

This animated screen shot shows how the scrolling technique works. Use the Spin Button to scroll through filter items.

  • Click the Up button, to filter by the next product in the list
  • Click the Down button, to filter by the previous product in the list
  • When you reach the beginning or end of the list, the next selection is "All"

pivotspinnertablefilter

Why Use Spin Buttons?

I wrote the original code for this technique long ago, to scroll through filter items in a pivot table report filter. Slicers hadn't been invented yet, and the spin button was a quick way to filter a pivot table.

Now you can use Slicers to filter a pivot table or Excel table, but they take up a lot of space on a worksheet. A Spin Button is a compact way to go through a list of items, in alphabetical order.

In this screen shot, you can see the size of the Spin Button, compared to a Slicer for the Product field.

pivotspinnertablefilter01

How to Set Up the Spin Buttons

First, I added an ActiveX Spin Button on the worksheet – there are detailed instructions on the Report Filter Macros page of my website.

NOTE: In Excel for Mac, ActiveX controls are not available. You would have to use the Spin Button from Form Controls, and create code to work with that.

pivotspinnertablefilter02

Next, I added code to the Spin Button – right-click on it, and click View Code.

pivotspinnertablefilter03

Select the SpinUp and SpinDown procedures, and add two macro names in each procedure.

pivotspinnertablefilter04

The macros will be added to the workbook shortly. These macros change the selected item in a pivot table's report filter, and then change the selected item in the Excel Table's Product column filter.

Create a Pivot Table

Next, create a pivot table, based on the Excel table that you want to filter. You can put the pivot table on the same worksheet, or on a different sheet. In the sample file, the pivot table is on the same sheet, so it's easier to see how the technique works.

The only field in the pivot table is Product, in the Report Filter area. In the screen shot below, you can see all the items in the Product field.

pivotspinnertablefilter05

Add the Pivot Table Macros

Next, you'll add two macros – PivotPageUp and PivotPageDown. The code is in the sample file (on the modPivot module), and on the Report Filter Macros page of my website. Store this code in a regular code module.

  • The code gets the current item number, then adds or subtracts 1, to get the new item number.
  • It shows that item, or shows "All", if the previous item was at the beginning or end of the list.

NOTE: Adjust the macros, if your pivot table is not on the same sheet as the Excel Table.

Add the Change Filter Macro

The final macro is named ChangeFilter, and it  is stored on a regular code module. The code is in the sample file, on the modFilter module.

  • This macro gets the name of the current page in the pivot table's Report Filter.
  • It selects that item in the Excel Table's Product column, or clears the filter, if "All" is selected.

Download the Sample File

To see how the macros scroll through filter items in an Excel Table, download the sample file from my website. In the Download section on the Report Filter Macros page, look for the download named Pivot Spinner Table Filter.

The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file.

________________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *