Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Excel User Confs
Consultant Profile
Book Reviews
Site Map

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.

Quick advice:

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.


Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London

Products for sale:


Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)

Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011