{"id":7944,"date":"2020-07-05T00:25:50","date_gmt":"2020-07-04T22:25:50","guid":{"rendered":"https:\/\/www.myexcelonline.com\/blog\/?p=7504"},"modified":"2024-02-06T15:48:11","modified_gmt":"2024-02-06T14:48:11","slug":"clear-delete-old-pivot-table-items","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/clear-delete-old-pivot-table-items\/","title":{"rendered":"Clear &#038; Delete Old Pivot Table Items"},"content":{"rendered":"<p>Unable to <strong>Clear <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> Cache Memory<\/strong> ??<\/p>\n<p>Have you ever cleared, deleted, or replaced your Pivot Table data\/items but they still show inside your Pivot Table filters?<\/p>\n<p>What gives??<\/p>\n<p>Well, you can easily clear your Pivot Table&#8217;s old items from your Pivot Table&#8217;s memory or cache<\/p>\n<p>In this article, you will be provided a guide on:<\/p>\n<ul>\n<li><strong><a href=\"#how-to-clear-pivot-table-cache-memory\">How to Clear Pivot Table Cache Memory<\/a><\/strong><\/li>\n<li><strong><a href=\"#change-default-setting-of-retain-items\">Change Default setting of Retain Items<\/a><\/strong><\/li>\n<li><strong><a href=\"#conclusion\">Conclusion<\/a><\/strong><\/li>\n<\/ul>\n<h3><a id=\"how-to-clear-pivot-table-cache-memory\"><\/a>How to Clear Pivot Table Cache Memory<\/h3>\n<p>Watch it on\u00a0<strong>YouTube<\/strong>\u00a0and give it a thumbs-up!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/sU0y3OvG670?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<\/div>\n<p>You might have faced this issue before. You have deleted or replaced old data from the data table but even after refreshing the pivot table, the <strong>data is still visible<\/strong>.<\/p>\n<p>This is because the old data is <strong>stored in the cache memory<\/strong> and displayed in filter selections even if there is no data for it at all. This can be confusing as well as annoying.<\/p>\n<p>Let&#8217;s look at an example and understand How to Clear Pivot Table Cache Memory!<\/p>\n<p>So, you have created a Pivot Table using the original data source containing the years 2012, 2013, and 2014.<\/p>\n<p>The <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener noreferrer\">Pivot Table<\/a><\/strong> will look like this :<\/p>\n<p class=\"line-two\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-01.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7506\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-01.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"411\" height=\"316\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-01.jpg 411w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-01-300x231.jpg 300w\" sizes=\"(max-width: 411px) 100vw, 411px\"><\/a><\/p>\n<p>Now, you change the year 2012 in your data source to 2013 and the same is reflected in the Pivot Table as well.\u00a0 But, the year 2012 is <strong>still visible in the Pivot Table&#8217;s filter<\/strong> selection.<\/p>\n<p>This is because the old item is still saved in the Pivot Table cache memory. Let&#8217;s learn how to fix it!<\/p>\n<p><strong><span style=\"color: #ff0000;\">Example 1:<\/span><\/strong><\/p>\n<p>Follow the step-by-step tutorial on <strong>How to Clear Pivot Table cache memory<\/strong> and make sure to download the exercise workbook to follow along:<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><strong><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Delete-Old-Pivot-Table-Data.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Delete-Old-Pivot-Table-Data.xlsx<\/span><\/a>\u00a0<\/strong><\/p>\n<\/div>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span>\u00a0Below is our data source and we want to replace the year 2012 with 2013, effectively only showing the years 2014 &amp; 2013.<\/p>\n<p>Go to\u00a0<strong><em>Home &gt; Find &amp; Select &gt; Replace<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7507\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"1276\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02.jpg 1276w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02-300x97.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02-1024x331.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-02-768x248.jpg 768w\" sizes=\"(max-width: 1276px) 100vw, 1276px\"><\/a><\/p>\n<p>Let us\u00a0<strong>replace\u00a0<\/strong>the\u00a0<strong>year 2012\u00a0<\/strong>with the\u00a0<strong>year 2013.\u00a0\u00a0<\/strong>Click\u00a0<strong>Replace All.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-03.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7508\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-03.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"443\" height=\"194\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-03.jpg 443w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-03-300x131.jpg 300w\" sizes=\"(max-width: 443px) 100vw, 443px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong>\u00a0<\/span>Go back to your Pivot Table.\u00a0\u00a0<strong>Right click\u00a0<\/strong>and select\u00a0<strong>Refresh.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-04.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7509\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-04.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"586\" height=\"370\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-04.jpg 586w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-04-300x189.jpg 300w\" sizes=\"(max-width: 586px) 100vw, 586px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>We have technically deleted the year 2012 records, so they should be gone from our Pivot Table, right?<\/p>\n<p>Hmm.. Looking good, the<strong> year 2012<\/strong> is now gone from our Pivot Table!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-05.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7510\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-05.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"333\" height=\"324\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-05.jpg 333w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-05-300x292.jpg 300w\" sizes=\"(max-width: 333px) 100vw, 333px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>BUT WAIT!\u00a0<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>Clicking on the\u00a0<strong>Column Labels\u00a0<\/strong>drop-down list, the <strong>Year 2012\u00a0<\/strong>is still there!\u00a0 Bloody hell!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-06.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7511\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-06.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"451\" height=\"283\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-06.jpg 451w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-06-300x188.jpg 300w\" sizes=\"(max-width: 451px) 100vw, 451px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong>\u00a0<\/span>Let us fix this!\u00a0 Go back to your Pivot Table &gt;\u00a0<strong>Right click\u00a0<\/strong>and select\u00a0<strong>PivotTable Options.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-07.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7512\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-07.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"416\" height=\"382\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-07.jpg 416w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-07-300x275.jpg 300w\" sizes=\"(max-width: 416px) 100vw, 416px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span>\u00a0Go to\u00a0<em><strong>Data &gt; Number of items to retain per field.<\/strong><\/em><\/p>\n<p>Select\u00a0<strong>None\u00a0<\/strong>then\u00a0<strong>OK.\u00a0<\/strong> This will stop Excel from retaining deleted data!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-08.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7513\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-08.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"456\" height=\"477\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-08.jpg 456w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-08-287x300.jpg 287w\" sizes=\"(max-width: 456px) 100vw, 456px\"><\/a><\/p>\n<p>This box lets you set the number of items per field to temporarily save, or cache with the workbook.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span>\u00a0Go back to your Pivot Table.\u00a0\u00a0<strong>Right-click <\/strong>and select\u00a0<strong>Refresh.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-09.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7514\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-09.jpg\" alt=\"Clear &amp; Delete Old Pivot Table Items cache\" width=\"487\" height=\"354\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-09.jpg 487w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-09-300x218.jpg 300w\" sizes=\"(max-width: 487px) 100vw, 487px\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Click the\u00a0<strong>Column Labels\u00a0<\/strong>drop-down list, and the <strong>Year 2012\u00a0<\/strong>is now gone!\u00a0 Problem fixed!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-10.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone wp-image-7515 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-10.jpg\" alt=\"clear pivot table cache\" width=\"458\" height=\"245\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-10.jpg 458w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/12\/Delete-Old-Pivot-Table-Data-10-300x160.jpg 300w\" sizes=\"(max-width: 458px) 100vw, 458px\"><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">Example 2:<\/span><\/strong><\/p>\n<p>In this table, months are displayed as abbreviations i.e. 3-letter month name, and using this data a Pivot Table is created.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-2020-07-06-at-5.00.48-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18432\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-2020-07-06-at-5.00.48-PM.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"585\" height=\"317\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-2020-07-06-at-5.00.48-PM.png 585w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-2020-07-06-at-5.00.48-PM-300x163.png 300w\" sizes=\"(max-width: 585px) 100vw, 585px\" \/><\/a><\/p>\n<p>But after creating Pivot, you realize it is better to present the full-length month name and hence you change the name in the data source.<\/p>\n<p>Now, when you look at the filter selection of Pivot Table, you will find that both versions of the month names are visible.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-245.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18439\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-245.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"498\" height=\"501\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-245.png 498w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-245-298x300.png 298w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-245-150x150.png 150w\" sizes=\"(max-width: 498px) 100vw, 498px\" \/><\/a><\/p>\n<p>Let&#8217;s fix that using the 3 simple steps!<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> <strong>Right-click <\/strong>on the Pivot Table and select\u00a0<strong>PivotTable Options.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-246.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18438\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-246.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"455\" height=\"521\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-246.png 455w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-246-262x300.png 262w\" sizes=\"(max-width: 455px) 100vw, 455px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>In the dialog box, go to <strong>Data<\/strong> Tab.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18412\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"456\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png 456w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-300x295.png 300w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> In the dropdown for the <strong>Number of items to retain per field, <\/strong>select<strong> None.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18418\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"456\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png 456w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1-300x295.png 300w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/a><\/p>\n<p>Click OK and don&#8217;t forget to refresh the Pivot Table.<\/p>\n<p>This will remove the abbreviated version of the month name and only show the full-length month name.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-247.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18437\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-247.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"498\" height=\"502\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-247.png 498w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-247-298x300.png 298w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-247-150x150.png 150w\" sizes=\"(max-width: 498px) 100vw, 498px\" \/><\/a><\/p>\n<p>To keep the data in the Pivot Table&#8217;s filter updated, make sure to change the number of items to retain per field to <strong>None<\/strong>.<\/p>\n<p>Once that is done and you refresh your table, the old data will no longer appear. This will clear Pivot Table Cache Memory!<\/p>\n<h3><a id=\"change-default-setting-of-retain-items\"><\/a>Change Default setting of Retain Items<\/h3>\n<p>By default, the number of items to retain per field is set to <strong>&#8220;Automatic&#8221;<\/strong>.<\/p>\n<p>If you want you even change the <strong>default setting to None<\/strong> so this setting is reflected in all the <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> you create. To do that, follow the steps below (this is applicable for<strong> Office 365 and Excel 2019<\/strong> only):<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Click on the <strong>File Tab<\/strong> at the top-left corner of Excel<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-239.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18409\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-239.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"515\" height=\"353\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-239.png 515w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-239-300x206.png 300w\" sizes=\"(max-width: 515px) 100vw, 515px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> From the left panel, select <strong>Options<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/WhatsApp-Image-2020-07-05-at-5.26.58-PM.jpeg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18417\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/WhatsApp-Image-2020-07-05-at-5.26.58-PM.jpeg\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"668\" height=\"726\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/WhatsApp-Image-2020-07-05-at-5.26.58-PM.jpeg 668w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/WhatsApp-Image-2020-07-05-at-5.26.58-PM-276x300.jpeg 276w\" sizes=\"(max-width: 668px) 100vw, 668px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> In the Excel Options dialog box, click on the <b>Data<\/b>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-241.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18411\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-241.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"827\" height=\"675\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-241.png 827w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-241-300x245.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-241-768x627.png 768w\" sizes=\"(max-width: 827px) 100vw, 827px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> Under Data Options, select <strong>Edit Default Layout<\/strong> button<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-242.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18413\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-242.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"825\" height=\"677\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-242.png 825w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-242-300x246.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-242-768x630.png 768w\" sizes=\"(max-width: 825px) 100vw, 825px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5: <\/strong><\/span>In the Edit Default Layout dialog box, click on <strong>PivotTable options\u00a0<\/strong>button.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-243.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18414\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-243.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"435\" height=\"297\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-243.png 435w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-243-300x205.png 300w\" sizes=\"(max-width: 435px) 100vw, 435px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6: <\/strong><\/span>In the PivotTable Options dialog box, click on the <strong>Data tab<\/strong>,<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18412\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"456\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244.png 456w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-300x295.png 300w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7: <\/strong><\/span>Under the <strong>Data tab<\/strong>, select <strong>None<\/strong> from the drop-down list in the <strong>Retain Items<\/strong> section<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear &amp; Delete Old Pivot Table Items\"  class=\"alignnone size-full wp-image-18418\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png\" alt=\"Clear &amp; Delete Old Pivot Table Items\" width=\"456\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1.png 456w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Screenshot-244-1-300x295.png 300w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/a><\/p>\n<p>Click Ok three times and Voila it&#8217;s done! The old deleted items from the data source are<strong> not shown in the Pivot Table&#8217;s filter selection <\/strong>anymore.<\/p>\n<p><span style=\"color: #0000ff;\"><strong>How To Clear Pivot Table Cache Memory<\/strong><\/span><\/p>\n<h3><a id=\"conclusion\"><\/a>Conclusion<\/h3>\n<p>In this tutorial, you have learned <strong>how to delete pivot table cache memory<\/strong> and\u00a0<strong>change the default setting of the retain items<\/strong> deleted from the data source.<\/p>\n<p>This will help you to force the Pivot Table to only show data or items that are currently in the data source.<\/p>\n<p><span style=\"text-decoration: underline; color: #0000ff;\"><strong>Helpful Resource:<\/strong><\/span><\/p>\n<p><strong>Make sure to download our FREE PDF on the<a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer\"> 333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n<p><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"nofollow noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-14329 size-medium\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg\" alt=\"\" width=\"300\" height=\"200\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-1024x682.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-768x512.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups.jpg 1340w\" sizes=\"(max-width: 300px) 100vw, 300px\" title=\"\"><\/a><\/p>\n<p>You can learn more about how to use Excel by viewing our FREE Excel webinar training on <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-43.html\" target=\"_blank\" rel=\"noopener noreferrer\">Formulas<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-5.html\" target=\"_blank\" rel=\"noopener noreferrer\">Pivot Tables<\/a>,<\/strong> and <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-39.html\" target=\"_blank\" rel=\"noopener noreferrer\">Macros &amp; VBA<\/a><\/strong>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-5.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/04\/530x300-Pivot.jpg?x85520\" alt=\"Excel Pivot Table\" width=\"530\" height=\"300\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Unable to Clear Pivot Table Cache Memory ?? Have you ever cleared, deleted, or replaced your Pivot Table data\/items but they still show inside your Pivot Table filters? What gives?? Well, you can easily clear your Pivot Table&#8217;s old items from your Pivot Table&#8217;s memory or cache In this article, you will be provided a [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17376,"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,17],"tags":[663,320,321,139,664],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/7944"}],"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=7944"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/7944\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17376"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=7944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=7944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=7944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}