How to Clear Excel Cache

Posted by Arch on August 27, 2019

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.

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 “Excel Options” button at the bottom of the menu.

Once in the “Excel 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 Documents,” 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 “PivotTable 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 PivotTableDim xWs As WorksheetDim xPc As PivotCache

Application.ScreenUpdating =False

For Each xWs In ActiveWorkbook.Worksheets

For Each xPt In xWs.PivotTables

xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next xPt

Next xWs

For Each xPc In ActiveWorkbook.PivotCaches

On Error Resume Next

xPc.Refresh

Next xPc

Application.ScreenUpdating =True

End Sub

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.

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.