I sometimes criticized Microsoft for lacking innovation, but I used to work there and so I was once part of the problem. There is a common quote at Microsoft that, after a few months, new employees become part of the problem. In my case, after I joined Microsoft, I argued unsuccessfully for the Excel team to move to C++ and object-orientation, which was resisted for performance concerns. I was a new hire from college, so my input carried no weight.

When I left Microsoft in 2000, I wondered whether I made any real difference. I didn’t know how to fight the system. Furthermore, since I wrote much of the PivotTable code, my exit resulted in a significant loss of PivotTable expertise, which may have resulted in few non-OLAP improvements in the past few releases.

After reading David Gainer’s post, I was able to discern a possible legacy of mine in changes made to PivotTables and the addition of Table structures in Office 12.

After arriving in Excel, I agreed to work on PivotTables, having already been familiar with Lotus’s innovative spreadsheet Improv. PivotTables had the same front-end and set of operations as Improv spreadsheets, but had different back-ends and goals. PivotTables was more like reports that summarized existing data, while Improv tables were human-friendly versions of traditional manually entered spreadsheet tables.

I then wrote a vision document predicting or advocating that, in future versions of Excel, various structures (database-like lists and tables) would automatically be recognized and that PivotTables would be logically integrated with the rest of the spreadsheet. (That is, regular spreadsheet tables and PivotTables would become more like each other.) My document was met again with skepticism as PivotTables were regarded as a marginal feature used by maybe 1% of users. Program management did seem slightly more receptive to my ideas than members of my development team.

When I wrote this document, PivotTables were the only recognized special structure in the Excel spreadsheet. Array formulas (used for matrix calculations) and data tables (used for what if analysis) were pseudo-structures specially handled by the Excel calculation engine, but still fundamentally part of the sheet, while PivotTable regions were a completely separate entity with its own distinctive set of operations. In later years, query tables (aka, external data ranges) and web queries joined the mix, followed by Lists in Mac Office and Tables in upcoming Office 12.

I identified a common set of operations—sorting, filtering, pivoting, subtotaling, outlining, structured selection, preserved formatting, English formulas—that would across all structures including PivotTables. This notion wasn’t entirely original as most of these operations already existed in Lotus Improv. Earlier versions of Excel introduced some of these features such as automatic subtotals, autofilters and outlining, but these features were awkward and only worked on the whole grid, rather than individual structures.

Unable to persuade others of my vision, I used whatever power I did have and engaged in guerilla tactics. Since I “owned” PivotTables, I could sneakily developed new features in to the product and then demonstrated a “prototype” to influence program management and testing. This only worked for features that required low development effort.

In this way, I introduced into PivotTables capabilities such as sorting and filter (AutoShow [originally, AutoFilter] and AutoSort) to match those found elsewhere in Excel. In retrospect, these were essential features to have in PivotTables but they almost never shipped. I was able to convince both PM and testing near code-complete to include these lightweight features that were going to be used far more often than the less-used, heavier and more complex features such as structured selection and calculated items.

I added structured selection in Excel 97, but it was annoying to have two different selection models, depending on whether the active cell was on a PivotTable; it also made PivotTables feel more separated from the rest of Excel. So, I fixed structured selection in the Excel 2000, so that it must first be invoked by clicking on the edge; the program manager followed lead and documented the behavior in the spec. The behavior was apparently copied over to Office 12’s “tables.”

Other features like merged cell support allowed PivotTables to resemble Improv tables. In subsequent versions of PivotTables, I decided to make PivotTables look more like traditional tables, so I implemented and then demonstrated Outline Layout with support for blank lines and subtotals at top. Unfortunately, my program manager mauled and buried the feature in the PivotTable AutoFormat dialog.

Outline Layout apparently was made more prominent in Office 12 with the new Ribbon UI. Apparently, some program manager wondered why Outline Layout, a more natural view, was hidden from users. I suspect also the feature’s existence conveyed the impression that PivotTable views were more malleable and led to Compact Layout.

Some features, which were “snuck in” by me but were later cut, made an apparent return in Office 12.

• Compact Layout. I demonstrated a variation of outline layout using indentation, but my original implementation differed from Office 12 compact layout by utilizing merged cells in order to retain compatibility with older versions of Excel and support drag-and-drop pivoting. The Office 12 implementation dispenses with drag-and-drop and can forgo compatibility with the new open XML format.
• PivotTable Names in Formulas. I included the ability to refer to PivotTables by name inside spreadsheet formulas. This feature appeared to be revived in Office 12 for table structures and possibly PivotTables.

Independent of me, PivotTables were becoming more server-based with OLAP functionality and SQL Server links. When I left Excel, I probably established another philosophical direction for PivotTable— the increasing unification of PivotTables with the rest of Excel. Some of the credit is shared by the PivotList Office Web Control team. I may also have some small part in the increasing recognition of structure of Excel.

There are still some predictions of mine that have yet to come to pass. Office 12 “tables,” for example, are still rigid and can’t yet be pivoted, but there is still Excel 13+.