Excel pivot cache madness

An archaic and terrible corner of Excel.

This is exactly the sort of thing I don’t like about Excel; an archaic, 2003 / 2007 “gotcha” that is common, baffling, and a pain.

tl;dr Excel shares a hidden cache between pivot tables so changes to one pivot table can affect others.

The problem goes like this.

If I create a table, enter a bunch of data, and create a pivot table off the initial table, then Excel silently creates a pivot cache to optimize any analysis for the pivot table. So far so good, right? Well, not really.

The pivot cache – something the Excel itself knows about but never communicates by name to the user – is associated with the table, not the pivot table. If I create a second pivot table off the first table (which is a very common use-case, by the way), Excel is using the same pivot cache, so when I, for example, apply a grouping to the second pivot table, it also applies to the first. If I ever want to create one pivot table to show an analysis on a day-over-day grouping, and another on a week-over-week grouping, I can’t without several kludgy workaround steps, and even then, on macOS it appears to not work correctly at all.

What’s more is that it does all of this silently. If I create my two pivot tables in different workbooks (which, again, is super common), then I won’t even see that my second week-by-week grouping is applied to my first pivot table.

What is the solution to this absurd UX issue? Either create a second table, pointing to the data in the first and use that, or uncheck an option to “Save source data with file” and restart Excel, which only seems to work sometimes.

But let’s back up here.

Why is Excel creating the pivot cache in the first place? It does this so when you’re goofing around in the pivot table, it can cache things, so results are fast, and you use less memory.

Firstly, if I wanted to use less memory I wouldn’t be running Excel at all. Secondly, memory consumption should be the programmers job to manage, not mine as the end user of a software product. Finally, if I want to create multiple pivot tables and use a bunch of storage or memory it’s because I’m doing something necessary, so the program should use whatever resources it needs to achieve my task.

(As a side note, this is not dissimilar to how Safari will complain that “the page is using a significant amount of memory, and your computer may be more responsive if you close the page.” To me this is funny. The program is telling me that the computer would work better if it doesn’t have to do work at all. A line of thinking that seems more like a Garfield comic than a serious piece of software.)

So in the end, Excel seems to be optimizing for faster results at the expense of a spreadsheet that does what you think it does.

Footnotes:

excel | spreadsheets

2023-01-24