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. For more info see Dicers
What’s new in Version 2
There are new features and improvements on the original Dicers standard version, and there are are also some great new features available by upgrading to the Pro version.
Dicers Pro is now free – enter this coupon code in your settings to activate
pro-uth-1b3lkd36e
like this
Standard edition
Here’s a quick summary and some screenshots for new features in the standard edition, and a video summary below
Range specification
The previous version had two modes for specifying the data to consider for Dicers. Now you can also specify a specific range.
Scrolling
Now you can specify “allow scrolling” for Dicers. Previously you could not extend the dimensions of Dicers beyond the add-on sidebar. Enabling this feature allows you to extend them beyond those limits.
Leading blank columns and rows
Previously you could not have leading blank rows or columns, and the table would need to start at A1 to be considered if ‘Wholesheet’ was selected in Data Settings.
Now blank headings will be given a name of their A1 notation column (A,B etc..)
Duplicate column names
These are now allowed.
They will automatically be assigned unique names
Pause
You may want to Pause the showing and hiding of rows while you update the sheet to prevent them from being hidden till you are finished. You can use the Pause button for this.
and you can restart filtering when you are ready
Sorting
Previously sorting of numeric values was done on the display format, and would therefore be alphabetic. This means that some numbers and dates would be out of order. Now sorting is done with reference to the underlying real values, so will be in a sensible order irrespective of the display formats applied in the Sheet.
Account information
To manage the Pro version subscription, there is now an account page. Note that no personal information is stored by the add-on. If you are using the standard edition, your account page will look like this. Occasionally you will get a reminder about subscribing to the Pro version, especially if you are a Pro user and your subscription will soon run out.. You can turn this off completely with the Reminders option.
Pro version
The Pro version has a number of unique features, not available in Excel Slicers (upon which Dicers are based). See the video below for the main features
This will take you through a subscription dialog, which uses the Stripe payment system, where you pay a one off fee for 1 years access to Pro features. Your credit card or email address is not stored or accessible by Google Sheets or the Dicers add-on.
Extending
Once subscribed, the UPGRADE page will show the expiration date. You can extend the subscription (add another year) at any time with the EXTEND button.
Your account
Your settings and account are available via these SETTINGS pages
If subscribed, your account will show you account status, and payment reference like this
Using a coupon
Trial subscription is available via a coupon code. This will give access for some period of time. Coupons can be redeemed via the Account tab. If you’d like a trial coupon, contact me.
A coupon gives access to the full set of pro features, but for a time period directed by the coupon code
Enabling pro feature
For convenience you can enable/disable pro features through the Pro Plan Settings page
Ticking this will reset to Standard version behavior
Note on Settings
When you start a new session of Dicers, the settings applied will depend on how you’ve set up the initial settings to be retrieved from via the Save page. Pro settings are no different than any other settings so to enable them to persist across sessions, either for this document or for all your documents, you should Apply the relevant options them via the SAVE page when you make changes to any add-on settings to make them persistent.
Dicer settings
The pro version introduces automatic, sheet level settings. When enabled, any dicers you have set up, including their selected values are maintained so that when you switch between pages, the previous selections for that page are automatically stored. There is also an option for preserving these sheet level settings across sessions, so when you restart the add-on, it either clears or restores its state the last time it was accessed.
Automatic table finding
If you have a sheet with multiple tables on it, or with leading blank columns or rows, and you have selected “wholesheet” from the Data selection page, these blanks will be all be included in the dicers list of values.
Consider a messy sheet like this
In this case, we really want the table with the yellow headings, but when considering the wholesheet, the standard version of Dicers sees these columns.
One way to pick the desired table would be using the specific range option
Which would correctly give this
The Pro version introduces autofind tables, which means you don’t need to bother with that.
As it will automatically find the most likely table on the sheet
Autofind settings
By default, it will find the biggest block of data that looks like a table in a sheet and will tolerate up to 1 blank row in a table to deal with this kind of formatting.
But you can find by position (the n’th block in a sheet) using the mode and rank settings
And you can also modify the tolerable blank columns and rows
to be able to even find tables with this kind of formatting
But the defaults should work for most cases.
New Icons
You’ll notice a number of new Icons associated with each Dicer.
These are greyed out in the Standard edition, but become active when you have a current Pro Subscription.
Color dicers
To select filtering by color, select this icon.
and this happens
Rows with matching colors can be selected, sorted, combined with other value or color dicers just like value selections. Here’s a selection with multiple colors selected. Switching back to value dicers can be done with the icon highlighted.
Or dicers
This example, which has some colors selected in one Dicer, but nothing in another
causes this data to be shown
If a value is selected as well
This is the result
However, if we make the color Dicer into an OR Dicer, using this icon
Then rows with “mars” OR rows with any of the selected color are shown.
Giving this result
You can convert back to an And Dicer with this icon
NOT Dicers
Normally rows that match selected values are shown. However you can change Dicer behaviors to hide (as opposed to show) matching rows with this icon.
So this selection and options
Would show any rows that are not white.
Any combination of Ands, Nots, Colors and Values can be used, but And Dicers are evaluated first.
For more info on standard Dicers features see Dicers. Dicers is available in the Add-ons store
- Chord Snip
- Color Arranger
- Debugging Office JavaScript API add-ins
- Dicers
- 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
- Polyfill for Apps Script properties service for the Office JavaScript API
- Sankey Snip