if you use pivot tables in Excel you may find your file rapidly growing in size this is because Excel creates a duplicate of the source data called a cache which is stored inside your file this cache lets Excel make much faster adjustments to your pivot tables but at the cost of increased file size there are a few options for tackling pivot table caches to reduce the size of your file but in this video we'll just show you the safest there's a link to our blog post in the description below that c
overs the other options if you need them we'll show you how to F find all of the pivot tables in your workbook then we'll demonstrate how to clear the cache and make sure it regenerates each time instead which will greatly reduce the size of your Excel file now let's get started while looking inside this large file you can see that a chunk of the data is made up of pivot cache if you haven't already please see the video why is my Excel file big where we discuss how you can safely open Excel file
s to see what's inside a link to that video is in the description below so I'm going to work on a copy of the file just so that we can see the file size differences at the end so let's open the file and that can take some time depending on the amount of data in your file or the size of your file so first we need to find all of the pivot tables in your workbook and there is actually an easy way to achieve this if we go to the view Tab and then we look in the show section we can see the navigation
if we filter for pivot then we're able to see a list of all of the pivot tables in the side panel here now we can simply Click on each of them in turn to select the pivot table now in order to see the data options of the pivot table we need to rightclick the selected pivot table and choose pivot table options then we're going to switch to the data tab to stop the file from keeping a copy of your pivot table data we're going to uncheck the first box save Source data with file and then we want to
ensure the data is refreshed when the file's open so we're going to check the refresh data when opening the file checkbox and that is all you need to do when we hit okay Excel will warn us that we are removing a copy of the pivot data from this file which can be a problem if you lose the link to the source data so you need to weigh up whether this is necessary in your circumstances if you're happy that all the link data is going to remain in place you can safely dismiss These Warnings now we ne
ed to repeat this process for each pivot table in the list and once we've performed that magic I'm going to save my file and I'm going to close the file so once we have saved the file we can see that there is a large reduction in file size you have now seen how to find pivot tables in your Excel file then remove cache data for each which reduces the size of your file we'd love to hear your ideas and feedback so please leave them in the comments below I'll see you on the next video
Comments