How to Clear Excel Cache
There’s no doubt that cache memory is handy and that every computer program relies on it. It helps software remember the most used functions and values and stores frequently used files. However, if you don’t clear the cache regularly, your computer might suffer a drop in performance. On slower and older computers, programs might become unstable.
Luckily, most software nowadays lets you clear the cache. The Microsoft Office program pack, more specifically Excel, is no exception. Read on to find out how to free up Excel’s cache.
Disable the Recent Documents List
The simplest way to improve Excel performance is to set the number of “recent documents” shown to zero. In other words, you’re effectively disabling the recent documents list. Here’s how you can disable it:
- Click on the “Office” button located in the top-left corner. Depending on the version, there might not be an Office button. In that case, click on the “File” tab in the main menu.
- The Office menu will open. Click on the “Options” button at the bottom of the menu.
- Once in the “Options” menu, select the “Advanced” tab.
- Scroll down until you reach the “Display” section. Change the value in “Show this number of Recent Workbooks” to zero.
- Click on “OK” to save the changes. The next time you click on the Office or File button, you’ll see an empty “Recent Documents” list.
Clear the Pivot Table Cache
Among the most critical cache clearing options specific to Excel are those that let you clear the cache of a pivot table. Doing so deletes old, unused items. There are two ways to do this.
Clearing Excel Pivot Table Cache Using the PivotTable Options
- Right-click on “any cell” in the pivot table. The context menu will appear.
- Select “Pivot Table Options…”
- Go to the “Data” tab and set the value of “Number of items to retain per field” to “None.”
- Click on the “OK” button to save the changes.
- For the changes to take effect, you should right-click on any “pivot table cell” again and choose “Refresh.”
How to Clear Excel Cache Using VBA Code
You can also use the “Microsoft Visual Basic for Applications” program and VBA code to clear Excel’s cache. The main advantage of this method is that it covers all pivot tables.
- Open the file for which you want to clear the pivot tables cache, then press “Alt + F11” to open Microsoft Visual Basic for Applications.
- Double-click on “ThisWorkbook” in the “Project” pane to the left to open the code window.
- Copy and paste the following code into the “ThisWorkbook” code window:
Private Sub Workbook_Open()
Dim xPt As PivotTable
Dim xWs As Worksheet
Dim xPc As PivotCache
Application.ScreenUpdating = False
For Each xWs In ActiveWorkbook.Worksheets
For Each xPt In xWs.PivotTables
xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone
For Each xPc In ActiveWorkbook.PivotCaches
On Error Resume Next
Application.ScreenUpdating = True
- To start the code, press “F5.” This action will clear the pivot tables’ cache in the active workbook.
How to Manually Clear the Office Cache
Clear Office Cache Using the Office Upload Center
You can use a program called “Microsoft Office Upload Center” to manually clear the cache for all Office programs. In Windows versions 7 and 10, you can find this application by typing its name in the “Start Menu’s” search bar. In Windows 8 and 8.1, access the Search option by hovering with the mouse over the bottom-right corner of the screen. It will be one of the suggested options.
- Open the “Upload Center” and click on the “Settings” button.
- In the “Upload Center” settings, tick the “Delete files from the Office Document Cache when they are closed” checkbox.
- Click on the “Delete cached files” button.
- Confirm your decision by clicking on the “Delete cached information” button.
Note: You can also set the “Days to keep files in the Office Document Cache” option to your liking.
Clear Office Cache Using Disk Cleanup
Windows “Disk Cleanup” helps remove many temporary files, including Office documents. You can find Disk Cleanup the same way you found the Office Upload Center.
- Once you’ve found the “Disk Cleanup” program, open it and select the drive where Microsoft Office resides.
- Click on “OK.”
- When the program finishes analyzing files, go to the “Files to delete” section and select the “Temporary Files” checkbox, then click on “OK.”
In closing, letting MS Office cache memory fill up can improve the workflow, but it can also cause numerous bugs, stability issues, and an overall performance decrease. If you don’t need the recently used documents list, consider clearing the cache regularly, especially if you have an older computer. Use one of the above processes to reduce your “cache pileup” and bring productivity back to the table!