How to Clear Excel Cache
There’s no doubt that cache memory is very useful. Almost every computer program relies on it, after all. It helps software remember the most used functions and values, as well as store frequently used files. However, if you don’t clear it regularly, your computer might suffer a drop in performance. On some slower and older computers, programs might become unstable.
Luckily, most programs nowadays let 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
Possibly 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 the 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, move to the “Advanced” tab.
- Scroll down until you reach the “Display” section. Set the value of the first option, “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 important cache clearing options specific to Excel are the ones that let you clear the cache of a pivot table. Doing so deletes old, unused items. There are two ways to do this.
Using the PivotTable Options
- Right-click on a 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 a pivot table cell again and choose “Refresh.”
Using the VBA Code
You can also use the Microsoft Visual Basic for Applications program to do this. 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, press Alt + F11 to open the Microsoft Visual Basic for Applications.
- Double-click on “ThisWorkbook” in the “Project” pane to the left. This will 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 will clear the pivot tables’ cache in the active workbook.
Manually Clear the Office Cache
Use 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.
Use Disk Cleanup
Windows Disk Cleanup program helps remove temporary files of all sorts, including Office documents. You can find Disk Cleanup the same way you found the Office Upload Center.
- Once you’ve found the program, open it and select the drive where Microsoft Office is installed.
- Click on “OK.”
- When the program is finished with analyzing files, go to “Files to delete.”
- Select the “Temporary Files” checkbox and click on “OK.”
Keep It Clear
While keeping the cache memory full helps improve the workflow, it can cause numerous bugs, stability issues, and an overall performance decrease. If you don’t absolutely need the recently used documents list, consider clearing the cache regularly, especially if you have an older computer.
How often do you clear the cache in Excel? Does Excel work faster then? How does it impact your computer? Let us know in the comments below.