Simulate Slicers in Excel 2003

In Switch Scenarios Dynamically using Slicers, Chandoo shows us how to use Slicers in Excel 2010 to filter data dynamically. Unfortunately, us Excel 2003 (and 2007) users are forced to make due with other solutions.

If you apply AutoFilter to a range of data, you need to click the dropdown arrow to change the filter criteria. However, Excel 2003 has Custom Views which we can use to copy the Slicer feature of Excel 2010. It's not perfect, but it's as close as I could get. If you can do better, leave a comment!

Set Up Toolbars

The first thing I do is add two buttons to my Standard toolbar to make it easier to switch views:

1. Show All – Go to View > Toolbars > Customize, Commands tab, click "Data" in Categories listbox, in Commands listbox drag "Show All" to any toolbar.
2. Custom Views – Go to View > Toolbars > Customize, Commands tab, click "View" in Categories listbox, in Commands listbox drag the "Custom Views" combobox to any toolbar.

The "Show All" button is very useful — it removes filter criteria, showing all the data in a table. If you use AutoFilter a lot, this button is a godsend. It removes any filter criteria without removing the autofilter arrows. (FYI you can do the same thing by going to Data > Filter > Show All.) I love this button!

The "Custom Views" dropdown (which is now on your toolbar, right?) lets us simulate the Slicer feature by allowing us to quickly switch views of our data. Otherwise you have to go to View > Custom Views every time you want to change views, which would defeat the purpose of trying to simulate Slicers!

Set Up Data Range

The next thing I do is set up some data to test out this workaround. I used Chandoo's data and set it up like this:

To make this easy, my data starts in row 1 with a header row, and is the only data on the worksheet. The bottom row uses the SUBTOTAL function to simulate PivotTable (PT) totals without having to create and iterate through PT fields, the way Chandoo does with Slicers.

FYI if you set up a PT from this data you end up with a layout similar to Chandoo:

Download the sample workbook to follow along: Excel 2003 Custom Views for Slicers workaround

Code to set up Custom Views

The following procedure will grab the unique values in column A and create a view for each name.

Sub SetUpViews()

  On Error GoTo ErrorHandler

  Dim currentWkbk As Excel.Workbook
  Dim currentSht As Excel.Worksheet
  Dim rng As Excel.Range
  Dim uniqueItems As Excel.Range
  Dim area As Excel.Range

  Set currentWkbk = ActiveWorkbook
  Set currentSht = ActiveSheet
  Set rng = currentSht.UsedRange

  ' stop screen updating
  Application.ScreenUpdating = False

  ' turn off existing autofilter arrows (just in case)
  currentSht.AutoFilterMode = False

  ' grab filtered rows to get unique list of entries in column A
  ' from http://www.contextures.com/xlautofilter03.html
  With rng
    .AdvancedFilter xlFilterInPlace, , , True
    Set uniqueItems = Range(.Columns(1).Offset(1, 0).Resize(.Columns(1).Rows.Count - 1).SpecialCells(xlCellTypeVisible).Address)
  End With

  ' remove advanced filter
  If currentSht.AutoFilterMode Then
    currentSht.ShowAllData
  End If

  ' -------------------------------------------------------
  ' loop through each autofilter value and add custom view
  ' -------------------------------------------------------
  For Each area In uniqueItems
    If Len(area.Value) > 0 Then ' skip subtotal row, it's blank in column A
      rng.AutoFilter 1, area.Value
      currentWkbk.CustomViews.Add area.Value, True, True
      currentSht.ShowAllData
    End If
  Next area

  ' update screen
  Application.ScreenUpdating = True

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

The first thing we do is apply an Advanced Filter to the range, to get a unique list of names. There is currently only one of each name, but if you insert additional rows and step through the code you'll see the data filtered.

After grabbing the range of unique items, we iterate through each one and use AutoFilter to display only its associated rows. Then we add a custom view based on the name, and repeat until all unique items have their own view.

Once the code stops, check the Custom Views combobox (again, it's on your toolbar, right?) or go to View > Custom Views. You should see entries for each name. Select each name and you'll see the filtered list. Voila! As an added bonus, the SUBTOTAL function will auto-update when you change views, just like a PT would — and a Slicer. Which is why we used it in the first place. 8)

Whenever you want to see the unfiltered data, just click the "Show All" button you added to the toolbar earlier.

Excel 2003 Custom Views for Slicers workaround