Blog

Spreadsheet hacks 3: Google Sheets

This is the third in this series of spreadsheet hacks that have saved my sanity and my health. The first two were focused on fancy-pants things you could do in Excel (Functions and Fill Down and PivotTables), but this is about ‘collaborative, cloud-based organisation solutions’ as I describe it to my boss, or ‘Sticking it on a Google sheet’ for everyone else. This series developed out of a post I did for PhD Talk in their ‘This is How I Work’ series. It’s worth checking out the whole series!

Screen Shot 2016-04-01 at 4.52.03 PM.png
CC from Atos on Flickr

 

Google Sheets meant I went from working 6 day, 60 hour weeks to get the timetable up and running, to working 50 hour weeks, now down to 45 hours as I’ve improved the design of the spreadsheet and other processes. That’s a normal working week, and I stay reasonably sane, and reasonably healthy. Hurrah!

***

Frankly, most of the time, I’m not using a spreadsheet for numbers at all. I’m using it for lists. In those cases, I don’t use Excel, I use Google Sheets.

Sign up for this tutorial. Let me know what subjects you’d like to teach next year. What’s your name, title and university qualifications for the Annual Report?

In the old days, I’d get people to email me the information, and I’d make a list of it. Or we’d try to export it from a database–but that meant which meant they had to email us and we had to enter the data in, in the first place. Ugh! Ugh! So much work!

Now I use Google Sheets.

Students and staff can jump on the sheets, add information, update information, they can all use it at once, everyone else can see how the information should be entered because they have a lot of examples. They can see the information immediately it is updated, and my inbox is not longer a massive bottleneck. 

The students are model digital citizens. The staff sometimes need a bit more intervention, but it’s 30 minutes a fortnight, not half a day a week like it used to be.

Sometimes I set the sheet to private, sometimes to public. Sometimes anyone with the link can edit, sometimes they can just propose changes that I then approve.

I can sort the information, search it, add to it, or download it to share with other colleagues.

I have vastly complex sheets with over 250 rows of data, being used by 4 stakeholder groups comprising nearly 400 people. And I find they are MARVELLOUS.

but they aren’t perfect. Some things to look out for:

  • With large data sets, you might want to View / Freeze certain columns so you don’t lose sight of your header rows as you work your way down to row 200. I find  the Freeze bars seem to move from time to time. They do no harm, and they are easily re-set, but it’s a nuisance.
  • Never put any information in cell A1. It’s too easy for someone to overtype there by accident, when they meant to be in the search box or something else. I put the sheet title in B2, and that works well.
  •  If there is any benefit in cheating, impose limits on what people can do. Setting up tutorials, we all work together, and there is no benefit in replacing someone else’s sign up. However, there are better and worse rooms to teach in, so on the timetable I only allow comments, which I then approve. Do this via your Share settings.
  • If something goes missing, use the File / See revision history function to see what has been changed, when and by whom.
  • Beware if you have any spreadsheet vandals in your team, especially if they are more senior than you.  A few years ago we tried to use a Google sheet for a team process. A senior colleague would delete and retype data instead of using cut and paste. They would regularly resort the list when the tasks had been delegated as ‘you work on lines 55-120’. It was confusing and stressful. We don’t use Google Sheets for that job any more.

So Google sheets aren’t the answer to everything. But they are so often the answer to things I need to do.

And I get such wonderful feedback about them from students and team members. They feel included, empowered, informed, and they are easy to use. 

I hope this series has given you some tips and hacks that can transform your working practices through the power of spreadsheets. Now we’ve got all that time back, lets go talk to some students, or read an article, or spend half an hour thinking, or go home on time. 

 

SHARE

Succeeding in a Research Higher Degree

Doing a Research Higher Degree (like a PhD) is hard, but lots of people have succeeded and you can too. It’s easier if you understand how it works, this blog gives you the insider view.

Contact

Related Posts

Writing Well and Being Well for Your PhD and Beyond is published

It’s publication week for Writing Well and Being Well for Your PhD and Beyond: How to Cultivate a Strong and Sustainable Writing Practice for Life. It’s available as a paperback and ebook on all the big book websites, and via the publisher. As with all my books, I’m delighted if you buy a copy but also delighted if you recommend it to your university library so you get to read it and so does everyone else.

I had the best time writing this book, and the pre-readers have given such warm and delightful feedback. My series editor described the book as ‘your best friend’; ‘it’s personable, relatable, oozing with strategies.. It simply is a gift’. The peer reviewers said things like: it’s ‘calming and supportive’, ‘a useful review and re-thinking of the writing process’ that ‘gives permission’ for you to write, containing a ‘sprinkling of humour’ but also ‘addictively practical’.

Read More

What I learned from tracking my writing for a year

Back in 2021, I tracked my writing for a year. I kept a done diary for 6 months (as I’ve previously written about on the blog), but I also met up every month with an old co-author and we each wrote a little report on what we’d been doing: what was growing in the garden, what we were eating, what was going on in the world, what we were doing to move, what we were reading, but also what we were doing to progress our next writing project.

Read More

Towards a theory of University ‘excellence’

Universities like to say they are ‘excellent’. It’s a buzz word, and when you’ve been around campuses for a while, you realise it’s an adjective that’s applied to absolutely everything, so it kind of ends up meaning nothing. But when we look around universities, we see lots of ways they aren’t great. But recently I worked with another major partner in the global higher education industry (who is not a university) and it helped me see why ‘excellence’ discourse is good, actually.

Read More

Get the latest blog posts

%d bloggers like this: