Danny: There’s a ninety-five pound Chinese man with a hundred sixty million dollars behind this door.
Linus: Let’s get him out.
Ocean’s Eleven (2001)
Once you’ve tweaked your advanced filter to the point where you have some decent results, you can use the same pivot table techniques to get an overview on that - but not yet.
For the moment this filtered data is just a view of the data - rows that don’t match our criteria are still there, but hidden, so any pivot table we generate will also include those hidden rows.
To address this, we need to copy the filtered data into a new sheet:
• While the data is still filtered, select CTRL+A to select all (CMD+A on a Mac).
• Press CTRL+C (CMD+C on a Mac) to copy the selection - or select the menu Edit>Copy if you prefer.
• You’ll notice that the copied data has dashed lines across it - this is because you are only copying the filtered data, not the hidden data.
• Create a new sheet to paste this data into, by selecting the Insert menu and then Sheet >
Blank sheet (you can also right-click at the tabs across the bottom and select Insert sheet, or press SHIFT+F11)
• Paste the copied data into this sheet by pressing CTRL+V (CMD+V on a Mac) or Edit>Paste
• Scroll to the bottom of the sheet to check it has only pasted the filtered data: there should be a couple hundred rows rather than over 15,000.
You can now use this data to generate a pivot table, as you did before with the whole data. So do that, putting Vendor Name in the rows, and Invoice amount in the data area to give you a total for each company.
This will also give you a list of companies which is easier to whittle down further: at the top of the column containing the company names should be a coloured cell saying Row labels. On the right of that is a downward arrow that opens up a drop-down menu showing all the company names. Click on this and you can untick any companies you don’t want to appear on this pivot table.
Above we can see that ALLSPORTS SCHOOLS COACHING LTD is not a transport company, so we can untick that. Likewise CENTRAL COACHING AND SPORTS ACADEMY, DOABA TENT HIRE SERVICE, JOHN GALLAGHER SPORT COACHING and so on (These are just examples in my data - look for similar examples in your own.)
Some might not be immediately obvious: is FREIGHT TRANSPORT ASSOCIATION a transport company? A quick search on Google will tell you it’s a trade body, so that can be excluded as well.
What about FINESSE COACHES LTD? That could be a coach in either sense of the word, but it turns out to be the vehicle kind. And in fact, while Googling, you may find a public inquiry judgement relating to a company of the same name in the Midlands by the Traffic Commissioner, dated a year previously. That’s something to follow up - perhaps with other companies here too (but don’t get thrown off track: finish the story you’re working on before beginning another).
Once you’ve filtered out the companies you don’t want in this table, you have a dataset you can re-pivot for a number of possible stories:
• Who’s getting the most money?
• Who’s spending the most on transport?
• What sort of companies are being used?
Getting (the data) out 29 Sorting column B from largest to smallest again would bring our biggest recipient to the top (in my data that’s one taxi company getting £67,000 in this one month alone).
What if we wanted to look at spenders? Well, we still need to retain the filter on company names:
to do that, drag the ‘Vendor Name’ from the Rows box into the Filter box. That will just give us a grand total - but we can also drag something new into Rows: Directorate. Your four boxes should now look something like this (depending what version of Excel you’re using):
This helps us understand who’s spending the money, and perhaps what on.
The overwhelming majority of payments, for example, are being made by the directorate ‘CYPF’ -another quick search, phonecall or local knowledge would tell you this stood for ‘Children, Young People and Families’. This covers²¹ “all business areas relating to education and social care for children and young people”.
So these may include taxis and coaches for transporting pupils to and from school (individually and in groups), and on school and other trips, among other things.
From this point you could drill down further to just look at spending by a particular directorate - it all depends on what you’re interested in, or what’s more newsworthy at the moment. For example:
• If the council is making lots of redundancies, spending money on temporary staff may be worth focusing on.
• If someone in the council is making statements about wasting public money, is their department practising what she preaches?
• If there are big cuts, where is money being spent unnecessarily? Or where are cuts not being made?
• If there are cuts elsewhere - for example in welfare payments - how might that be having an impact at a local level? For example: an increase in homelessness shown by increased spending on bed and breakfast accommodation.
²¹http://www.birmingham.gov.uk/cs/Satellite?c=Page&childpagename=Legal-Services%2FPageLayout&cid=1223092568895&pagename=BCC%
2FCommon%2FWrapper%2FWrapper
• If a particular issue is making the headlines, such as teenage obesity or roads in need of repair, how much money is being spent on that issue, or how has that changed?
You can add many more - the more you read the news, the more questions will spring to mind (you’ll