Pivot tables are the quickest and most powerful way for the average person to analyze large datasets. No coding skills or mathematical brilliance are necessary—just the ability to point and click your mouse.
But don’t take our word for it. Pivot tables had a superfan in none other than Apple founder Steve Jobs, who immediately saw their genius.
In 1985, Jobs was forced out of his role as chairman of the board at Apple after failing to beat IBM in the business computer market. Fortunately, he was a stubborn man. Jobs immediately started the company NeXT, with the idea of taking on IBM once again.
As he developed the NeXT computer, which would launch in 1988, Jobs was looking for killer software programs to create demand for the product. From his experience at Apple, he knew that a good spreadsheet program could drive sales. Jobs credited VisiCalc, the first widely used spreadsheet software, for the huge success of the Apple II computer, released in 1979.
In his search for that need-to-have product, Jobs met with software company Lotus. The organization had already developed Lotus 1-2-3, a popular spreadsheet program that ran on IBM computers. It was in these meetings that Jobs would first stumble upon the “pivot table.”
Software developer Pito Salas was at the time working in research and development for Lotus, looking into how people typically utilize spreadsheets. Salas saw that users would often use spreadsheets to try to calculate summary statistics by categories (often referred to as crosstabs). For example, a company selling bicycles might want to examine their data to find unit sales by month or revenue by country. The way people did that at the time was cumbersome and error-prone because it involved writing complicated formulas.
Salas decided the world needed software that would make those calculations simple. Rather than enter formulas, users would be able to point and click to get those summary statistics. The Lotus team called this tool “flexible views,” but today similar tools are called “pivot tables” in both Microsoft Excel and Google Sheets.
The Lotus team showed Jobs an early prototype. “Steve Jobs thought it was the coolest thing ever,” Salas, now a professor at Brandeis University, tells Quartz. Jobs then convinced Lotus to develop the pivot table software exclusively for the NeXT computer. The software came out as Lotus Improv, and though the NeXT computer was a commercial failure, Lotus Improv would be hugely influential. The “flexible views” aspect of Improv would be built into both Lotus 1-2-3 and Excel (the latter was the first to actually use the term “pivot table”).
Bill Jelen, Excel evangelist and co-author of Pivot Table Data Crunching, credits Salas as the “father of pivot tables.” Salas says his contribution to pivot tables is one of his life’s most gratifying accomplishments, though he believes he was just building on the foundations of many others.
Today, pivot tables are among the most important and commonly used tools in the spreadsheet wizard’s toolbox. “A pivot table lets you create a one-page summary report from hundreds of thousands of rows of data, often in four, five, or six clicks,” says Jelen. “It is the fastest way to get answers from large datasets.”
It’s hard to know exactly how many people use Excel in their day-to-day work, but there are hundreds of millions of Excel users worldwide and it stands to reason that many have picked up the software’s most powerful tool. Pivot tables are generally listed at, or near, the top of lists of the most useful features in Excel. Data analysts use pivot tables to understand public health, economic growth and advertising effectiveness, among many other uses.
“That’s my life,” says Justine Shakespeare, a senior program manager at nonprofit labor rights organization Verité. “All I do is make pivot tables.” In her case, the pivot tables are to analyze survey data from interviews with migrant workers in global supply chains.
Here’s an example of the pivot table in action. Recently, a reporter at Quartz got access to data on remittances sent from the US to Mexico by month going back to 1995. But what the reporter really needed was a summary of the total remittances in the first six months of each year. Pivot tables were the way to go. Getting the numbers she needed involved inserting a pivot table, making a few selections to choose the data she needed, and filtering out the months she wanted to exclude. The video below shows those steps:
If you want to learn more about how to use pivot tables, try this video from “Excel ninja” Cody Baldwin. For those who prefer Google Sheets, try this introduction from Sheets expert Ben Collins.