Now available on Add-ons store
The Dicers add-on provides a simple and visual way of applying filters to data in Google Sheets. They are similar in function and appearance to Table Slicers in Excel. Any column in a sheet can become a Dicer, just by adding it, and it will show the values in column. Selecting that value will filter the data in the sheet according to the values selected in each of the dicers.
Dicer features - standard edition
Here's the Add-on launching menu item
On initialization, Dicers automatically identifies the data on the active sheet and builds a few dicers in the sidebar, which looks like this.
Individual dicers can be removed using the garbage icon. Any filtering associated with the removed dicer is removed from the spreadsheet.
New dicers can be added to the sidebar using the Add button
..which brings up each of the columns in the active sheet. Selecting/Deselecting a column adds or removes the associated dicer.
Each dicer shows the unique values in its column. You can filter the active sheet by selecting a value in one or more dicers. This example shows 2 dicers with values selected, and the sheet will hide all rows that do not match these filters.
Multiple selections are enabled with the multiple icon on each dicer
Now when you select more than one value from the dicer with multi-select enabled then rows in the sheet are shown that match any of the selected values as in this example
Dicer selections can be cleared with the remove filter icon.
..and all dicer selections can be cleared at once with the clear all button.
By default, the unique values in a column are sorted in the dicer. You can flip from ascending to descending with the sort icons.
In order to fit a number of them in the sidebar, dicers are staggered and overlap. However they can be dragged around and resized in the normal way. Double click on a dicer to bring it to the front.
Just like Excel slicers, each icon and value has a tooltip which will be shown if you hover over it. This allows you to see the purpose of icons, or the full value of an item if it's too long to show in the space available.
There are a few things that you can tweak in the settings tab, which looks like this.
These control the initial settings of the dicer, and how the dicers are initially laid out in the sidebar.
You can change the colors for each of the dicer states if you wish.
Dicers automatically detects changes in the data. If you delete a column then it's dicer is also removed if it exists. The contents of each dicer is updated to reflect the latest values in the sheet. If you move sheets, and the columns are the same as the sheet you've moved from, the original dicers and selections are maintained, but if the columns are different, then they are eliminated and new ones built
If you move to a blank sheet, then you'll see this message in place of a set of dicers. This will allow you to generate some test data to play around with the add-on if you wish. If instead you start to create data in the sheet, then Dicers will notice and replace the message with dicers reflecting your data.
Normally the entire sheet is treated as a the target table, but you can use a selected area in the data tab, in which case only the currently selected area is used.
You'll notice that each settings page has an apply and a back button. This allows you to undo any changes you've made while on this settings page. Apply will be enabled if you have made any. Any changes made on the page are immediately applied to the chart preview so you can flip over to see what it looks like. To keep them hit Apply, to get abandon them use Back.
In addition, Dicers has a comprehensive way of making settings permanent so you can re-use them if you have a house or document style you want to follow, or for restoring complete settings as they were at the beginning of the session, or to the default values for the dicers.
This dialog allows you to retain the current settings so that they will apply each time you open this document, or to every document where you use Dicers. Clear these settings will cause Dicers to revert to it's normal default values in this and other documents.
Dicers follows this pattern when deciding which settings to use when opening, using the first settings collection that exists.
Now available on Add-ons store
Additional Dicer features - subscription edition
If you would like to provide feedback on the kind of additional features you'd like to see in a subscription version, you can do so through this form
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Add-ons >