The Invention of the Pivot Table


The concept that led to today's pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if one could build a tool that could recognize these patterns, then one could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demoed its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993.

Pito Salas, inventor of the pivot table concept, is always working on cutting-edge products at http://www.salas.com.

The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv, rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries.

Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the ability to add calculated fields. Excel 97 also opened up the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.

CASE STUDY: Life After Pivot Tables

You have 40,000 rows of transactional data, as discussed in the previous case study. Your manager asks you to prepare a summary report showing revenue by Region and Model. Luckily, you have pivot tables at your disposal. Here are the steps you would follow today:

1.

Select a single cell in your dataset. Choose PivotTable Report from the Data menu. Click Finish. You are given a blank pivot table, as shown in Figure I.7.

Figure I.7. After three mouse clicks, you have a blank pivot table report. Three more mouse clicks to go.


2.

From the pivot table field list, drag Region and drop it where it reads "Drop Row Fields Here." Drag the Model field where it reads "Drop Column Fields Here." Drag the Revenue field where it reads "Drop Data Items Here." After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

Figure I.8. Drag three headings to the report, and Excel calculates your report.


If you are racing, you can actually create the report shown in Figure I.8 in exactly 10 seconds. This is an amazing accomplishment. Realistically, it would take you about 50 seconds at normal speed to create the report. If you are a spreadsheet wizard and are instead following the steps in the previous case study, the nonpivot table solution would take you at least 12 times longer.

Further, when your manager comes back with the request to add Market to the analysis, it takes just seconds to drag the Market field from the field list and drop it on the report, as shown in Figure I.9.

Figure I.9. Creating a new report with the Market field is as simple as dragging the field to the report.




    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net