Pivot cache

The Pivot cache is automatically created each time you insert a Pivot table, and it is an object that replicates the data source based on which Pivot table is being made. Each time you need to change the look of the Pivot table, Excel automatically calls the cache, simply because it’s much faster – it handles local data, stored in the computer’s memory. However, what happens when a synchronization error occurs?

Pivot cache is generated if the Save Source Data With File checkbox is selected in the Pivot table options. This action as a consequence creates larger Excel file, as data is being saved along with the report. But again, it’s not that bad because the report will work faster, especially if you have connected to a large dataset. Sometimes it happens that the cache stays within a file, although you have changed the data source. I will tell you my experience, so you could better understand what it is all about.

I run home finance in an Excel file. It consists of two worksheets, the first is a structured data table, and the second is a worksheet with a simple report. After all, you have more details in one of the previous “recipes”. In order for me to access it from multiple devices, Excel file is in the cloud and I synchronize it regularly with the computer. At the beginning of each year, I empty the table in the first worksheet, initialize it and begin to record transactions.

A few days ago, I tried to make a Pivot report, but I got some pretty strange information. Even though I created a report based on a table in which are transactions that occurred in 2018, I got an unknown values. By further analysis, I realized that it was about the data from 2017. I examined the source table in detail. The only logical explanation for this strange situation is that the data remained recorded in the cache, which occurred when I created a similar Pivot table report of previous year, so Excel used them to fill a new one.

The solution is very simple. You should go to the Analyze ribbon and choose the Refresh or Refresh All option (choose second option if you want to refresh the cache of multiple reports), and then Excel will then re-create the cache. This time report will contain current data.

These situations are common if you make reports after connecting to your company’s information system database. Please note that you always refresh your data before you begin to review the report.