I recently discovered something that I had been … let’s say fearful of … and that’s pivot tables. I heard them mentioned by the odd bean counter here and there, the finance team talked about them like a good friend and I had seen the option dangled in front of me while using Excel. But never had I ventured further, afraid of the consequences and the possible damage that could be made to the time-space continuum. But that all changed a few weeks ago as I fumbled around looking for a elegant solution to my budget spreadsheet. Traditionally I’ve always resorted to charts, such as a line graph but it doesn’t always work that well.

I use Office 2007 and while the ribbon isn’t everyones cup of tea I’ve slowly acclimatised to it. Sure there were a lot of menu options that all of a sudden seemed to disappear on me (forcing a page break for example, Alt+I, I Enter) the Insert ribbon showed me PivotTable, teasing me to click the button and see what woud happen. Possibly I would get sucked into a vortex of numbers and formulas, complete with middle-aged maths teachers in shorts, socks, sandals and enough pens clipped into various parts of the outfit to explain the ‘Where did all the pens go?’ question.

So with a daring bravado really seen in someone of my character I selected my columns of data and clicked the PivotTable button.

Pivot tables - Useful but about as much charisma as John Keys
Pivot tables - Useful but about as much charisma as John Keys

It came up with a few option I wasn’t too sure about but when in doubt and driving software the best option is always just to click OK (before you go installing that free movie trojan you just downloaded, I was kidding ok) but it offered to put it in a new worksheet so there it went, where it then allowed me to choose which columns I want to report on. You can then choose a column to break down into separate columns (e.g. different types of expenses) by dragging that data type into the Column Labels box. The really cool thing (I can’t believe I used the cool when talking about pivot tables) is that you can then manipulate the pivot table to have it display exactly the way you want it. For example, right-click on the date and you can choose to group date transactions by month and year which is more suited to a budget (I’m organised but I don’t budget on a daily basis!). You could then filter by an expense type if you wanted to just look at exactly how much you spent on Miley Cyrus paraphanalia, if that’s your thing.

Lastly if you prefer pictures, it’s easy enough to click the Options button and choose the Pivot Chart option. The best thing is that the chart will update based on the filters and groups you’ve set for your pivot table. In options you can also update your data source if its location or range changes.

Great, so there you go – no excuse now, get into those pivot tables, yep knock yourself out, you know you want to…

Pivot tables … not just for accountants
Tagged on:                 

One thought on “Pivot tables … not just for accountants

Leave a Reply