Spreadsheet Hacks: Making spreadsheets work for you through Functions

This is the first of three blog posts about spreadsheet hacks that have saved my sanity and my health. The first two are focused on fancy-pants things you could do in Excel (Functions and Fill Down, and PivotTables), and the third on collaborating via Google Sheets. These posts developed out of a post I did for PhD Talk in their ‘This is How I Work‘ series. 

I mostly use Excel for lists and tables. I now work with a lot of data–mostly not for research or finance, but in reports on student enrolments or grades, or heights to assign gown sizes. Obviously these tips aren’t going to work for multi-million dollar profit/loss statements, or for sophisticated mathematical modelling. This is for teachers and administrators who could do with clawing back a couple of hours a semester here or there, through a few short cuts.

These hacks regularly save me 30 minutes or a couple of hours. I’d love it if they sped up your life too!



Functions are what Excel calls mathematical formulas. There are basic ones like SUM (which is for basic arithmatic) or AVERAGE (which calculates averages), and some really complex ones like CONCATENATE (which puts text things together in ways I still don’t quite understand).

You probably already use SUM and AVERAGE in Excel, but what if you want to do something a little bit more complicated?

The most common use of Functions for me is turning a list of data into a list of slightly different data–perhaps I want to take the percentage given for an assessed work and turn it into a grade?

So I gave one student 77%  but I want to display their grades to them as H2A, or B, or Credit.


In Excel, you can just use the Function button, but it’s worth digging into the formula itself if you want to do slightly more complicated stuff. You also don’t need to build these things yourself. Put aside 15 minutes to Google the thing you are trying to do, ‘excel formula function if grades’ for example. Someone else will have made an example, so you can copy it and tweak. (That’s why I’ve put my function in full above!)

Now you have a basic outline, it’s time to tweak it to suit your needs. Maybe that means adjusting the grade boundaries–perhaps 70% is a “First” at your institution. . That means unpacking the function, so you know which bits to change and which bits to leave alone.

If you don’t do mathsy stuff often, it can be challenging, but go slow. Spend 30 minutes now to save yourself an hour next semester.

=   means this is a function.
IF   is the function type. Here we are telling the computer to check IF this is true, then do this other thing
C3   is the cell number. That is, the place where the relevant first bit of info is stored. Column C, row 3.
>   Remember your basic algebra? x>y, x is bigger than y. x<y, x is smaller than y. x=>y, x is either the same size, or bigger than y… etc. (I still do this by looking at the > sign and going, which size is wider? big size, big number. Yes, really).
(brackets)  Brackets group stuff together: 3(x+y) (add up x and y and times it by three) is not the same as 3*x+y. (three times x, and then add y to it).
, commas  In functions, commas are used instead of THEN. Here, ‘IF this is true, then do that, then check IF this is true, then do that, then…’
“quotes”  Put something in double quotation marks so that exact phrase will appear. This is useful when you want the outcome to be “Distinction” or “Academic Concern” rather than another number. (If you are 156cm tall, and you need an academic gown size 120, you can just enter 120 into the formula, not “120”).

This particular function is called a ‘nested function’ because it has a whole stack of IFs inside each other, like a matryoshka doll.


So literally that formula means,

Hey computer, check out C3. What number did the student get? If it was over 79%, give them an A. But if it was over 74%, just give them a B. etc If it was less than 50%, give them an F. Thanks computer!

You can now use this nested function to do all sorts of other things! Hurrah!

Fill Down

Except, oh hell, you’ve finally tweaked your grades function to give the right outcome… and you realise that C3 only works for your first line. You need to do it again for C4, for C5, for C6… ‘This isn’t a short cut!’ you cry.

That’s when Fill Down comes to the rescue. You can either do this via the Edit / Fill / Down menu in Excel, or Control + D (both Mac and Windows).

Fill down is smart. If you Fill Down a formula, it will assume that you want it to update C3 to C4 to C5 to C6, and do it automatically!

Screen Shot 2016-04-01 at 12.36.57 PM.png

You can also customise Fill Down. Via the Edit / Fill / Series menu, you can tell the computer to Fill Down numbers in order (e.g. 1, 2, 3) via ‘Linear / Step Value 1’, or to fill in days of the week, or months of the year.

Tseen Khoo of the Research Whisperer blog recently tweeted:



Each time I tried to finish this post, I’d write another 600 words. So, coming up,  PivotTables and Google Sheets. Those two are big ones, they gave me back DAYS of my life, every year. They have saved my sanity and my health, as well as making me more efficient and a more useful colleague and manager…. so look out for parts 2 and 3 of this series! Check the #spreadsheets tag, or the Tech Tips category. 





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.


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: