Pivot Table – From Data to Information

The word Pivot always brings to mind that hilarious scene from the sitcom Friends where Ross, Chandler and Rachel are trying to move a large couch up a narrow staircase. The whole time Ross keeps screaming “Pivot! Pivot! Pivaaat!” to highlight the need to turn as you move as an approach to pass through the corners. And with that, I have perhaps used my first meme and popular culture reference in this entire blog. 😊

In a world that is increasingly becoming driven and informed by all kinds of data – being generated every second! – using Excel or Google Sheets or a similar spreadsheet (or database) solution to capture that data is commonplace. But once the data arrives, what are some ways to make it talk? From an educator’s perspective, data is critical for everything that is done inside and outside the classroom. A few months ago I wrote about how I have used spreadsheets to analyze student scores on an exam. While the post captured some elements of identifying competence areas for students, there are ways to go deeper/broader than that. To that end, I wish to talk about Pivot Tables in this post.

While the concept of pivoting data isn’t really new (it has existed as a main feature of spreadsheet and database platforms for decades) it does seem to be one of those fuzzy areas that has a reputation of being complicated – and hence rarely used. So before we start pivoting data perhaps it is best to start with why and the when we might need this feature and then quickly go on to the how.

The pivoting part of the data is your ability to easily shift perspectives on the same set of data to get it to reveal different aspects and provide deeper insights.

Why pivot?

Dynamic perspectives

One of the big reasons pivot tables are powerful is because any dataset can be used as columns and any dataset can become rows. This ability to dynamically swap focus points on the same set of raw data is a good way to derive new meanings from the source. So, for instance, if your raw data is student assignment scores for an entire semester, then seeing the summaries based on individual student performance is quite different from seeing how the entire class did on any given assignment. Pivoting offers you both perspectives and instantly updates itself when you add more data into the source.

Time saver

As a slightly ironic aspect of being something not used a lot in common scenarios, one of the biggest features of a pivot table is that it is quick and quite easy to set up. Things that are possible using functions and formulae in spreadsheets are also possible using Pivot Tables except it takes perhaps 1/10th the time to do it. Check out this video below that actually calculates the number of clicks comparison between a manual table and a pivot table version of the same data.

Learn Pivot Tables in 6 Minutes (Microsoft Excel)

Less is more

Pivot Tables offer deep views into our data by keeping them summarized into a single visual chunk. In other words, they do more (behind the scenes) by doing less (in the front end). This way they extract meaning from a large matrix of numbers and text so that the table becomes rather powerful as both an observer and a predictor of your data. The pivoting part of the data is your ability to easily shift perspectives on the same set of data to get it to reveal different aspects and insights. Check out this short video on how these perspectives can be shifted to extract different kinds of meaning from the set of data.

Learn Pivot Tables in 6 Minutes (Microsoft Excel)

When pivot?

One of the challenges with features like Pivot Tables is knowing when exactly to use them. If you Google them you will find plenty of How-To videos and articles (which is great!) but without a strong and practical purpose the most useful features may seem less appealing. So here is a list I came up with to give you some context for potential use.

The raw data should have some repeating patterns. Like, username, full name, category, section, class name, project name etc. Anything that can be used to group the pivot since pivot tables produce aggregates, totals, summaries based on multiple occurences of a given value.
The source data should be well structured by default. One of the big limits of pivot tables is that blank, erroneous or mismatched data doesn’t get handled too well. So you will need to massage the source a little bit to make sure the summaries look good. If the structure is consistent then a pivot can save you lots of time!
Volume of data matters too. If you have a small record set which can be managed with a few basic =sum( ) and =average( ) functions then pivot may seem like more of the same. Of course, you can still pivot! But pivots are most revealing when larger datasets are considered.
Finally, look up examples. Often times looking at worked samples triggers new ideas. Networking with peers either on social media or offline can create capacities within our organisations for best case scenarios to use pivot tables.

How pivot?

As mentioned, Googling the how will get you tons of links and videos. But here are some unique examples I found of how pivot tables have been used/implemented.

Final thought

So much valuable and unique data gets created every day in our schools. But how much of it do we really use? I have seen examples of departments and teams spend money and resources in outsourcing solutions to ed-tech companies that sometimes, at the end of day, present a glorified version of an Excel sheet with a pretty dashboard. Instead, why not put in-house training in powerful tools and develop home grown solutions? Fundamental software like Google and Microsoft offer plenty of powerful (and scriptable!) features that can be used to build some pretty robust solutions. Having built some of these myself I think it is worth looking into as an option not just because you wish to save some bucks but also to be able to critically evaluate what is readily/reliably available before venturing out into the good, bad and sometimes ugly world of ed-tech companies.

Happy pivoting!