Dicers are a visual way of filtering rows on Sheets by selecting from lists of values that are present in selected columns.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.See also Dicers Pro and advanced features
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
- Changes in data are automatically detected and dicers updated
- Dicer values can be automatically sorted
- Multiple filter values can be selected
- Dicers can be resized and rearranged
- Hover for tooltips/labels
- Behavior and appearance can be tailored
- Settings can be saved and applied at document or user level
This is intended to be used with regular sheets. Pivot tables are not yet supported.
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.
Selecting multiple values from the same dicer
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
.. and can be flipped back to single choice with the single icon
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.
Resizing, dragging and bringing to front.
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 a 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 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 is exactly the same pattern as is used in my other add-ons such as Sankey Snip and Chord Snip
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.
In an open document, you can select some different setting by using the Apply settings dialog
Now available on Add-ons store
This standard edition supports all these features mentioned above, but there will also be a premium version available through a small subscription. This will support additional capabilities some of which are shown below
Additional Dicer features – subscription edition
- Dicer selections stored against individual sheets
- The definition of ‘matching’ can be customized to apply a filter to multiple values
- Dicers can be combined using OR and NOT logic
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
- Chord Snip
- Color Arranger
- Dicers Pro and advanced features
- Measure round trip and execution time from add-ons
- Merging slide templates with tabular data
- Office Add-ins – first attempt
- Orchestrating competing google and Office framework loads
- Plotting maps with overlays Sheets add-on starter
- Promise implementation for Apps Script Stripe payments
- Repeatable add-on settings layouts and style
- Sheets API – Developer Metadata
- SlidesMerge add-on
- Unpicking the Google Picker
- Watching for changes in an Office add-in
- When test add-ons doesn’t work
- Sankey Snip