Excel Pivot Tables
Some people suggest Pivot Tables are the most important software since the spreadsheet. Some people can't see the point of them at all.
Codematic is firmly in the first category. Pivot tables are a genuinely incredible piece of technology. Those that do not see the need may be using spreadsheets in a different way, or they may just need to see them in action.
|Possibly the most useful aspect of pivot tables is the design discipline they enforce by separating the actual data from the view(s) of the data. Many people are blocked from experiencing the true power of pivot tables because their data is already half pivoted. The number one barrier I see to pivot table adoption is data that is laid out in a report format rather than in a flexible list.
|The most obvious benefit of using pivot tables is reporting flexibility - you can view your data grouped any way you like all with a simple drag, drop, point and click interface. This leads to a further benefit - spreadsheets with pivot tables are more simple than intensive formula driven reports. The reduced complexity leads to probably the most important benefit - improved reliability. Pivot table based spreadsheets are much more straightforward to test than formula based spreadsheets. With reported material error rates of 40% to 90% in commercial spreadsheet reviews, there is plenty of space for improvement. If the data is not accurate is it worth reporting? should there be a warning?
|Most of our developments have incorporated pivot tables for many years now, often alongside formula driven reports. We will never force a solution into a pivot table if something else is more appropriate, but that seems pretty rare.
A simple rule of thumb that helps people design their data for pivot tables is just to have 1 column with values in, all the other columns should then describe that number. Many people work in a tabular format with say months in columns - that is already part pivoted data and will not pivot properly. A better approach, although it seems to use lots of rows, is to have a column called month with months repeating as required, and a column called value. A simple example workbook is here.
Here is a short review of some good pivot table books.
Please contact us with any questions.
25 January 2012 - UK Excel Developer Conference - London
Products for sale:
New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)
Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)
Classic Ribbon Tab
Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)
Products coming soon:
(Find and control external links in Excel Workbooks)
Due by Q1 2111.
(Excel VBA based spreadsheet auditing tool)
Due before the end of 2111.