Why Pivot Tables Never Die

Metadata

• Author: Simon Späti
• Full Title: Why Pivot Tables Never Die
• URL: https://www.rilldata.com/blog/why-pivot-tables-never-die

Highlights

• While everyone’s talking about AI revolutionizing business, there’s a quiet renaissance happening with one of the most influential business tools created: the pivot table. (View Highlight)
• In 2025, we’re witnessing something remarkable - modern data tools are bringing pivot tables back to the forefront. But why would cutting-edge platforms invest in a decades-old spreadsheet feature? The answer lies in what made pivot tables revolutionary in the first place: turning complex data into instant insights without writing a single line of code. (View Highlight)
• A pivot table is a separate sheet, usually in spreadsheet-style software, that aggregates and summarizes tables (within the spreadsheet, databases, or business intelligence programs) elegantly and simply. It’s a WYSIWYG editor, like a REPL for tables. You can drill and slice; you get direct feedback, and it’s fast and instant. Compare this to an SQL query that you must run before getting any results. (View Highlight)
• A pivot table lets you drill down, add, and remove dimensions and metrics (count, sums) quickly, and you can explore the data on the fly. It’s easy to use and get a feel for the data in front of you. Core Components and Functionality It has four key components:
  1. Row and column dimensions that define how data should be grouped
  2. Filters for selecting only the data you need
  3. Values specify what aggregate calculations (SUM, COUNT, AVG) should be performed on the grouped numerical data. (View Highlight)
• This structure allows users to “slice and dice” complex datasets without understanding the underlying data architecture or writing complex formulas. It’s rigid in its form but flexible in its application. With pivot tables, users can explore how business metrics like revenue, activity per user, and transaction volume vary across different dimensional contexts such as geographic region, fiscal period, or industry.Its clear boundaries and constraints on the four components make it easy to understand. Companies can implement pivot tables in every tool in the same way. For that to work, no prerequisites exist except that the input data must be tabular. (View Highlight)
• In one image, the evolution looks like this, with a long period of double-entry bookkeeping as the way of doing accounting, up to the inception of pivot tables and the domination of Excel, until today with modern pivot tables. (View Highlight)
• Excel first introduced the spreadsheet. No, wait. That’s not true. Two tools preceded it, but let’s back up. As the pioneer of creating the first spreadsheet software in 1979, VisiCalc is also cited by many as the “killer app” for personal computers. It allowed users to create, manipulate, and save rows and columns of data in a dynamic format and boosted the sales of the Apple II. A little later in 1983, the famous Lotus came along on the IBM PC. It was much faster, had integrated charting and graphics, and had basic database capabilities (hence the name “1-2-3”). Lotus 1-2-3 quickly became the leading spreadsheet application. (View Highlight)
• Later, in 1985, Excel was introduced. It was initially released for the Apple Macintosh (!) due to the Mac’s more advanced graphical capabilities at the time. Around that time, Pito Salas invented the pivot table while working with Lotus’ Advanced Technology Group in 1986. It was a next-generation spreadsheet concept that was released by Lotus in 1989, as Lotus Improv revolutionized spreadsheets.

  Allowing analysts from fifteen minutes of complicated data table and database functions to “just seconds” of dragging fields into place. Born were the first data analysts. (View Highlight)

• But how did the pivot table emerge, you might ask? As briefly explained, it was created in 1986 and released three years later as part of Lotus in 1989. (View Highlight)
• With the growth of digitalized double-entry bookkeeping, companies had more data, and over the months and years, accountants needed a way to analyze these data meaningfully. A basic spreadsheet could store the transactions, but analyzing trends and patterns and creating reports was still manual and time-consuming. (View Highlight)
• Pivot tables allow one to summarize and aggregate a large dataset of transactions quickly or analyze revenue and expenses across multiple dimensions, so-called slide-and-dice”. Instead of pre-calculating everything, drill down from summary to a details level within a meeting. Making it the first self-serve tool for users. (View Highlight)
• Sounds Familiar? The problems today are vastly similar to those with Excel. The difference is that the data got more significant, and the compute got faster and cheaper. Today, we usually use OLAP cubes that allow us to drill down on the fly, compared to long pre-calculus or long or night ETL loads. (View Highlight)
• A pivot table offers powerful data aggregations that transform detailed raw data into meaningful, high-level insights through automated summarization and statistical analysis. It can take source data from databases, spreadsheets, or business intelligence systems, and you can easily create well-structured data analyses grouped into discrete categories. Aggregations are typical sums, sums, averages, and counts. These help analyze and see underlying patterns with interactive data analysis on raw data. (View Highlight)
• Think of a pivot table as a dynamic cross-tabulation engine. It can rapidly reorganize (“pivot”) large datasets to answer specific business questions. For example, a table containing thousands of sales records can be instantly transformed to show total revenue by product category across different regions or average order values by customer segment over time. The power comes from its interactive nature. We can easily drag and drop different fields to restructure the analysis on the fly, making it an invaluable tool for data exploration and business reporting. (View Highlight)
• If you want, the “OG” of Excel might be its graphical user interface (GUI) and dashboard, which enabled less tech-savvy people to start with data analysis. However, with the explosion of Excel and the domination of the market for so long, the biggest business intelligence tool to this day and immortal? Potentially the most influential software ever built. (View Highlight)
• VertiPaq, later renamed xVelocity, is another star in the background integrated into Excel (and later Power Pivot, SSAS Tabular, and PowerBI). It was an in-memory columnar engine. When you load data into a data model, it is loaded, compressed, and stored in RAM using the VertiPaq engine. (View Highlight)
• Today, we talk about equivalents like Apache Arrow and others, but VertiPaq was back in 2006 (!). The Apache Software Foundation announced Arrow on February 17, 2016, although the initial codebase and Java library were seeded by code from Apache Drill. Drill, on the other hand, was first released in 2014 and stemmed from Google’s Dremel Encoding, an interactive analysis of web-scale Datasets in 2011. You can see how far ahead Microsoft was at that time. (View Highlight)
• We humans use what we know, what is intuitive. It’s human to want to control it. Artificial intelligence and complex data engineering have made the business more suspicious. If you are not the one building it, it becomes increasingly a black box to you. (View Highlight)
• If we can’t understand and can’t control it, we also trust it less. Trust is a human instinct that goes far. Once the trust in your data is lost, it’s nearly impossible to get it back. (View Highlight)
• The question is, how do we build intuitive data tools? Humans, especially business people, want to use tooling and processes instinctively and trust. (View Highlight)
• A key factor for sure is simplicity. The easier a tool is to use and understand, the more we feel in control and the higher trust we get. But how do we achieve this? Through thoughtful constraints. (View Highlight)
• Take BI tools as an example - while powerful, they often lead analysts down a rabbit hole of endless customization. Hours slip away adjusting colors, pixel-perfect positioning, and fussing with logos rather than uncovering the insights that drive business value. The freedom to customize everything often becomes a burden that distracts from the core purpose: understanding our data. (View Highlight)
• But if you take Rill Developer, it is an intuitive BI tool because it has built-in constraints. You can’t change the default line chart much. That’s a strength. Of course, you might want to customize many things for various reasons, so it might not be the right tool. But every tool should have a set of reasonable constraints, and by defining them well, you take a lot of mental overhead away from people using it. Is it worth spending hours perfecting a dashboard’s aesthetics when you really need to understand how your company is performing? I will let you answer this question for yourself. (View Highlight)
• The pivot table aligns with these principles. Most of us have used it extensively, and there were reasonable constraints from the start, working with tabular data and dragging and dropping columns to the four boxes. It makes us believe we are under control. We can choose the dimensions and the measures. All of these make it easier to use. (View Highlight)
• On top, it standardizes the process. Like double-entry bookkeeping or taking a Dockerfile, it lets us do many things but has clear rules. However, these constraints let us deploy on any Kubernetes Cluster, cloud-agnostic, AWS, Google, or Microsoft; it does not matter. The same is happening with pivot tables, as they evolved into such a well-defined standard; modern tools, especially in business intelligence dashboarding tools, can now integrate pivot tables into their BI tool, and every user knows how to use them—benefiting immediately from the REPL-like exploration. (View Highlight)
• But what exactly is standardized with pivot tables? It standardizes around the concept of measures and dimensions. If you want to provide a pivot table, you need to provide a tabular table with metrics and possible dimensions to drill down, such as a time dimension or date field if you want to analyze over time. Defining that you need one date column for a line chart over time is very clear and intuitive for us to use. (View Highlight)
• «The Human Factor» of data, and what makes an intuitive data tool. A tool is intuitive if we are familiar with it, if it’s simple, if we can control it, and if we trust it. We achieve this by having well-defined, built-in constraints, leading to standardization across the board. (View Highlight)
• We often think more complex problems require more sophisticated solutions, but as The Unreasonable Effectiveness of Data Paper suggests, the opposite is the case. The more complex, the more we should focus on simple models with massive data. (View Highlight)
• The only thing that changed one or two decades ago is the scale. And that changes everything. The paper suggests that there is often a “threshold of sufficient data” where the system’s behavior fundamentally needs to change. What does not work with 1000 examples all of a sudden works with millions or billions? What is essential here is that with larger data, speed and quick response time is key. So, we need a strong backend. And that’s why OLAP cubes never die. They are the personification of pre-calculated calculations and sub-second responses. (View Highlight)
• That’s why pivot tables are returning. These are simple, and everyone understands how to use them, yet they are powerful in their interactive analysis. And with fast OLAP or cloud data warehouse backends, we can achieve Excel-like pivoting. (View Highlight)
• Growing data also makes Excel less sexy. Excel is slow on a massive scale, local (can’t share with people), and it needs manual labor. Modern pivot tables, integrated into BI tools, usually come with an automated ETL process; they are fast as a strong backend powers the reports, caching is in place, and all the things you’d want for a fast, interactive pivot table. (View Highlight)
• Some examples: Rill has introduced Pivot Table into Rill Developer. As well as Cube, improving spreadsheet analytics through pivot tables. DuckDB Pivot Tables are also used through the Excel pivoting API and creating pivoting-style analytics for its users. (View Highlight)
• As we can see, the look and feel are 1:1, the same as the initial Libre Office or Excel interface. What does that mean for the BI tool or the data model? Does it mean the data needs to be in tabular format, and we need to define measures and dimensions, or is it automatically based on data types and some enhanced AI? For example, Rill does it automatically when you create a model or dashboard based on your data sources, enhanced with AI, called GenBI. Read more on The New Era of GenBI. (View Highlight)
• The enduring power of pivot tables is their robustness, simple usage, and fast, interactive response. It’s the Lingua Franca of data if you are not fluent in the language of SQL or Python. A common language everyone understands: the top management, domain experts, and developers. It’s an interface to data; it’s the first no-code interface. (View Highlight)
• (View Highlight)
• Every chart starts with an analysis, self-serving exploring of data, so why not use a pivot table? Pivot tables would allow every user in the organization to build dashboards instead of only power users. Removing the demand to understand the details of creating dashboards, multiple BI tools, the data model, or the tools, maybe even DAX or other data modeling languages, to every user in the business organization. Truly self-serve. Moving the pivot table from local spreadsheets to BI tools makes data analysis more approachable. Similar to what Excel does, but now in a web app and to easily share with everyone. (View Highlight)
• The questions are the compute and the data model. What is transforming and aggregating my data in seconds so that I can explore the data on the fly? Who and how do we create a simple data model that works for everyone? (View Highlight)
• For the computer, it is usually a solved problem. We have DuckDB and Rill for local development, or we go into cloud data warehouses such as Snowflake, BigQuery, or Microsoft Fabric with its VertiPaq in-memory engine. Databricks’s Photon Engine has a SQL interface and works on distributed files. Or using an OLAP system if you need sub-seconds and a high volume of events. There are many more options, but crunching the data isn’t the challenge these days anymore. (View Highlight)
• However, creating a useful data model is still hard as you must understand the data and the business in and out. But this is where pivot tables help enormously. You can learn to understand your data by exploring. Based on that, you create better data models and architecture for your data warehouse or platform. (View Highlight)
• The goal of business intelligence is to focus on the value, not the tooling. BI visualizes and makes extensive data understandable for humans in a split second. The best example is the airplane cockpit, which shows the pilot all the most needed KPIs in one cockpit. The challenge is to make this even easier, such as querying the data by asking free-flow questions or writing in plain English. (View Highlight)
• Future BI needs to make GenBI a first citizen in BI tools. Making the interface more simple and more intuitive, with fast response times. One approach is to return the pivot table. (View Highlight)
• Adding that with AI capabilities, it could be the first chance for AI to explore the data within a reasonable time, as AI has a hard time processing the raw data on the fly, but with a REPL like a pivot table, getting quick responses that AI could interpret, that could be another approach to make BI and AI work together very well. BI is the “human-eyes” of data. We can get there using the GenBI and pivot table. (View Highlight)
• With the era of data engineering, responsible for reliable data for business and AI drive workloads, pivot tables, and business intelligence, in general, will profit most from AI, specifically GenBI. With the explosion of tools and fragmentation, BI is the common interface for non-technical people who need results and insights. (View Highlight)
• With the help of never-dying OLAP backends, I predict that pivot tables as quick REPL for AI to retrieve data and try to understand them might be the next big thing in the years. Imagine pivot tables running natively on data lakes, direct-querying S3 files through your BI interface, with no databases required. (View Highlight)