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.


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.


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. 


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

The blog is back

Not only is Writing Well and Being Well for your PhD and Beyond now in to my publishers, but we have a publication date and a preorder link.

Read More

The story of my thesis

If you have ever been to one of my workshops in the last decade, you will probably have done this warm up. In fact, if you came to a multi-day writing retreat I ran, you would have done this at the beginning of each day. It is the most powerful, most flexible, simplest tool in my writing tool box.

Read More

How to unstick your reading list

As I was getting to the end of the recent book, I was buying books at my usual pace but not reading them (as that brainspace was completely taken up with reading my own draft or references for the draft).

Now the book is in and the summer has started, I felt excited to dive into all these books but I also felt stuck. I couldn’t get into gear, let alone find my groove.

So I rummaged around in my toolbox, and came up with this list of techniques. None of them are perfect, but little by little we are turning the dial back to reading.

Read More

Get the latest blog posts