I have been doing a bit of work with Google Sheets lately, here . To be honest, Sheets has been one of those applications which I have wanted to go further with for a while, but never really found the time or purpose. With the help of Ben Collins, Alice Keeler, Chris Betcher, Jay Atwood, Chris Harte and Eric Curts I have explored everything from formatting to formulas. Here then are some of my lessons learned through it all:
Smashing Cells Together
I have lost the amount of times that I have had to create a spreadsheet with a range of different data, but each somewhat related. For example, one column has a list of usernames, which then needs to be turned into an email address. Obviously the simple answer is to write two lists. However, the shortcut is to use ‘&’ to smash the two cells together. If you want two names combined then you use ‘&” “&’. To remove the formula from the cell, download as a CSV. This turns everything into text. Then you can either continue using the spreadsheet in another program or re-upload the new sheet.
Validating More than Just Data
I had always been aware of data validation. However, I had never quite seen the potential. Jay Atwood talks about keeping a menu of items in a separate tab in your sheet which you can easily make into a validated columns when collecting raw data. Another interesting use that I came upon is using a data validation cell is a button to select a particular focus. I found this via a video from Ben Collins who documents how to use the VLOOKUP formula to make a dynamic table. (You can actually find one built into the gradebook template in Sheets.) I took this further and made a dynamic table based on a CSV download of a simple timetable. Collins has also written a guide to creating wildcard to search through a data set. While the team at CIFL have developed a thorough introduction to the possibilities of the VLOOKUP formula.
Formatting
One of the big differences between Sheets and Microsoft Excel that I discovered early on with my use of GAFE/GSuite was the absence of formatting. It did not necessarily concern me. However, I worked with some teachers who were frustrated by this. Over time, Google has improved the formatting within Sheets. Now you can merge cells, add alternate colours and change direction of text. Although Sheets does not have the variety of preset formatting options, the Explore Tool now makes its best attempt to provide useful recommendations.
Colourful Conditions
Although it is easy enough to apply a conditional formatting to a set of numbers, it is not as obvious about how to deal with categorical data. The answer is to combine multiple single colour rules. Another useful trick is selecting the ‘awesome’ box with the conditional format menu open. This will then show all the rules applied throughout the sheet. Also, as you select each one, the range being affected is highlighted in the sheet.
Life Made Easier with Formulas
Last year, I decided to analyse my blogroll in an attempt to appreciate the diversity or lack thereof. I started by downloading my OPML file from Feedly and opening this up in a sheet. I then progressively went through my 200+ rss feeds and replaced them with the website, as well as the various categories. I recently discovered that I could have imported some of this data using the IMPORTFEED formula. In part it was Tom Woodward who uncovered this possibility for me through his post on exploring WordPress. The further that I go, the more I realise that formulas afford so many more possibilities than what is offered in the menu. For example, sorting using either filters or dropdowns can be limiting and restrictive. The SORT formula does the same thing, but with more of the nuances.
In Scripts with Trust
Moving on from formulas, I have also being toying around with a few scripts and addons lately. This has included:
- TAGS Explorer: Martin Hawksey’s Twitter visualisation tool.
- TimelineJS: the tool from KnightLab to build a visual timeline from a spreadsheet for representing timelines.
- Epic Rubric: Alice Keeler’s script for creating, collating and sending out rubrics to students.
More than building formulas, I feel that scripts involve a bit more effort and patience. Sometimes things do not work, but that is part of the learning to work backwards working out where things may have gone wrong. Usually it involves me breaking code that I was not meant to touch. I must be honest, I am still yet to properly dive into scripts and APIs, but know that is probably one of my next moves.
Creative Sheets
One of the areas that has surprised me about exploring Sheets is the various creative activities that seem to rise. Whether it be Tom Woodward’s play on magnetic poetry, Alice Keeler’s idea for pixel art, Eric Curts random emoji writing prompt generator and Jay Atwood’s use of text rotations to create a shape poem. Activities like this always leave me rethinking the limits as to what an application like Sheets may have to offer.
So that is me, what about you? Have you had any experiences with Sheets? As always, comments welcome.
If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.
Tips, Tricks and Sheets by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
That’s a nice collection! Google Sheets has always been an amazingly powerful tool for me but a difficult one to get lots of people to think about outside of simple number operations.
Yes, I have tried to show a few colleagues some of the stuff I have been toying with. They like the finished products, but not necessarily the process.
I also finally got the Blog Roll template that you made for me up and running. Wondering, when you setup the page to grade the data out of the Google Sheet, I presume that the site needed to be published to the world first, like with TimelineJS. With this in mind, what format does it need to be in? I noticed that it gives about five options, including webpage, CSV, TSV etc.
Tried to change the sheet aligned to the blog roll to my own sheet, but it simply didn’t work. I am assuming that my ‘headings’ are the same and realise that might be another thing to consider, but wondering about the publishing side first. I recall that you copied the data when you created it, so maybe I need to change something in the way I have organised my data.
Am I presuming that the process is that simple? Really enjoying finally digging my teeth into Sheets, but it does require patience and perseverance at times. Next project is to explore the possibilities and potentials of creating a dashboard.
You should be able to make that sheet accessible by doing the Publish to the Web option. That should make the JSON format available. I’m not sure if publishing via csv/tsv would do that but I’ve never tried.
If headers were changed, it would make a difference (and probably break things). If you have a URL where it’s failing, I can take a look and possibly tell you what’s up.
I swapped it back to your sheet. Will investigate when I get a chance. Thank you as always for the support.
Nice work, Aaron. I love the Publish to the Web option. I find the docs version underwhelming, perhaps because A4 doesn’t translate well to a web page, but a published sheet, even with multiple tabs, always looks good.
Last year I used forms embedded in a site to have staff sign up for PD sessions, then had a master sheet that pulled in data from the sheets connected to the forms. The master sheet was then embedded on the site along with the sign-up forms, so that people could refer back to see what they’d signed up for and see what others were doing. I’m not sure that anyone used it (they still just rang me to ask. Isn’t it always the way?), but I was very pleased with the elegance of the whole setup.
Also, I’d add that the first thing I do with any finished sheet is copy and “paste as values” to strip out the formulas, which avoids the downloading as a CSV and re-uploading which you mention above.
Another great read. Thanks.
Thanks for the comment Eric. I think that there is some really simple things that we can do with Sheets. I’m left wondering if I could possibly import data and paste as values using a formula? Now you have me thinking.
Academia Obscura shares a spreadsheet for generating representations of solar systems.
Ben Collins has created a site/guide to support the move from Excel to Google Sheets.
Also on:
Martin Hawksey shares some of his Sheets tips and tricks associated with putting together a conference. It is always interesting to consider different contexts and they way in which Sheets are utilised to inspire new possibilities.
I enjoyed this piece Ben. I feel have achieved so much just within Google Sheets, including collecting together information, organising responses and generating a timetable.
Also on:
With checkboxes as radio buttons, Ben Collins presents another example of the affordances of Google Sheets.