Some love for the pivot table

A step too far?

A step too far?

Now marriage is probably a bit too big a commitment, but for a data journalist, love for the excel pivot table is due.

When Excel has formulas such as this:

=IF(F6=1,IF(VLOOKUP($D$6,$B$9:$E$51,4)=”All”,SUMIF(Payments!$N$6:Payments!N279,$I$9,Payments!$Q$6:Payments!Q279),SUMIF(Payments!$K$6:Payments!K279,$I$9,Payments!$Q$6:Payments!Q279)),IF(VLOOKUP(Data!$D$6,Data!$B$9:$E$51,4)=”All”,SUMIF(Payments!$O$6:Payments!Q279,Data!$I$9,Payments!$Q$6:Payments!Q279),SUMIF(Payments!$L$6:Payments!Q279,Data!$I$9,Payments!$Q$6:Payments!Q279)))

(not that I have any idea what that means) then anything that makes life as simple as the pivot table is worth falling in love with.

In his book ‘Data Journalism Heist’, Paul Bradshaw calls pivot tables the ‘crowbar’ of data journalism, as they are the best way to “crack open your data”.

What makes pivot tables such a powerful tool is that they allow you to simply drag and drop data into columns or rows, making it easy to analyse, categorise and summarise the data, giving you the answers you need in no time.

Take a table like this for example.

3754 rows of European Investment Bank finance contracts from 2008 – just a little bit unmanageable for deducing anything from this dataset.

Creating a pivot table

If you want to try this out during the how-to, here is the original copy of the dataset I used.

Click on any cell within the data, making sure there are no empty rows or columns in between and on the Data option in the ribbon find the pivot table option (in some other versions it can be found in the Insert option in the ribbon), and select ‘create manual pivot table’, choosing the new worksheet option.

Creating the pivot table

Creating the pivot table

You should now have an image similar to the screenshot below, as it could be a slight variation depending on your version of excel, it should still function in the same way.

Pivot table - starting point

Pivot table – starting point

A pivot table allows you to drag and drop each heading into a row or column to analyse the different elements of your data.It is best to keep things as simple as possible, just dragging the different field values into rows whenever you want to examine them.

And now the fun begins. In our pivot table, we can take it in turns to look at each of the value fields to the rows section of our pivot table, for example in our data – sector, region, country and date.

Let’s look at how we can look at the amount in loans for each sector.

Dragging the sector field to the row labels

Dragging the sector field to the row labels

Then by dragging the signed amount to the values field,  we can look at the total money in loans from the EIB for each sector.

Excel pivot tutorial screenshot 5

It works just as easily with another field, for example the amount for each country or the total loans for each year.

Comparing values for each country

Comparing values for each country

Comparing values for each year

Comparing values for each year

You can also use the column field to dig deeper into your data, but be very careful as it tends to complicate things.

Using both the column and rows field

Using both the column and rows field

To get to a potential starting point for a story, sort the data in the row with values that you have in order to compare them effectively and identify the largest/smallest values. Just select one of the cells in the values column and press the sort button on the top left of the Pivot Table ribbon.

Sorting to identify largest/smallest valuues

Sorting to identify largest/smallest values

So, you can pretty quickly identify that from all the regions listed in our data, the European Union  has received more money in loans since 2008, followed by Enlargement countries. The regions with the least are the EFTA countries and South Africa is in second to last place. By simply dragging a different value field into your row label, you can just as easily  see which country has received the most, or which year had the lowest total amount of money in loans.

Another brilliant feature of pivot tables is the possibility to analyse different calculations, not just the total sum. By clicking on the i button next to the sum box (this is for macs, similarly you should be able to find this by right-clicking on a pc), you can select different indicators you want the table to display.

Going beyond the sum, looking at other indicators

Going beyond the sum, looking at other indicators

You can now analyse other values other than the sum, for example the COUNT – how many loans were signed for each region, sector, country or year. Other useful things to look at would be AVERAGE amount per loan signed for each field and more.

And there you go, now you can pivot.

But watch out, don’t get too emotionally involved, it looks like Mr. Pivot has been taken.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s