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