How to Clear Cache in Excel?

Think of a cache as a temporary storage space on your computer that Excel uses to store files.

These files help Excel to perform better by storing information such as frequently performed tasks and functions or your recently used documents.

While this is helpful on a short-term basis, in the longer run, as you use the program more and more, the cache files start to build up.

This may cause Excel or, in the worst case, your operating system to slow down. Your files may take a long time to load.

This can decrease productivity, especially on older systems with low hardware specs. Hence it is a good idea to clear the cache occasionally. Large caches can also cause issues with the app syncing to the cloud.

Files stored inside the cache can come from many different sources in Excel.

The Recent Documents List uses the cache to remember which Excel files you opened recently. Similarly, the Office Upload Center and Pivot Table also make use of the cache for better processing.

In this article, we will take a look at how to clear the cache in Excel using different methods.

Method 1:  Turning Off The Recent Documents

One way of reducing the cache in Excel is to disable the Recent Documents.

Let’s see how we can do that using the steps below.

  1. From the ribbon, select the File tab.
Click the File tab
  1. The following window will appear.
Excel backstage area opens
  1. Select the Options button from the panel on the left.
Click on Options
  1. The Options window will appear as shown.
Options window
  1. Go to the Advanced section.
Click the Advanced option
  1. Settings for the Advanced section will appear on the panel to the right.
  2. Scroll down until you find the Display section.
Display section in the Excel options dialog box
  1. In the box next to ‘Show this number of Recent Workbooks’, set the value to 0.
Set Show this number of Recent Workbooks to 0
  1. Finally, click on OK.
Click OK

The next time you open Excel, you will no longer see any recently opened documents.

But the cache will have been cleared from these files.

So in this method, we have seen how to clear the cache by not letting Excel store any information regarding recently used documents.

Method 2: Clearing Files from the Office Document Cache

In this method, we will be clearing the Excel cache from files that are stored by the Office Upload Center.

The Office Upload Center is an application that comes bundled with office installations. Let’s see how to clear the cache through the steps below.

  1. Open the Office Upload Center app. (On Windows 10 you can search for it in the windows search bar.)
  2. The Office Upload Center Window will appear as shown.
Office Upload Center Window
  1. Click on the Settings button at the top.
Click on Settings
  1. The Settings window will open as shown.
Settings window
  1. Under the Cache Settings, check the box which says ‘Delete files from the Office Document Cache when they are closed’.
Delete files from the Office Document Cache when they are closed
  1. Click on the ‘Delete cached files’ button.
  1. A confirmation window will open as shown.
Confirmation window opens
  1. Click on the ‘Delete Cached information’ button.
click on the delete cachet information option
  1. Finally, click on OK and close the window.
click OK to close the window

This will clear any existing cache files in the Office Document Cache.

So in this method, we have seen how to clear the cache from files stored by the Office Upload Center app.

Method 3: Clearing Cache from the Disk Cleanup App on Windows

In this method, we will use the Disk Cleanup utility available on windows to clear the cache.

Follow the steps below, which will guide you through the process.

  1. Open the Disk Cleanup app. (On Windows 10, you can search for it in the windows search bar.)
  2. The Disk Cleanup Drive Selection window will appear as shown.
disk cleanup drive selection
  1. From the Drives drop down, select the drive on which Microsoft Office is installed. (In my case, it is the ‘C’ drive)
select the drywear Microsoft office is installed
  1. Click on OK.
click OK
  1. The Disk Cleanup for C: window will be opened as shown.
disk cleanup for drive window
  1. Under the ‘Files to delete’ section, scroll down until you see the Temporary Files option.
temporary files option
  1. Click on the checkbox next to the Temporary Files option.
check the temporary files option
  1. Click on OK.
click OK
  1. A confirmation window will appear as shown.
disk cleanup confirmation window appears
  1.  Click on the ‘Delete Files’ button.
click on the delete files option

The Disk Cleanup will run, and any Temporary Files on the C: drive will be cleared.

So in this method, we have seen how to clear the cache using the Disk Cleanup app available in windows.

Method 4: Clearing the Cache Used By Pivot Tables

Pivot Tables are extremely useful in Excel. But they also save temporary files in the cache for use.

In this method, we will take a look at how to clear the Pivot Table cache from the Excel cache.

  1. Right-Click on any cell of your pivot table.
  2. You will see the context menu show up as shown below.
context menu after right click
  1. From this menu, select the Pivot Table Options.
click on the pivot table options
  1. The Pivot Table Options window will appear as shown.
pivot table options window appears
  1. Go to the Data tab.
  1. Click on the drop-down next to the ‘Number of items to retain per field’.
click the drop down for number of items to retain per field options
  1. Select the None option.
select the none option from the drop-down
  1. Click on OK.
click OK
  1. Right-Click on any cell of your pivot table.
  2.  The context menu will appear as shown.
  1.  From this menu, select the Refresh option.
click on refresh

This will clear any cache files stored by the pivot table and will reduce the Excel cache.

So in this method, we have seen how to clear the pivot table cache in Excel from the right-click context menu.

Method 5: Using VBA Code to Clear the Cache

For more advanced users, we will be using VBA code to clear the Excel cache. Follow the steps below to see how to do it.

  1. Open the VBA editor by pressing ‘Alt + F11’ on your keyboard. The following window will appear.
open the VB editor in Excel
  1. From the menu bar at the top, click on Insert.
  2. Under the Insert menu, select the Module option.
insert module
  1. This will insert a new module where you can write your VBA code.
module code windows inserted

The VBA script to clear the cache is shown below:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Clear_Cache()
Dim pivotTable As PivotTable
Dim worksheet As Worksheet
Dim pivotCache As PivotCache
Application.ScreenUpdating = False
For Each worksheet In ActiveWorkbook.Worksheets
For Each pivotTable In worksheet.PivotTables
pivotTable.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pivotTable
Next worksheet
For Each pivotCache In ActiveWorkbook.PivotCaches
On Error Resume Next
pivotCache.Refresh
Next pivotCache
Application.ScreenUpdating = True
End Sub

The above VBA code goes through each worksheet in the workbook, and then in each worksheet, it goes through every Pivot Table there is and clears the cache for each pivot table

  1. Copy and Paste the above VBA code into the module window.
  2. To run the code, place the cursor anywhere in the code and press the ‘F5’ key on your keyboard. (Alternatively, you can click on the green play button in the toolbar)
run the macro by clicking on the green play button

So in this method, we have seen how to clear the cache in Excel using VBA code.

This article has presented different methods for clearing the cache in Excel.

As you have seen, there are different sources that generate cache files like the Office Upload Center, the Recent Documents List or the Pivot Table Cache.

If you do not want to get rid of the Recent Documents List, use Methods 2 & 4 only.

Method 3 uses the Disk Cleanup app on windows to clear the cache.

However, this app will clear the caches for all other apps stored on your drive as well (e.g., Word, PowerPoint, etc.). If you want to clear the cache for Excel only, skip this method.

If you have multiple worksheets in your Excel file and each worksheet has a pivot table in it, then clearing all the pivot table caches using Method 4 can be cumbersome.

Instead, use the VBA code from Method 5 to automatically clear the pivot table cache from all the Pivot Tables in all worksheets.

I hope this article was helpful to you. If you have any questions, let us know in the comments section below.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Clear Cache in Excel?”

  1. or you can just delete this file: C:\Users\(username)\AppData\Local\Microsoft\Office\16.0\OfficeFileCache

    Reply

Leave a Comment