Cross-Worksheet Filters

by Charlie Daffern

So you’ve got a worksheet together, all formatted and ready to go with a nicely set-up filter. Maybe the filter is fairly complex and you’ve used it to exclude particular variables from your view, or perhaps you’re just allowing the user to choose to see only what they’re interested in.

Either way, the key here is that you want to apply the filter to different worksheets without going through the set-up all over again. For instance…

…a scatter plot of each gymnast’s scores on the various apparatus. In this example I’m going to filter by gymnast, to allow the user to see the distribution of each athlete’s scores across the various apparatus. To do this we first need to head back over to the worksheet with our original filter on.

If you right click on the relevant pill in the filter shelf, a drop-down menu will appear. By navigating to the ‘Apply to Worksheets’ option, you’ll see that this is automatically set to ‘Only This Worksheet’. But there are other options!

The one we want to use in this case is ‘Selected Worksheets…’

Note: I’ve chosen ‘Selected Worksheets’ because I have other worksheets in this workbook using the same data source, where I’m analysing the data differently – by apparatus, rather than by gymnast. If you wanted it to be consistent across sheets using the same data source, or all related data sources, you could select one of the other two options in the drop-down menu.

This opens a pop-up option menu, where we can multiple-select the worksheets that we want this to apply to. I’ve selected ‘Diff/Exec Rank’ which is the name of the worksheet containing the scatter plot that I’m looking to apply the filter to.

Once you hit OK you’ll see that the pill on the filter shelf gets a little icon next to it, illustrating that the filter is applying to other worksheets. Let’s take a look back at the scatter plot worksheet…

Voila – this worksheet now has the same filter pill, with the same cross-worksheet icon, and the number of marks is reduced to only David Belyavskiy’s scores. If sheets using the same filter are added into a dashboard, a single filter can be used across the two without the need for dashboard actions.

Above, we see the filter for David Belyavskiy. Below, the filter is applied for Lin Chaopan.

Note that no filtering is applied to the chart in the top right, as this was not selected in the ‘Apply Filter to Worksheets’ window.

Avatar

Charlie Daffern

Fri 15 Dec 2017

Fri 15 Dec 2017

Tue 12 Dec 2017