Pivot table love

I love pivot tables.

These days I only really ever have one spreadsheet design – a bunch of input type stuff, some reference stuff, a big database sheet and then a bunch of pivot table reports.

That design came into its own recently when a client asked for an urgent reporting change. Instead of reporting by reporting line as agreed, their client now wanted everything by geography.

A quick check of the database sheets showed geo was in there so it was the work of a few minutes to totally change the whole reporting.

Of course I still scratched my chin, drew a sharp breath and made it sound difficult and expensive!




34 Responses to “Pivot table love”

  1. ross Says:

    …and then posted about on your website?! FAIL? :)))))

  2. Simon Says:

    well its a gamble isn’t it. take the glory for building to a good design and hope the client can see the value of that. Or squeeze as much cash out as often as possible. I try for the former, but everyone I deal with seems to be doing the latter to me.

  3. Doug Glancy Says:

    Simon, I’m just creating my first Access-fed reporting workbook which contains lots of pivot tables. The user can click a menu item in Access and it opens a template of the workbook, which gets the data from Data>Input External Data – No DAO/ADO required yet. It seems quite simple, just a few lines of code to automate Excel and away it goes.. Reading this and other blogs has provided lots of inspiration to get to this model.

    The project I’m working on requires charts and I’m not using pivot charts because of the loss of formatting when they’re refreshed. So I’m building a class that ties a table to a chart and parses the Label and Data ranges of the table to feed the chart source data. So far I’m pleased at the ability to access and manipulate the areas of the pivot table.

    Do you manipulate the pivot tables with code much?

  4. jonpeltier Says:

    I love pivot tables. They make data analysis easy, fast, and flexible, as Simon pointed out with his reporting change.

  5. Ross Says:

    >>I try for the former, but everyone I deal with seems to be doing the latter to me.

    Yeah, I know that feeling, I get embarrassed asking for money, I’m working hard to overcome that!

  6. Alex J Says:

    Same love here. The report dashboad I provide also gives the user a set of dropdowns to do some drilldown. The dropdowns set page fields on the full set of pivots and update the data displays and charts.

    IMHO, pivot tables are THE key function of Excel.

  7. Marcus Says:

    “a big database sheet”

    Tsk tsk, Simon shame on you. This is one of my pet peeves with spreadsheet design,

    I have unfortunately borne witness to spreadsheets 150 meg in size (and beyond). Here one sheet had a great wad of data with others containing a multitude of PivotTables, each with independently cached data. The spreadsheet file would then be copied to provide variations on how the data was viewed. (Perhaps this rant should have been posted under the Dinosaur Users entry).

    So while I produce plenty of spreadsheets with PivotTables, unless the volume of data is trivial, the data goes where it belongs; in a database (typically Access).

    Cheers – Marcus

  8. Simon Says:

    one cache is a small price to pay for single file deployment in this case (IMO).

  9. Mike Staunton Says:

    Fortunately I need to use pivot tables and Access relatively rarely for the sort of numerical calculations I do – and I’m planning to give a talk on the ten most useful features of Excel without including pivot tables (so, Alex W, we’re a broad church here) – on the other hand, I really use data tables a lot

  10. Charlie Hall Says:

    I too like Pivot Tables and use them a lot

    I have found that when comparing year over year, or year vs budget, the pivot table does not produce the most effective report layout – so I have a macro that on refresh captures the columns of interest, and copies them off to the right, and hides the columns of the pivot table – or joins them to the pivot table, if some columns from the pivot table look great on their own. This has made pivot tables far more powerful and useful for my clients. One other customization for clients is to add a simpler user interface – many casual users of excel (like sales people) find the pivot table UI to be daunting – so adding a toolbar to get them the reports they like has worked well.


  11. Charles Says:

    IMHO Pivot Tables are Excel’s best feature (err… apart from Excel’s ability to calculate things that is).

    Now if we could just use UDF functions in calculated columns and items …

  12. Harlan Grove Says:

    I’m with Mike – I rarely use pivot tables.

    IMO array formulas are Excel’s best feature.

  13. Gordon Says:


    While I have a few projects that rely totally on array formulas, I don’t like them as I find them very slow when combined in any great number.

    Pivot tables are undoubtedly a very powerful feature, with one limitation that I find myself coming up against again and again: inability to use > & < operators in GETPIVOTDATA. For this reason I often find myself querying the same data that I have in the pivot table via SQL as it can produce the results I need.

  14. Building Models with Pivot Tables « Number Cruncher Says:

    […] 5, 2009 Pivot Table Love @ Smurf on Spreadsheets This is a really interesting post, mainly because of the discussion it’s generated – […]


    I started working with Pivot Tables back in Excel 5 when they first showed up. At the time an MS Marketting manager here in Canada said “They come from the future!” (which kinda scared me because he seemed to believe it ;-) ) …….

    I am a HUGE fan of Pivot Tables and use them in every data-related Excel app and most of the time add Excel PTs to my Access apps as a report option.

    Once again I am disappointed that MS has made zero effort to promote the capabilities of PTs, especially with external data (which I agree is the way to go most of the time Simon – sorry – and I know you DO agree with that despite …… whatever).

    An understanding of PTs would go a long way to getting users to understand database concepts (like relational theory) and would help “lockin” for Excel in organizations.



    “I often find myself querying the same data that I have in the pivot table via SQL as it can produce the results I need.”


    Yes I usually do all the heavy lifting in the SQL of the data source for my PTs. Just bring in data Transformed already. It just makes things cleaner to me….


  17. Eddie Tam Says:

    Have you people know about PALO – the opensource OLAP tool for Excel?


    From my personal experience, this tool is far more powerful than PT.


  18. Harlan Grove Says:

    If you make spreadsheets for reporting, then pivot tables may be very useful. If you make spreadsheets for decision support (little data, lots of calculations, possibly some stochastic simulation), pivot tables are seldom useful.

    As for array formulas, those and udfs (originally in XLM way back in the 1980s) were what hooked me on using Excel. They offerred a level of flexibility and structure not available in other spreadsheets. To me little better than using autorecalculating formulas in Excel to do things that required menu commands in 1-2-3. Speed was never an issue: if speed were essential, spreadhseets NO MATTER WHICH ONE were NEVER the proper platform.

  19. Simon Says:

    Sadly Palo has been on my list of ‘things to take a proper look at one day’ for a very long time, one day maybe.

  20. Eddie Tam Says:


    How long does it take to do a download and install?

    This is the download page:
    (Just pick the Palo 2.5 Client & Server for Windows)

    And a demo spreadsheet:

  21. Giles Says:

    Pivot tables are great, but the last time I used them in Excel, you couldn’t use arbitrary functions to aggregate data – just sum, average, etc – and they didn’t refresh automatically when you changed the underlying data. Sounds like the first one of those problems is still there, but does anyone know if the refresh problem has been fixed?

  22. jonpeltier Says:

    Giles –

    The first item is a problem, not being flexible in the calculated fields that can be used in a pivot table and not being flexible enough in the output calculations.

    The second, waiting until the user says update before it updates, is no problem at all. Once you make this automatic, you’ll have the data churning and the CPU creeping up to that magical 100% mark. The numbers will move as the analysis proceeds, and you will lose the ability to treat this set of numbers right here as a snapshot.

  23. Giles Says:


    I know what you mean about the usefulness of a snapshot, but why just for pivot tables? After all, if you update a number or a cell, every other cell that depends on it is updated, sucking up CPU power and making the numbers move as your analysis proceeds — so you lose the snapshot just by using Excel normally. If you want to avoid this happening, you can always switch automatic recalculation off.

    So to better phrase my description of the problem: pivot tables are inconsistent with the rest of Excel.



  24. Giles Says:

    Sorry, I meant “if you update a number or a formula”

  25. Mike Staunton Says:

    I try to ensure that my spreadsheets have no static calculations such as fourier analysis using analysis toolpak – each time I change the underlying data, I want my spreadsheet to automatically update

    So what happens to pivot table outputs when the underlying data changes – do the outputs stay the same (bad) or change (good)?

  26. jonpeltier Says:

    Mike – Changing the underlying data has no effect on the pivot table until the pivot cache is refreshed.

    Giles – I guess I’m so used to the way Pivot Tables work, the I don’t find them inconsistent at all. In fact, web queries need to be explicitly updated. And pivot tables can be based on external data sources. Do we want these to be updated continuously? It might be convenient, but think of the overhead to monitor the external data source to see whether it may have changed since a second ago.

    The worksheet is a special case, in that it can signal Excel that its data has changed. Not making use of this characteristic makes the pivot table’s treatment of internal data consistent with how it treats external data.


    “So to better phrase my description of the problem: pivot tables are inconsistent with the rest of Excel.”

    I don’t think that is true and it’s unfair to Pivot Tables :-) …… I would asusme that in 99.99% of cases the Pivot Table’s data source is going to be a Snapshot at a point in time. To update the Cache you just need to Refresh the PT, which you can do as often as you want I suppose (you could even tie a Refresh to the “Worksheet_Change” Event on the Variable Worksheet if the performance was adequate).


  28. Mike Staunton Says:

    Jon and Dick

    Thanks for your answers – what concerns me most is the potential for errors that can arise from the underlying data changing but the pivot table output remaining unchanged


  29. Charlie Hall Says:

    In most applications where pivot tables are most useful, the underlying data is not changing that dynamically – for example, say you are interested in analysing your companies current sales performance. Here is a tool that can slice and dice it in any way you can dream of, and do it quickly – without any programming. You can look customer sales trends (by month), do a comparison to last year’s data, compare to budget numbers, segment by product within the customer, or switch it around and look at customers within a product segment. Of course it depends on the underlying data, but if the data is there, the pivot table can get you instant understanding of that data. And this is with a fully flexible tool that can be applied to most any data – what could be more consistent with Excel.

    Further, if you want to use pivot tables to analyze dynamic data, with a little bit of vba, you can refresh the data automatically on sheet activation, or even on changes to the underlying data – it might lead to performance issues, but it is doable.

    And lastly, one can jump directly into Pivot Charts to see the trends more clearly – yes, pivot charts have their weaknesses, but for an interactive analysis tool, it is hard to beat.


  30. jonpeltier Says:

    Mike –

    Such an error would occur as well if the data were from an external source.

  31. Simon Says:

    I think Giles makes a good point, and one that I have totally overlooked.

    I am not a fan of manual calc, I have always felt it goes against what spreadsheets are for, and is a key indicator that something might have been better developed in a different technology. Automatic calc and instant, constant updates are my idea of what a spreadsheet should be. Others have other ideas and that fine.

    And actually in many of my models I do battle to keep the pivot cache up to date by trapping all sorts of events in the off chance I can catch any changes.

    I think Charlie has it, in that pivot tables are often used for fairly static data. So perhaps I’m abusing pivot tables by trying to use them on interactive data – oh well.

    One of the things I like about the Reuters feeds is they note the time of last update – it would be great if pivot tables had that automatically say in the top right corner, or even just in the right click properties.

    Jons points about snapshots is interesting as it is one area where spreadsheets are very weak. There are lots of additional tools to allow you to drill back through the history of what a certain cell has contained, but nothing native. I am excluding change tracking on the basis that its so crap I have never relaly used it. Someone who knows more might like to comment?

  32. Augusto Albeghi Says:

    Pivot tables are the MS low cost solution for data analysi. Given you have Excel in house already, they eliminate the need for another data analysis tool.
    They do not address, though, the need for refreshing complex layouts which are more adherent to people menthals schemas.
    That was one of the key movers to build Viney@rd.

  33. P Verco Says:

    If I want a pivot table to refresh, I use vba with auto-open so it refreshes when the page is opened. For Financial Reporting, manual refresh is usually more appropriate, and I most often run a SQL query into a pivot table with parameters that are selected manually (and via vba) to provide choice.

    I’ve also used Palo to create an MIS solution for a european business with £200m turnover. It provides excellent database capabilities for excel, and is extremely intuitive in use. I’d recommend it to anyone.

    That was immediately after I showed them how to substitute their UK mainframe print reports for live, on-screen reports using Pivot tables and arrays – all without any additional software investment.

    You can do surprisingly large projects, just using Excel and Pivots.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: