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!
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.
