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.

Build a Robust and Secure Backup Solution for your Mac 💻

The following article describes how I’ve set up a system for Mac backups that is robust and secure. The importance of doing this correctly came to the fore of my thinking after my brother had his laptop stolen. In the event of a need to restore from a back up you need a system that is robust – you don’t want to find that your backup is corrupted or otherwise of no use right when you need it. Also, in the event of theft you want to be confident that even your backups cannot be used to provide confidential information to the thieves. If you are using FileVault on your internal HDD then you have already made a security-conscious move with your data, therefore having secure backups will be all the more important to you as you don’t want a thief to easily bypass the encryption on your internal HDD by simply restoring from an unsecured backup.  If you’re not using FileVault then you should be – follow this link to find out how to set it up.

I have a MacBook Pro with a 500 GB HDD, so the below is a description of how I have recently established my system for backing up that Mac. In addition to that I have an extensive archive of educational videos that I’ve created and need to store somewhere (approx. 350 GB). My HDD is nowhere near large enough to accommodate those, so my backup solution includes keeping redundant backups of that archive (along with some other files). If you have a different size HDD and different archive requirements you can alter your backup disk sizes accordingly.

The system begins with three external disks. I have a 2.5″ 500 GB portable disk (Toshiba), a 2.5″ 2 TB portable disk (Western Digital, WD) and a 3.5″ 2 TB desktop disk (Hitachi). Some time ago (about 18 months to two years ago) I read an excellent article evaluating HDD’s to find the most reliable brands. Since then the article has been updated so it doesn’t have the same information when I read it but it’s still worth a read. In the article I learned that Hitachi drives are the most reliable (in the storage range they considered). Hence I have one in my system and I advise you to get one too if you can. If that is not possible WD now own the hard disk arm of Hitachi and WD drives faired well in the analysis so WD are a good brand to go with.¹

Whichever brands you settle on, I would advise using at least two different brands and one drive should definitely be a 3.5” desktop drive as 3.5” drives are inherently more reliable than 2.5” drives. In my set up I use the different drives as follows:

  1. The 500 GB 2.5″ drive is set up as a Time Machine backup disk. I take this drive with me to work for frequent Time Machine backups at work and at home.
  2. The 2 TB 3.5″ drive has two partitions. One partition is set up with the well renowned Carbon Copy Cloner, CCC, by Bombich Software. This disk remains at home on my desk to make bootable backups whenever I am at the desk. I also have a 128 GB SanDisk micro SD card permanently mounted on my MacBook for additional storage and CCC backs this up to the 3.5″ drive also. The second partition is used as a standard external hard drive for permanent archiving.
  3. The 2 TB 2.5″ drive is used as an external hard drive with no extra software for making second copies of the archive files stored on the 3.5″ drive.

Once you have your three drives you need to format them correctly. In order to make your backups secure they need to be encrypted. So open up Disk Utility should be the first option when you type “disk” into Spotlight, Alfred, or Launchpad. Alternatively open up the Applications folder in Finder and then locate Disk Utility in the Utilities folder. Formatting the drives is easy if you are starting with blank drives. I wasn’t starting in that position so I had to move files to another drive, format one drive and then transfer files back to it so I could format the other drive. This is time consuming if you have to do it but it is important to get it right.

When you have a drive ready to format connect it to your Mac and it’ll show up in Disk Utility. Make sure that you are viewing devices in the Sidebar and not just the volumes in Disk Utility – select View All Devices in the View menu (so that you get the right formatting option). Select “Erase” as the procedure that you want to do on the device. You are then presented with some options. Choose “GUID Partition Map” as the Scheme you want to use and “Mac OS Extended (Journaled, Encrypted)” as the format. This option means that a password will be required to access anything on the drive. Combined with use of FileVault this provides a secure platform for your computing – extending encryption to your backup. Of course there are people/organisations that could bypass the encryption and access your data but the thief who steals your computer for a quick buck is (probably) not one of them.² Disk Utility will prompt you for the password you’d like to use to encrypt the drive.

Screen Shot 2018-03-05 at 22.24.25
Encrypted Format to use

Once you have correctly formatted and encrypted your drives, select the one that you will use for frequent Time Machine backups and plug it into your Mac. When you plug it in your Mac you will be prompted to enter the password for it – input your password and tick the box to save the password to your keychain. (There should be no security risk by saving your password on your computer because if someone gains access to your computer then what advantage do you have if they do not also access your Time Machine backup?) Your Mac should then show a dialog window asking if you’d like to use that disk to make a Time Machine backup. Click “Use as Backup Disk”, then follow the instructions and Time Machine will start making backups.

IMG_0060.png
This should pop up when your drive is mounted

I think it is wise to use different software to Apple’s Time Machine for the backup on a second disk. Time Machine is an excellent application but you’ll never know about a bug until you need to restore from a backup by which time it is too late. My recommendation is to use an excellent app called Carbon Copy Cloner (CCC) made by Bombich Software. CCC backups have a feature that sets them above Time Machine backups in my opinion – they are bootable.³ In the event that your internal hard drive gets fried you can boot up straightaway from your CCC backup. With this set up you are preempting bugs from ruining backups in either Time Machine or CCC and you benefit from the unique features of both TM and CCC backups.

If you are following my setup for assigning disks to backup function then this second disk will be your large capacity desktop hard disk. In that case partition the drive so that you have about 700 GB available for the CCC backup on one partition and the rest can be used for archiving large files so they’re not taking up space on your internal drive.

How to partition a hard disk

1. Mount the disk on your Mac and launch Disk Utility.
2. Set the view to Show All Devices (press ⌘2 or use the View menu).
IMG_0062
3. Select the “device” for your hard drive in the sidebar. Don’t select the “volume” (volumes are displayed under their device)IMG_0063.png
4. Then click Partition in the toolbar.
5. Click the “Plus” button at the bottom of the window to add a new partition.
Screen Shot 2018-03-05 at 22.31.33.png
6. Resize the partition you will use for backups to be a bit larger than the internal hard disk you are going to backup.
For instance for my 500GB internal drive I created a 700 GB partition.
7. Ensure that archive partition has the encrypted format in the Format field.  The partition for the encrypted CCC backup needs to be set up differently.  So follow Bombich’s instructions on how to set up an encrypted backup here
8. Click Apply and Disk Utility will partition the drive.

The third disk is used for creating a redundant archive of your large files. Do this so that if your desktop hard disk should fail you still have copies to fall back on. When you copy files to the desktop disk make sure you also copy them to the third disk and vice versa.

At this point you have now established a back up solution for your Mac and any large files that is robust (if one device or app fails you’re still covered) and secure (even if your backup disk is stolen here is still an encryption barrier to keep that data safe).

If you’ve followed my scheme up until how I hope you find it to be a useful solution for your backup needs.  If you have any comments or suggestions add them below or on Google Plus, alternatively you can email me at danwilsonav@gmail.com.

Update 16/3/2108

Some feedback I received suggested that a flow diagram would help readers better understand the backup solution I’ve described above. So here is the diagram:


Footnotes
¹ In the original article someone added a comment with additional information looking at failure rates over time. They noted that if WD drives failed they tended to fail almost as soon as you got the drive. That suggested that WD drives are most likely reliably built but poorly shipped. So if you manage to get your WD drive running after purchase it is likely going to last well. And of course that may have changed since the original article publish date – hopefully for the better!

² There probably are people or organisations who have the resources and knowledge to crack encrypted drives or bypass the encryption but the question is do they have the motivation to crack the encryption on your drive? Cracking into encrypted drives is possible but very difficult, time consuming and therefore expensive. Unless you are a person of significant influence or a known terrorist you can probably be confident that your data will be safe. Keeping data secure is really about using methods that make it impossible for the average person or even a skilled person to access your data.

³ A further advantage of CCC over Time Machine is the ability to back up multiple hard drives. Time Machine can only back up your internal hard drive (as far as I can see). CCC can make backups of external hard drives that you mount on your Mac. In my case this proves to be extremely useful for making backups of a 128 GB micro SD card that I have continuously mounted to my Mac. Find out more about using micro SD cards for continuously mounted storage expansion here.

Jazz Up Your iPhone Lock Screen

You can now buy a cool animation for your iPhone* Lock Screen right here on this blog: https://wp.me/P4d3tP-hj

The first 50 people to use this promo code (FIRST-50-FREE-N) will get one Live Wallpaper of their choice for FREE.  To get your free animation go to the page (above) and fill out the order form for one Live Wallpaper, insert the promo code instead of the PayPal receipt number.

A small selection of animations are shown below. See them all on the purchase page above.

Blizzard
Premier League Tables 2013-2017
Rotating Globe 1
Popping Dots – Wave

 

 

 

 

 

 

 

 

 

 

* These animations only work on 3D Touch enabled iPhones.  Animations are activated by 3D Touching the Lock Screen.

Cool Live Wallpapers for iPhones

A couple of months ago I designed some Live Wallpapers with science themes.  Friends enjoyed the concept of the animated lock screens but not everyone understood the content.  So It occurred to me that perhaps some new animations with a non-science theme would go down well with a broader audience.

Since then I’ve designed several new animated wallpapers for iPhone Lock Screens.  You can see a preview of them in the video below.  If these do appeal to you complete my short survey to receive a Live Wallpaper for free – I want to know if there is a market for these!

snow drift (short) GIF
Snow Drift (short) Live Wallpaper
(Low Res GIF )

 

High Sierra now updated to version 13.1


Perhaps you’ve been delaying upgrading to High Sierra until some bugs are worked out. Or maybe, like me, waiting until APFS has got some real world mileage before jumping in. I was waiting particularly on the advice of Bombich Software who pointed out the real lack of knowledge and documentation about APFS.  Their advice was to wait until 13.1 was released. 

13.1 has now been released so I am going for it (with a couple of backups at the ready to be on the safe side).

A Problem with Wireless Charging

At first I was rather taken with the concept of wireless charging, and I still am to a certain extent. But it is now mixed with some skepticism. 

When the iPhone 8 was released and wireless charging was a key feature I thought that was a great feature to include.  The design of the phone is fantastic by the way.  But having thought about actually getting a wireless charging pad more I am not so sure about it’s greatness.  Currently when my phone is plugged in I will often still use my phone and I am sure a very high proportion of smartphone owners do the same (95% of the people I see charging their phones on a daily basis do this – the exception being when I charge my phone overnight).  

When your phone is plugged in to a cable that moves with your phone this is fine.  But when your body has to do all the moving because your phone must stay in a fixed location use of phone whilst charging becomes very inconvenient.  If you use a charging dock you’ll know what I mean.  A wireless pad gives a little more flexibility but the phone still needs to remain within a few cm of the pad to charge at all and probably within one cm for efficient energy transfer. 

So now I am unsure of how beneficial a wireless charger will be.  How about you?  Do you have one and can you share your experience or have you decided against it for another reason?  Either way please share your opinion in the comments here or on Google Plus.