The Checkbox (Tick box) Has Arrived on Sheets

After a wait of several days since being announced in the latest update the checkbox has made it to Google Sheets.  NB in the UK a checkbox is called a tick box, so in the screenshots below you will see tick box instead.

So far the implementation seems to be a solid new feature.  To insert checkboxes simply select the cell(s) where you want a checkbox, then insert them from the Insert menu.

Screen Shot 2018-04-17 at 14.43.33

When a checkbox is unticked the cell’s value becomes FALSE.  This is the default value after adding checkboxes to cells.  When the cell is checked it becomes TRUE.

Screen Shot 2018-04-17 at 14.44.26

But checkboxes are not restricted to returning TRUE/FALSE, they can return numbers or even custom strings.  In order to do that you do so via the Data Validation menu.  Make sure the cells you want these custom checkboxes inserted into are empty; i.e. do not insert checkboxes via the Insert menu first.  From the Data menu select Data Validation.  Then a pop over window appears.  In the Criteria field choose Checkbox.

This slideshow requires JavaScript.

To customise the values for checked and unchecked tick the option for “Use custom cell values”.  Then below two new text fields will appear.  For the values you could use 0 and 1 as the unchecked and checked values respectively (or the reverse if you desire).  Or they could be “Yes” and “No” or even “foo” and “bar”, etc.

This slideshow requires JavaScript.

If you insert checkboxes by this method then here is something to watch out for.  When the checkboxes are inserted they are inserted as unchecked just as they are for the method of insertion using the Insert menu.  However, unlike that method the cell values are not updated to what you set them in the data validation window.  That is, when you insert standard TRUE/FALSE checkboxes with the Insert menu all the cells get the value of FALSE.  But when you insert checkboxes with Data Validation the cell values remain blank until they are checked.  For example, if I used “No” as the value for unchecked then after I Save the data validation the cell values will not be changed to “No”.  In order to set the cell values to “No” they need to be manually checked and unchecked (Google may change this behaviour in the future).

Select a range of cells with checkboxes and toggle them with the space bar

Speaking of which, manually checking and unchecking large numbers of checkboxes, you can of course select a range of cells with checkboxes and toggle them all at once with the space bar.  So should you be using custom checkboxes via data validation you can fairly quickly set them to the custom unchecked value you set.

On mobile (iOS at least) you cannot add new checkboxes but you can interact with any you insert on desktop.  You can copy and paste existing checkboxes, so if you have a spreadsheet with checkboxes already set up and want to extend the range you can do it on mobile – this is true even of checkboxes with custom values.

In my opinion checkboxes on Sheets will prove to be a valuable feature, what are your thoughts – comment below or on Google+ or Twitter?

tick boxes on Sheets iOS app
You can toggle and copy and paste existing checkboxes on mobile apps

 

Advertisements

Update for Google Sheets brings Macros and Checkboxes (not fully rolled out yet)

Google announced an update for Google Sheets on 11 April.  The new features look very exciting with macros being the headline feature.  Macros enable you to record a series of edits to a Google Sheet that you will repeat and then save them in a menu to replay anytime to automate that series of actions.  It even generates a script that you can edit without having to re-record the macro.  This will eliminate a significant amount of tedium for big users of Sheets.  Click the link above to read Google’s update about this and to watch their demo.  Currently this is available on G Suite accounts but not regular Google Drive accounts.

Whilst macros were the headline feature, the one I am most excited about is checkboxes.  Checkboxes are a significant reason that I still use Apple’s Numbers for some of my spreadsheets.  Being able to tick items off in a spreadsheet is, in my opinion, a much more efficient user experience than having to use a drop down menu.  I am excited about this but as of yet the feature hasn’t rolled out to users as far as I can see – I can’t access them in my regular Google Drive account nor in my G Suite account.  Fingers crossed that it comes soon and is a solid implementation.

What are your thoughts about this update?  Do you make/use a lot of spreadsheets?  Will these and the other new features tempt you away from Excel and/or Numbers to Google Sheets?  Share your thoughts in the comments here, Google Plus or Twitter.

Track the Important Numbers in Your Life : How to set up a Number Dashboard (#Dashboard) in Google Sheets

Number Dashboard Screenshot
Number dashboards (#dashboards) can be used to track numbers that are important to you.  They are built on Google Sheets but can be easily and quickly viewed on mobile browsers.  If you already have numbers calculated and stored in other Google Sheets you can easily copy a #dashboard into existing spreadsheets and plug in your numbers.  Or use standalone #dashboards to hook into multiple spreadsheets in a straightforward workflow and collate your numbers in a single location.

 

 

In the latest iteration of #dashboards I’ve made a small collection of background images that can be included in your #dashboard.  These images are designed to be used dynamically.  As your numbers change so does the image to reflect that change.  For example, say you are counting down to a target date, as you approach the date a circle outline can progressively be filled in.  Or say your crypto investment is on the up, then an upward graph can be displayed, or a downward graph if things aren’t going so well.  The choice is yours.  In most cases you will need to design your own spreadsheet logic to incorporate the images as you need them, but I have simplified the procedure for incorporating countdown and count up images.
Number Dashboard Images Screenshot
The following will walk you through the process of setting up your very own #dashboard.

Method 1.  Set up a#dashboard to collate numbers from multiple other Google Sheets.

  1. Make a copy of my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. On the Data tab give your number a name, e.g. “Fuel Range”, in the relevant row.
    Number Dashboard Data tab Screenshot.png
  3. Copy the spreadsheet key from the spreadsheet that hosts your number and paste it into the cell on the Data tab.
    Spreadsheet key is the string of random alpha numerals in the spreadsheet URL.  The key for the above spreadsheet is 1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw
  4. Write out the address of the cell.  Cell Address is the name of the sheet (or tab) in the spreadsheet followed by an ! then the coordinates of the cell using column letter and row number, e.g. SheetName!B3
  5. The spreadsheet will then proceed to access the number.  But before it can display it you need to authorise the connection to the spreadsheet by hovering over the #REF error message that appears and clicking “Allow access”.
    You are only allowing the copy of the #dashboard that you created and own to access the data so you are ok here on a privacy front.
  6. Give a unit to your number if appropriate.
  7. Find an image on the web that you want to use to represent your number.  Copy the image address and paste it in.
  8. Everything else is optional: you can add a further written description, a link (to a graph or more information), and include a dynamic image (more on that below).
  9. On the Data tab you can add a link to an image that will display at the top of the #dashboard.  I find that a nice way to differentiate between my various #dashboards.  If you don’t want one, ignore that and shrink the row on the dashboard.
  10. Once you’ve added in your numbers publish the #dashboard to the web.
    File > Publish to the web > Change “Entire document” to “Dashboard”
    Also expand “Published content and settings” and change “Entire document” to “Dashboard”
  11. Copy the URL and paste it in the web browser ➝ that is your dashboard.
  12. One last bit of tidying up: add “&chrome=false” to the end of the URL and go there.  Much nicer hey?
  13. Send that URL to your phone and bookmark it or add it to your home screen for quick access.

Method 2.  Add a #dashboard to an existing Google Sheet.

  1. Open my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. Right click the Data tab.  Choose “Copy to …”
  3. Search for and select the Google Sheet you want to add the #dashboard into.
  4. Open that Sheet and rename the tab you copied back to “Data”.
  5. Now repeat steps 2 ➝ 3 for the Dashboard tab.
    (If you want to use the images in the Images tab then you’ll also need to copy that tab into the new Sheet too.)
  6. Now the dashboard lives in your spreadsheet.
  7. To add a number to the #dashboard simply overwrite the formula in the value column and make it point to the cell where the number is, e.g. type”=“ and navigate to the cell or type something like this “=sheetName!C3”.
  8. Type in all the details as in steps 2, 6 ➝ 9 under Method 1 above.
  9. To publish the #dashboard follow steps 10➝ 12 under Method 1 above.

Add a dynamic image to your dashboard.

  1. I have a number that is going to count up to a target value.  I want to use that fantastic circle to fill in as I approach the target!
  2. Type “Up” in the column headed: “Image counts up or down to target value?”
  3. Choose Circles from drop down menu.
  4. Type in the value to start counting from.  Usually this is the value as it is now, or 0.
  5. Then type the target value.
  6. Now the #dashboard knows to ignore the image link (if there is one) and use the dynamic circle image.
  7. The image appears automatically in column on the far right.
  8. There are ten chunks of the circle that get added as the target is approached.
    Screen Shot 2017-08-07 at 16.00.45
  9. If you are comfortable typing logical statements into spreadsheets then you can design formulae to add dynamic emoji or graphs to a number.  I included an example for you called : “Investment Gain / Loss”.  That number doesn’t change automatically so feel free so to play around with the value and see the image change.
I hope that you like the #dashboards and that they provide a useful service for you.  If you need any help setting yours up then feel free to comment below or contact me on Twitter: https://twitter.com/rapidreportsHQ

Inbox by Google : Great and Annoying

I like many aspects of Inbox on iOS. It is Google’s own app so it can presumably harness the email data in ways that third party apps cannot, or at least the direct access it has to email data must give it a reliability and speed edge. Snooze to location is good. Built in actionable reminders. Google’s summary cards for standard emails. These are all great things, however the app does has some very annoying limitations.
1. No text formatting options. Five years ago this wouldn’t have been problem as most people would have been happy to be able to email whilst mobile. But these days it should be possible to add bold, italics and underline to your emails. This is all the more problematic given that even WhatsApp and Slack allow for some formatting – WhatsApp only handles bold but Slack has a range of options. If instant messengers can offer text formatting an email app absolutely should be able to do so. At times Inbox has had support for text formatting, but it is there one update and gone the next. Presently it is available, but who knows for how long?

2. No file attachments outside of Photos / Drive. Sure you can browse your file storage app and attach from there, but is it really too hard for Google to add a proper file picker? Furthermore this is no good for replying to emails; you have to start a new thread (not helpful).

3. Lack of draft saving from share sheet. When sharing files from other apps to email from Inbox, if the email fails to send or you stop composing the email you lose the email. Whereas when sharing into other apps (i.e. Spark or Outlook) the file is opened in the email app itself and attached to a new email. If you then stop composing the email you are given the option to save a draft. Inbox’s behaviour is nice in that it doesn’t switch you into the Inbox app; it overlays Inbox’s interface into the app you are sharing from. But as outlined above it is annoying if you start and email and aren’t able to complete it in one go as you’ll lose the entire email. A common problem here is that if you start composing from the share window and then need to check something in the document you’re trying to share (or another document in the same app for that matter) then there is no way to open the document without closing and losing your email. 

4. No printing! If you want to print emails you’ll need a different app. Even attachments can’t be printed unless they are a PDF. I don’t often need to print emails but when I occasionally do this is a major limitation. More often it is the need to turn an email into a PDF that I notice this lack of feature. In iOS you can turn any document / file into a PDF if it can be printed (find out how here). So the fact that I cannot print an email means that I cannot turn it into a PDF.
I hope that these lack of features will be addressed soon. Does anyone have thoughts about their favourite email app?

Google’s Rapid 2 Step Authentication Option

Google Prompt is a fast second step authentication option on iOS and Android.  Whenever a login attempt is made on your Google account Google Prompt sends a login confirmation to the Google app on your  phone.  You open the app (via push notification) and tap “Yes” to confirm a login attempt is valid (or “No” to deny a login attempt).
This login method is quite a bit faster than using an authenticator app.  I use Authy which even with its widget in the notification centre takes some time to copy the number into the field.  I like Google Prompt for its speed and simplicity.  The slight downside is that it requires your phone to have a live internet connection to use.  This is only a slight downside because if you are signing into a Google service somewhere chances are that you have an internet connection available; with some exceptions.  But Google Prompt works parallel to the other second step options available including an authenticator app, so in the absence of an internet connection on your phone you still have offline options to fall back on.  Head into your Google 2 step verification settings to set it up.
Google 2 Step Authentication Options.png
Google 2 Step Verification Options

 

From a security perspective it is hard for me to say whether this is a better or worse method than an authenticator app.  I understand the methodology behind authenticator apps, but not this one.  That said, I trust Google with my information and I trust them to have built a reliable and safe second step with Google Prompt.  Furthermore I trust that they will be on the ball enough to keep it safe.  I say this because of the numerous articles and security updates that are the result of contributions that Google has made to tech security worldwide.
In summary Google Prompt is a system I am prepared to trust and it makes my login process a great deal more streamlined whilst maintaining its integrity.  I recommend this for anyone wanting the benefits of 2 step authentication with a bit of a faster workflow.

Are You Planning to See the Total Eclipse of 2017?

On 21 August 2017 a Total Eclipse will travel across the USA from West Coast to East – right across!

The eclipse will hit the West Coast just after 10:15 AM PDT, it will then reach the East Coast about 90 minutes later at just gone 2:45 pm EDT.

A great website with loads of information about the eclipse can be found here: Great American Eclipse.  They’ve got maps covering a wide range of eclipse information, videos, eclipse viewing glasses for sale, and lots of information about viewing the eclipse.  The maps available on Great American Eclipse are fantastically detailed – make sure you download the high resolution computer wallpaper.  If you are able to make it to view the eclipse you will certainly find their resources very helpful.

If you are interested in seeing the path overlaid on Google Maps I have added it and you can access it with this link: http://bit.ly/eclipsemap17.  Also embedded below.  Now you will be able to navigate there and confirm you are indeed in the path of the oncoming eclipse right in your Google Maps app.*

I really hope I can make it out to the States to see this, what about you?

 


*Please note that there will undoubtedly be some inaccuracies in the data displayed on my overlay of the map.  So to be certain of being right in the path of the eclipse it would be advisable to move a reasonable distance inside the path and not stay on the very edges as denoted by the black lines.

Act fast to show your support for Numerous

Numerous has been on my blog to do list for some time now.  That is, to write about how great it is – especially in conjunction with Google Forms and Sheets.  It has proven to be an incredibly functional app for me and for many others.  But they recently announced their imminent closure due to being unable to become self sufficient.

However, the are many people who find Numerous incredibly useful and yet it is a free app and service.  Perhaps there are enough of us out there willing to pay to keep Numerous going.*  If you love / rely on / appreciate Numerous take a moment to show your support by filling out this form and then follow the result on Numerous.  This method of course showcases how cool Numerous is: Google Sheets picks up input to the form and Numerous hooks into the sheet to display a number summarising the results.  Multiple cells on the spreadsheet can be linked to from Numerous so you can display multiple results.  For example, with this poll I am taking here I can display (1) how many people are willing to pay for Numerous, (2) on average how much they are willing to pay and (3) the most popular monthly fee for Numerous.

https://nmrs.co/e/slfry9rmymap

* I appreciate that getting enough revenue might in itself not be enough to keep Numerous going, but maybe just maybe it is.

UPDATE 13/2/16: 

For a more detailed analysis of responses check out the published results here.

Here are a couple of other numbers on Numerous to track results of the poll:

Average Numerous Fee
The average fee that users are willing to pay
Most popular fee
The most popular monthly fee for Numerous

Update 3/3/16:

Tweet from @NumerousApp :

@nadnosliw We’ve heard from dozens of people, which is very gratifying, but it needs to be more like 10,000 or 100,000…

Scaled Conditional Formatting Arrives in Google Sheets

Good news spreadsheet people: you can now use colour scales in the conditional formatting in Google Sheets.  So now you can get a quick visual representation of a group comparison.  As a teacher I occasionally need to rank student achievement across a whole class / cohort (the more common and important analyses are those against an individual student’s own benchmarks).  So this newly added feature should prove useful for my grade book.

G Sheets Conditional Formatting - scales