Introduction

2 + 2 = ___

Your resistance to answering this question is futile: you’ve probably just answered it without even thinking.

We doubt it’s controversial to say that many of you taking this course have the instinct and itch to know what a column of numbers adds up to, or how the numbers are spread across the different categories in the dataset in front of you. Spreadsheets make this sort of descriptive analysis easy by giving you a kit of mathematical functions to add, subtract, multiply, divide and create averages and percentages from numbers. We think that you know how to use some of these, but if you want to brush up your skills, run through our data fundamentals course on analysing data.

However, the spreadsheet also has another powerful descriptive analysis tool called pivot tables. In a nutshell, this is what they do:

*Image: “More Kittens”, courtesy Hitchster. Some rights reserved: CC-BY 2.0. Adapted by Tactical Tech.*

Pivot tables summarise complete datasets without you having to write formulae, create new columns, or arrange your data in any particular way. They enable you to combine data in ways that reveal the relationships that exist in the data, and show it to you in a new light. They don’t change your data, they have a stack of useful functions built in and using them effectively can cut down on a lot of repetitive tasks, saving you time.

A combination of a number of spreadsheet functions – like sort and filter, and some formulae – work in a similar way to pivot tables in that they enable you to rearrange and pull out small bits of data more easily. Pivot tables do these things for the complete dataset, and present it to you so you can see it all.

Upon completion of this course, you will:

understand the basics of pivot tables and how they work;
have created around 20 different examples of pivot tables;
be able to build and adapt the layout of pivot tables; and,
be able to use pivot tables to examine and explore a dataset.

Course requirements

A gentle introduction to exploring and understanding your data builds on two earlier School of Data courses:

Data Fundamentals, which covers the basics of data, and how to work with a spreadsheet.
A Gentle Introduction to Cleaning Data, which looks at the four most common ways that data gets messy and dirty, and outlines practical solutions to them using a spreadsheet.

To do this course you will require the following:

An up-to-date version of the free and open source software Libre Office installed on your computer. We assume you know how to do this. We have chosen to use LibreOffice because it doesn’t cost anything (unlike Microsoft Excel), doesn’t systematically invade your privacy or require a continual broadband internet connection (like Google Spreadsheet).
a copy of the sample dataset for this course, which is a small set of data about the contents of the author’s kitchen shelves. In the course, we’ll use it to demonstrate the basics of making pivot tables.
a copy of GRAIN’s data on “land grabbing”. This dataset is typical of the sorts of data that Non-Governmental Organisations (NGOs) create and work with. We have used it as the example dataset in our earlier course on data cleaning. In this download there are two worksheets: the original ‘dirty’ data, and the ‘cleaned’ version. Throughout, we will work with the cleaned version.

Course contents

There are four sections in this course.

Section 1 covers the first steps in building a pivot table, showing what happens to your data when it is added as a Row Field in a pivot table.
In Section 2 we look at how to make a pivot table even more useful by adding ‘data fields’.
Adding Column Fields is covered in Section 3.
To complete the course, Section 4 looks at how to create charts from data in pivot tables, adding a visual aspect to your understanding of the data.

There are three parts in each section:

A quick task which uses a dataset about the contents of a kitchen shelf to illustrate the concept of pivot tables.
A longer task using a dataset about the massive sell-off of agricultural land in the developing world. This will go deeper into creating pivot tables, along with some problems that will help you put your new skills into practice.
A bonus feature, which explains how pivot tables relate to the other useful features in a spreadsheet.