Dicers

Important note on my add-ons

I've (reluctantly) decided to stop supporting all my Apps Script add-ons, as I no longer have the time or inclination to put them through the various verification processes and upcoming add-on store process changes.

I'll be removing them all from the store in a couple of months.

I realize that many of you may be using some of them. In order to provide some continuity, since they are open source, you are welcome to make your own copy and use them how you wish. Any developers who want to repackage and republish are also welcome to do so.

Here's the repos. You'll also find the link to the scripts if you prefer to take a copy of already built scripts.


https://github.com/brucemcpherson/ChordSnip
https://github.com/brucemcpherson/ColorArranger
https://github.com/brucemcpherson/SankeySnip
https://github.com/brucemcpherson/SlidesMerge
https://github.com/brucemcpherson/Dicers

documentation is here. Anyone can republish this, use the videos etc as required.
http://ramblings.mcpher.com/Home/excelquirks/addons/chordsnip
http://ramblings.mcpher.com/Home/excelquirks/addons/sankeyaddon
http://ramblings.mcpher.com/Home/excelquirks/colorfiesta/colorarranger
http://ramblings.mcpher.com/Home/excelquirks/addons/slidesmerge
http://ramblings.mcpher.com/Home/excelquirks/addons/dicers



Now available on Add-ons store
 
 
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




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 automaticallu 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.

Introductory video



On starting

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.


Removing dicers

Individual dicers can be removed using the garbage icon. Any filtering associated with the removed dicer is removed from the spreadsheet.



Adding dicers

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.


Filtering

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 flipped back to single choice with the single icon

Removing filters


Dicer selections can be cleared with the remove filter icon.

..and all dicer selections can be cleared at once with the clear all button.

Sorting 

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 the normal way. Double click on a dicer to bring it to the front.

Tooltips

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.

Settings

There are a few things that you can tweak in the settings tab, which looks like this.


Dicer settings

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.

Data


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.


Managing settings

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


Save settings

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.


Reset settings

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




Availability

Now available on Add-ons store

Subscription version

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

Comments