Blog

Spreadsheet Hacks 2: PivotTables

This is the second 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. 

In my previous post, I talked about the spreadsheet hacks that changed my working life. This one is going to be MOAR technical. Unless you regularly deal with spreadsheets that have thousands of cells in them, you probably don’t need this.

If, however, you deal with spreadsheets that have thousands of cells in them, and they make your life miserable, you definitely need PivotTables.

I cut down one task that used to take me 5+ hours, twice a year (and in July it always interrupted my holiday), into a task that takes me 45 minutes each time. 2 days of my life, regained! I’d love it if some of these improve your life too.

***

Pivot tables are super easy, once you know what you are doing. But to start them might feel a bit scary. I was terrified by the memory of trying to do something like this fifteen years ago when I just started working. But PivotTables are better now, and much more useful.

Pivot tables are one of Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.
Pivot Tables How To at Excel Easy

What it does it it allows you to select only certain parts of your big lump of information, to sort them, and then to perform functions on them, very very easily. 

For example, I get a data report from the university that tells me the enrollment details of every student at my college. This means I can check if they are meeting our requirement that they are enrolled full time at the university.

What I want to do is ask:

Hey, computer. Tell me, which students aren’t enrolled in enough credit points to meet the requirement?

Students take varying numbers of subjects, and they take them at different times of the year. Some subjects are worth 6.25 points, some 12.5, some 25 points. It’s a nightmarishly messy data set.

Combing through 1800 lines of information, with every subject name, subject code, etc etc would take me hours. Even mixing in a few SUM functions and Fill Down, it’s a couple of hours of semi-manual work.

However, with a pivot table I can do it quickly. (In fact, I just did it to get some screen shots to write this post… so very quickly indeed!)

Because Excel is better at counting and adding up reliably than me, I know the numbers are more likely to be right. The bigger and more complex the task, and the longer I work on it, the more likely I am to miss something, or make stupid mistakes. PivotTables eliminate tiredness and silly errors.

That leaves me brain space, and time, to do the things I’m much better than Excel at–like checking in on students, crafting careful and elegant emails, referring, empathising, or giving constructive criticism.  Human stuff.

It sounds kind of miraculous, and it really is!

***

How do you PivotTable?

First, you go to a new sheet or workbook and select the Data / Pivot Table menu item in Excel. It will ask you to select your data range. Go back to your data and drag to select it all.

Next Excel will show you this black PivotTable Builder. Select what data you want to see (I want student number, name, the study period and the credit points).

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

It will just dump those Fields into any one of the four boxes you see above. Simply drag the info into the right box.

  • I want to know which students are enrolled correctly. So I want Student ID, Family Name and Given Name as my first column, of Row Labels.
  • I only want to check their enrollments for Semester 1, so I want to Filter by Study Period.
  • Finally, I want to add up the credit points of all their subjects, so that is a function Value. The black box automatically decided to COUNT the credits, so I clicked the little i and selected SUM instead.

PIVOT TABLE IS GO!

You can stop there. OR, you can filter further. 

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

I wanted to Filter by the Study Period, so I clicked on the little downward arrow in the red circle (sorry this is a terrible screenshot, I’m preserving the anonymity of my students!). This box popped up. I scrolled down selecting ‘Semester 1’ and ‘Year Long’ subjects only.

I have a list of ‘How many credit points is each student studying this semester?’ with 300 entries, not 1800.

You can stop there. OR you can add on some of your other new skills from the previous post. For example, a student may request permission to study 37.5 credit points. I can check who is ‘underloading’ as we call it, by combing through the list, or by writing a new IF function.

=IF(B281<50,”Underloading”,””)

Writing “” leaves the column blank.

This will mark everyone who is underloading.   It’s still not totally automated, I have to manually weed through the list, ignoring anyone I gave permission to, exempt students, postgrads… but I’m starting with a list of about 50 students, not 1800 or 300. And it took me half an hour, and I know it’s right.

Finally, we have the actual task outcome. Next week, I’ll be writing to 16 students, checking that they understand the rules and finding out if there are any issues.  Because that’s really what I wanted to do in the first place!

***

I hope some of you will find this useful as you struggle to spend less time being an administrator, and more time being a teacher, an advisor, strategist, mentor or leader. Next time, I’ll talk about how I am a better manager because of Google Sheets. Find it via the #spreadsheets tag or in the Tech Tips Category. 

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: