Let’s take a look. Here we have an Excel table that contains almost 3000 rows. This is sales data, and each row represents one order, for one kind of chocolate, to one customer. Using this data, let’s build a pivot table and see what happens if we remove the source data. The moment a blank pivot table is created, Excel generates the pivot cache. As we add fields to the pivot table, we are actually working with the pivot cache. We can prove this by changing the first row of the source data. Let’s change extra dark chocolate to mint chocolate. Back in the pivot table, we see no change. Mint chocolate is nowhere to be found. However, when we refresh the pivot table, we see mint chocolate appear. That’s because refreshing the pivot table rebuilds the pivot cache. Now let’s remove the data tab from the workbook. At this point, you might think the pivot table would break, but that’s not what happens. Thanks to the pivot cache, the pivot table works just fine. Removing the source data will reduce the file size significantly. If we save this workbook with a new name, the new file is a lot less than half the size of the original. But what if you want to see the source data again? In that case, you can simply use a pivot table’s drill down feature to recreate the source data. Just double-click the grand total, and Excel will create a new worksheet that contains the full set of source data.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
