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
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.
=IF(C3>79,”A”,IF(C3>74,”B”,IF(C3>69,”C”,IF(C3>64,”D”,”F”))))
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!
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:
Yes, “copy or fill down” is the golden secret. Has changed my blogkeeping life. 😉 https://t.co/90oLe4XES3
— Tseeny McTseenFace (@tseenster) March 18, 2016
Hurrah!
***
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.
