Why Pivot Tables are Your Best Friend

Following on from my post on beginner’s tips for Excel taken from our workshop with James Ball (of Guardian Datablog fame), I’d like to highlight the possibly most valuable tool within Excel, which if you can master, you’d be well on your way to becoming an actual Data Journalist….the pivot table.

A pivot table is basically a table in Excel which you ask a question of, and considering that’s precisely what data journalists do, it’s a pretty handy tool. Like other tables in Excel it can provide sums and counts, but pivot tables are especially useful because you can drag and drop the table’s fields graphically/visually, changing around the different value fields in the table. While standard Excel tables contain ‘flat’ data of columns or rows, pivot tables can provide summaries of different aggregations e.g. averages, so you can set them up to summarise, analyse, explore and present your data.

One of the shortlisted pieces in last year’s Data Journalism Awards was from Wales Online, who collated a map of the percentage of Welsh schools with CCTV by local authority. They did it by:

“[collating responses] in a spreadsheet, with open responses evaluated to place them in categories, for example whether cameras were inside or outside and whether parents had been told the locations or just that there were cameras on site. The number of responses in each category was then collated using a pivot table.”

So, how do you create a pivot table?

  • Select the data you want to explore, go to ‘Insert’ then ‘Add pivot table’
  • Always put the pivot table in a new worksheet

  • You can then insert whichever values you want and Excel will create the tables for you, for example below I clicked ‘Donation’ and ‘Party’ and it created a table of sums for each party

  • You can right click on a cell e.g. sum of donation and click on ‘value field settings’ to work out average/total etc. or change to decimal

  • You can move about different things in the rows or filters by dragging in/out donor/donee etc.
  • Make sure you organize the cells properly by clicking a single cell and sorting a-z, largest to smallest etc.

 

Have a play about with the pivot table and you’ll soon get used to it and recognise it as one of the most useful things in the data journalist’s toolkit.

Further links:
http://en.wikipedia.org/wiki/Pivot_table
http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
http://spreadsheets.about.com/b/2012/06/01/excel-2007-pivot-table-tutorial-2.htm
http://www.cpearson.com/excel/pivots.htm

 

 

Leave a Reply