{"id":23541,"date":"2022-01-26T11:03:06","date_gmt":"2022-01-26T10:03:06","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=23541"},"modified":"2024-02-06T16:15:54","modified_gmt":"2024-02-06T15:15:54","slug":"pivot-cache-explained","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pivot-cache-explained\/","title":{"rendered":"Excel Pivot Cache Explained"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25510\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache.png\" alt=\"Excel Pivot Cache Explained\" width=\"800\" height=\"450\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache.png 800w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-768x432.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><\/p>\n<p>Whenever you make a change to your dataset (updating data, adding more rows or columns), you have to manually refresh your <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Table\" data-wpil-keyword-link=\"linked\">Pivot Table<\/a> to have your changes reflected. Why is that?<\/p>\n<p>I&#8217;m going to explain to you the concept of the <strong>Pivot Cache<\/strong>. Join me in understanding this concept with some quick illustrative examples below!<\/p>\n<ul>\n<li><a href=\"#what-is-pivot-cache\"><strong>What is Pivot Cache?<\/strong><\/a><\/li>\n<li><a href=\"#sharing-a-pivot-cache\"><strong>Sharing a Pivot Cache<\/strong><\/a><\/li>\n<li><a href=\"#count-the-pivot-cache\"><strong>Count the Pivot Cache in Workbook<\/strong><\/a><\/li>\n<li><a href=\"#reduce-excel-file-size\"><strong>Reduce Excel File Size<\/strong><\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><em><strong>Exercise Workbook:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Pivot-Cache-Explained.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"what-is-pivot-cache\"><\/a>What is Pivot Cache?<\/strong><\/span><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Why is there a need to refresh the Pivot Table? Let us look at this sequence of events below:<\/p>\n<ul>\n<li>You invoke the creation of the Pivot Table<\/li>\n<li>Then a snapshot of that data in that point in time is stored in the Cache<\/li>\n<li>The Cache content is the basis of how the Pivot Table is created<\/li>\n<\/ul>\n<p>You do not see the <strong>Pivot Cache<\/strong> as this runs in the background.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone wp-image-23545 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-01-1.jpg\" alt=\"Excel Pivot Cache Explained\" width=\"1362\" height=\"623\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-01-1.jpg 1362w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-01-1-300x137.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-01-1-1024x468.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-01-1-768x351.jpg 768w\" sizes=\"(max-width: 1362px) 100vw, 1362px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Now what happens when you update the source data?<\/p>\n<ul>\n<li>Your source data is updated<\/li>\n<li>You invoke the refreshing of the Pivot Table<\/li>\n<li>Then a snapshot of that data in that point in time is stored in the Cache<\/li>\n<li>The Pivot Table is updated based on the Cache<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-23546\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-02-1.jpg\" alt=\"Excel Pivot Cache Explained\" width=\"1360\" height=\"606\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-02-1.jpg 1360w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-02-1-300x134.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-02-1-1024x456.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Pivot-Cache-Explained-02-1-768x342.jpg 768w\" sizes=\"(max-width: 1360px) 100vw, 1360px\" \/><\/p>\n<p>When you create Pivot Table in Excel, a Pivot Cache is <strong>created automatically<\/strong>. It is a <strong>replica of the original source data<\/strong> but it is not visible to us. When you make changes to the Pivot Table, it uses Pivot Cache and not the original source data.<\/p>\n<p>Even though this makes<strong> Excel very responsive and quick to change<\/strong>, it <strong>increases the file size<\/strong> to almost double.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"sharing-a-pivot-cache\"><\/a>Sharing a Pivot Cache<\/strong><\/span><\/p>\n<p>When you<strong> create multiple <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Tables\" data-wpil-keyword-link=\"linked\">Pivot Tables<\/a> using the same source data<\/strong> in a workbook, Excel creates a single Pivot Cache and re-uses it for all Pivot Tables.<\/p>\n<p>The <strong>advantage<\/strong> of sharing a Pivot cache is that it <strong>prevents duplication<\/strong> but it creates<strong> several disadvantages<\/strong> as well:<\/p>\n<ul>\n<li>When you refresh one Pivot Table, all other Pivot Tables get refreshed.<\/li>\n<li>When you group one field in Pivot Table, it gets grouped in other Pivot Tables.<\/li>\n<li>When you insert a calculated field in Pivot Table, it appears on all other Pivot Tables.<\/li>\n<\/ul>\n<p>If these disadvantages create a hindrance for you, you can simply create multiple Pivot caches for multiple Pivot Tables. To do so, follow the steps below:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1: <\/strong><\/span>Click anywhere on the data source and <strong>press Ctrl + T<\/strong> to create a table. Click <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25494\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-1.png\" alt=\"Excel Pivot Cache Explained\" width=\"859\" height=\"446\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-1.png 859w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-1-300x156.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-1-768x399.png 768w\" sizes=\"(max-width: 859px) 100vw, 859px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>Go to the <strong>Table Design<\/strong> tab, you will see the <strong>Table Name box<\/strong> displaying the name (Table1) assigned to the table.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25495\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-2.png\" alt=\"Excel Pivot Cache Explained\" width=\"990\" height=\"146\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-2.png 990w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-2-300x44.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-2-768x113.png 768w\" sizes=\"(max-width: 990px) 100vw, 990px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>Select<strong> Summarize with PivotTable<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25496\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-3.png\" alt=\"Excel Pivot Cache Explained\" width=\"992\" height=\"146\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-3.png 992w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-3-300x44.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-3-768x113.png 768w\" sizes=\"(max-width: 992px) 100vw, 992px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4: <\/strong><\/span>In the PivotTable dialog box, click <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-4.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25497\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-4.png\" alt=\"Excel Pivot Cache Explained\" width=\"469\" height=\"290\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-4.png 469w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-4-300x186.png 300w\" sizes=\"(max-width: 469px) 100vw, 469px\" \/><\/a><\/p>\n<p>Pivot Table and its cache will be created. Before you create another PivotTable, you need to<strong> covert the table back to range<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-5.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25498\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-5.png\" alt=\"Excel Pivot Cache Explained\" width=\"256\" height=\"235\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5: <\/strong><\/span>Go to <strong>Table Design<\/strong> Tab &gt;<strong> Convert to Range<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-6.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25499\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-6.png\" alt=\"Excel Pivot Cache Explained\" width=\"993\" height=\"146\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-6.png 993w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-6-300x44.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-6-768x113.png 768w\" sizes=\"(max-width: 993px) 100vw, 993px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6: <\/strong><\/span>Again <strong>convert the range back to a table<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-7.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25500\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-7.png\" alt=\"Excel Pivot Cache Explained\" width=\"850\" height=\"445\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-7.png 850w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-7-300x157.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-7-768x402.png 768w\" sizes=\"(max-width: 850px) 100vw, 850px\" \/><\/a><\/p>\n<p>Excel will assign a different name to the table &#8211; <strong>Table2<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-8.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25501\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-8.png\" alt=\"Excel Pivot Cache Explained\" width=\"991\" height=\"144\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-8.png 991w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-8-300x44.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-8-768x112.png 768w\" sizes=\"(max-width: 991px) 100vw, 991px\" \/><\/a><\/p>\n<p>Now you can create another Pivot Table using the data from Table2.<\/p>\n<p>Since the tables have different names, Excel will create separate caches for them!<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"count-the-pivot-cache\"><\/a>Count the Pivot Cache in Workbook<\/strong><\/span><\/p>\n<p>How to do how many pivot caches are present in your workbook? Excel can count them for you using a simple <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"VBA\" data-wpil-keyword-link=\"linked\">VBA<\/a> code.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1: <\/strong><\/span>Press <strong>Alt + F11<\/strong> to open the <strong>Visual Basic<\/strong> window.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-9.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25503\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-9.png\" alt=\"Excel Pivot Cache Explained\" width=\"790\" height=\"480\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-9.png 790w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-9-300x182.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-9-768x467.png 768w\" sizes=\"(max-width: 790px) 100vw, 790px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>Press <strong>Ctrl + G<\/strong> to open the <strong>Immediate<\/strong> window.<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-10.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25504\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-10.png\" alt=\"Excel Pivot Cache Explained\" width=\"656\" height=\"729\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-10.png 656w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-10-270x300.png 270w\" sizes=\"(max-width: 656px) 100vw, 656px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>Enter the following code:<\/p>\n<p><em>?ActiveWorkbook.PivotCaches.Count<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-11.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25505\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-11.png\" alt=\"Excel Pivot Cache Explained\" width=\"580\" height=\"133\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-11.png 580w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-11-300x69.png 300w\" sizes=\"(max-width: 580px) 100vw, 580px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4: <\/strong><\/span>Press <strong>Enter<\/strong>.<\/p>\n<p>The result will be displayed below the code!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-12.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25506\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-12.png\" alt=\"Excel Pivot Cache Explained\" width=\"580\" height=\"133\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-12.png 580w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-12-300x69.png 300w\" sizes=\"(max-width: 580px) 100vw, 580px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"reduce-excel-file-size\"><\/a>Reduce Excel File Size<\/strong><\/span><\/p>\n<p>The only issue that you face while using <strong>Pivot cache is that it doubles the size of the Excel file<\/strong>. This <strong>problem can easily be solved<\/strong> by <strong>deleting the original source data<\/strong>. This will reduce the file size significantly!<\/p>\n<p>The <strong>Pivot Table will remain intact<\/strong> and you can easily use it.<\/p>\n<p>If you want to <strong>get the source data back<\/strong>, <strong>double click on the grand total value<\/strong> in the unfiltered Pivot Table.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-13.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25507\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-13.png\" alt=\"Excel Pivot Cache Explained\" width=\"300\" height=\"257\" \/><\/a><\/p>\n<p>This will paste the original source data into a new worksheet for you.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-14.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Pivot Cache Explained\"  class=\"alignnone size-full wp-image-25508\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-14.png\" alt=\"Excel Pivot Cache Explained\" width=\"964\" height=\"443\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-14.png 964w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-14-300x138.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Pivot-Cache-14-768x353.png 768w\" sizes=\"(max-width: 964px) 100vw, 964px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Make sure to download our FREE PDF on the <a href=\"https:\/\/app.monstercampaigns.com\/c\/yk5u6nmubly3hlfehsco\/\" target=\"_blank\" rel=\"noopener nofollow\"><strong>101 Best Excel Tips &amp; Tricks<\/strong><\/a>:<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Whenever you make a change to your dataset (updating data, adding more rows or columns), you have to manually refresh your Pivot Table to have your changes reflected. Why is that? I&#8217;m going to explain to you the concept of the Pivot Cache. Join me in understanding this concept with some quick illustrative examples below! [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":25510,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[914,915,916],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23541"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=23541"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23541\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25510"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=23541"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=23541"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=23541"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}