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.
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.
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.
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.
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?
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.
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.
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.
On the Data tab give your number a name, e.g. “Fuel Range”, in the relevant row.
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
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
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.
Give a unit to your number if appropriate.
Find an image on the web that you want to use to represent your number. Copy the image address and paste it in.
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).
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.
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”
Copy the URL and paste it in the web browser ➝ that is your dashboard.
One last bit of tidying up: add “&chrome=false” to the end of the URL and go there. Much nicer hey?
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.
Search for and select the Google Sheet you want to add the #dashboard into.
Open that Sheet and rename the tab you copied back to “Data”.
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.)
Now the dashboard lives in your spreadsheet.
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”.
Type in all the details as in steps 2, 6 ➝ 9 under Method 1 above.
To publish the #dashboard follow steps 10➝ 12 under Method 1 above.
Add a dynamic image to your dashboard.
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!
Type “Up” in the column headed: “Image counts up or down to target value?”
Choose Circles from drop down menu.
Type in the value to start counting from. Usually this is the value as it is now, or 0.
Then type the target value.
Now the #dashboard knows to ignore the image link (if there is one) and use the dynamic circle image.
The image appears automatically in column on the far right.
There are ten chunks of the circle that get added as the target is approached.
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
Since the announcement that the fantastic app Numerous would cease to be on the 1 May I and many other Numerous users have been wanting to find something that could replace it. The ever so slightly quirky app excelled at displaying important numbers in a clean and clear way that helped us keep track of numbers that meant something to us. Some people, like me, started out dubious that we would find much use for the app. But it quickly proved its worth. It will leave a big dent in the routines of many people when it shuts down. Well done Numerous team you made something truly excellent and many, many of us will be sad to see you go.
Despite the best efforts of many nothing has been found that can adequately replace Numerous. So as the deadline for Numerous’ end drew nearer I put my mind to work about how I could hack together something to simply display useful numbers. The rest of this post is about a spreadsheet (Google Sheet) I’ve designed that can do just that.
Let me start by outlining the main aspects of functionality of Numerous that this spreadsheet does not replace and its limitations in comparison to Numerous.
1. It can only display numbers that are already in a Google Sheet (this might change in the future but the procedure won’t be as straight forward).
2. There is no social aspect. You can’t browse a catalogue of numbers and individually select numbers from a community to follow. But if you have friends tracking numbers in Google Sheets you can give each other access to each other’s spreadsheets on a case by case basis.
3. All numbers are technically public; no private numbers.
4. No value history or graph.
But, that said, a potential benefit to some people will be the ability to display dynamic text and not numbers only.
Now onto how to use the spreadsheet for your own numbers. You must first appreciate the nature of this spreadsheet. It doesn’t actually do any computation of your numbers, it simply hooks into the spreadsheets you already have to display those numbers in a way much like Numerous: clear and nicely contextualised. Also it will display those numbers in a web browser so you can get faster access to your numbers wihtout having to search through your spreadsheet directories.
The spreadsheet is published here: Number Dashboard. Follow the link to the spreadsheet and save a copy to your Google Drive so that you can edit your own copy.
In the spreadsheet there are two sheets: “Dashboard” and “Data”. Dashboard displays your numbers and Data is where you input information so that the spreadsheet can collect your numbers from your other spreadsheets.
Each Google Sheet has a unique URL. In the URL is a unique spreadsheet key. You will need to copy the keys of the spreadsheets that have any numbers you want displayed in the Dashboard. Below is a screenshot of a Google Sheet URL with the spreadsheet key indicated. The format of the URL is: https://docs.google.com/spreadsheets/d/spreadsheet key/edit#gid…
Paste the key into the relevant column in the Data sheet. Next you need to input the address of the cell containing the number you want to display. For example, if I had a spreadsheet with a sheet called “Fuel Log” and the car mileage was in cell “D5” then the address I would need to write in the Data sheet would be: Fuel Log!D5. Include all spaces as spaces and you don’t need to include any extra quotation marks.
Once you do this the spreadsheet will begin the process of trying to look up the value. But you will need to give permission to link the spreadsheet with the number with the Number Dashboard. To do that hover the mouse over the cell in column E that displays the #REF! error. Then a pop over wil show that has a button that says “Allow access”, click that and the value will display.
In the table in the Data sheet don’t overwrite anything in columns A, E or H. The value and image are automatically inserted based on the information you put in (instructions included below for inserting your own images). For best results use square images. Everything else in the table in the Data sheet should be pretty self explanatory. Annotated screenshot below.
All your numbers by now will show up in the Dashboard sheet. The next stage to get your numbers accessible without having to open up a spreadsheet app and then navigating to the spreadsheet is to publish the Dashboard to the web. I’ve included some annotated screenshots below showing the steps to do this. At the end of the (very short) process you will be provided with a link to the published Dashboard. Bookmark the link, or even better save it to your smartphone homescreen. Now your numbers are accessible with a tap from the homescreen.
Other features / limitations:
1. Inserting your own images. To insert an image you need it to be available on the internet. If you want to use your own image you need to insert it into a website or cloud storage and copy the link to the image, then paste that link into the Data sheet. I have used Google Photos for my images. I upload the image to G Photos, then navigate to view the image there and right click it to copy the image address.
2. If you know your way around Google Sheets you can customise info in the Data sheet further. For example you can have a dynamic name for a number. In my Data table I link to a number that counts down to the next school holiday. But also on that spreadsheet there is a cell that displays the name of the next holiday (e.g. “May Half Term” or “Christmas Holidays”). So I used the IMPORTRANGE() function in the Data table to reference that cell and have a dynamic name for that number.
2. Countdowns are/were a great feature of Numerous. They can be created in Google Sheets. But remember that this spreadsheet just displays your numbers. You will need a standalone spreadsheet to do the countdowns. Lucky for you I have one, you can get it here. My one is designed for counting down to college / school holidays. So when one holiday is reached it automatically updates to the next holiday. But it can be used to track just one date too.
3. Customising the size to fit your smartphone. If you find that the published dashboard is not the right size for your smartphone then open the spreadsheet in Google Sheets and resize the columns on Dashbaord so that you get a suitable fit. Note that it takes several minutes for the display to update so be patient as you see what works for you.
4. The layout is fixed. Numerous would give you a different view in landscape and portrait; that won’t happen with this spreadsheet.
This is clearly an inferior setup compared to Numerous, but I do hope that it will be of some use in Numerous’ absence. If you have any questions or tips to share please leave a comment here or on Google Plus or reach me on Twitter.
If you are designing a table in html there is a lot of tedious repetitive typing to do. A spreadsheet can be used to take a lot of the tedium out of the process. Previoulsy I have used spreadsheets to generate repetitive text and html in what I think was a relatively efficient way. I also thought that it was a very quirky method unlikley to appeal to other people. Until, that was, I read this blog post on the Zapier blog. Then I realised perhaps my quirky method might be of some interest to the mark up community.
So here is my contribution, it builds on Matthew’s post on the Zapier blog by showing how I would use Google Sheets to build a html table. The method is different to Matthew’s as it avoids the very long concatenate formula and makes it easier to make quick edits to the table formatting. It is more complex to set up, but much easier to work with once it’s done.
What follows are the steps to make the key parts of a html generating spreadsheet from scratch, but I have published a spreadsheet here which is based on the table that Matthew shared in his blog post. You can download and tweak it for your own use.
Step 1. Create a spreadsheet
Create the spreadsheet with two sheets: “Templates & Output” and “Row Details”
Step 2. Set up your templates in the “Templates & Output” sheet
In one cell set up a template of the html for the whole table, using a text placeholder (e.g. [rows]) for where you want the rows to go. I put my placeholders in square brackets but you can customise this to your own preferred style, just make sure they won’t be mistaken for html.
In another cell set up a template for the table rows, using text placeholders (e.g. [Name], [Description], [Link] etc) for the specific details of each row.
Step 3. Make a table for the unique details in the Row Details Sheet
Make a table with a header row, the header items are the placeholders in the templates. Each row of the table here will provide the unique details for each row in the html table. So if you were producing a table with details about a range of apps, each row will include the details of one app.
Step 4. Write a formula to replace the placeholders in the row template
In a column to the right of all the details write a formula using SUBSTITUTE(). This formula requires three arguments SUBSTITUTE(arg1,arg2,arg3).
* For argument one point to the template for table rows, this will form the base text of SUBSTITUTE(). This reference should be absolute because the layout of all rows is based on the same template (“A1” is a relative cell reference and “$A$1” is an absolute cell reference).
* Then use the formula to replace a placeholder with the detail from the table. Argument two should point to the placeholder text, i.e. the header of the table column. This argument should use an absolute row reference (“B$1” for example) because the placeholder text is a constant for all rows in the table.
* Argument three points to the detail in the table that the placeholder should be replaced with (e.g. “B2”).
Nest multiple SUBSTITUTE() formulae for as many placeholders as you have. If you had five unique details to place into your template for each row then you would need five nested SUBSTITUTE() formulae. E.g. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,B$1,B2),C$1,C2),D$1,D2),E$1,E2),F$1,F2)
Where the absolute row references point to the headers of the table. Fill that formula down the length of the table.
Step 5. Concatenate the html for all rows
In one cell, can be anywhere I put mine in the Templates & Output sheet, concatenate all the html that your spreadsheet has generated for the rows. You can manually write a CONCATENATE() formula, or use ampersands “&”, or my favourite is to use JOIN() with an OFFSET() formula. The beauty of JOIN() is twofold:
1. I can easily concatenate all the html with a line break as a delimiter which gives me a tidier html output. You can certainly add a line break with CONCATENATE() but it requires a lot more work. NB Add a line break in a formula by typing CRTL+Return.
2. I can pass in a dynamically resizing array of items with OFFSET(). In my spreadsheet the OFFSET() formula only picks up the number of html items that correspond to a table row that I have actually filled in. If I used CONCATENATE() I would have to go into and edit the formula everytime I wanted to add or subtract rows from my table. But with JOIN() I can add five new rows to my Row Detail table and the html creator will automatically accomodate those extra additions – it is beautiful!
I know I haven’t fully explained how to use JOIN() or OFFSET() here – I didn’t want to add a lot more information than necessary. If you check out my shared spreadsheet hopefully you will be able to work out how I did it. If not and you really want to know more then add a comment to this post and I will add an update with an explanation of my method.
Step 6. Compile the html for the table
In a cell in the “Templates and Output” sheet use SUBSTITUTE() again to replace the [rows] placeholder with the concatenated html for the rows. That cell then provides the html for your table. Copy and paste it into your website / blog.
Setting everything up was a lengthy process but now your hard work will pay off. Should you need to tweak the formatting of the html table it is now really, really easy and fast to do. All you have to do is update the relevant code in your template. For example, if you wanted to make all of the columns a little wider you can simply change the width value in the row template and your html output will automatically update – no need to adjust a concatenate function and re-fill. And when you come to design your next html table you will already have a spreadsheet ready, needing only a few minor tweaks before it can be used.
Below you can see the output for one of my tables. The full table can be seen live on my website here. I hope that you found this method useful. If you need further clarification on setting this up let me know in the comments.
With some logic formulae, such as IF(B2=“”,“”,SUBSTITUTE(…)), you can automatically hide irrelevant data from the concatenation stages. A formula like the one above checks to see if you entered anything in cell B2, if you didn’t it returns a blank cell, if you did it will return the output from the SUBSTITUTE() formula. ↩
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.