How to Clear The 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!