0

How to Clear Excel Cache

Posted by Arch on August 27, 2019
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:

  1. 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.
  2. The Office menu will open. Click on the “Excel Options” button at the bottom of the menu.
  3. Once in the “Excel Options” menu, move to the “Advanced” tab.
    Excel Options
  4. Scroll down until you reach the “Display” section. Set the value of the first option, “Show this number of Recent Documents,” to zero.
  5. 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

  1. Right-click on a cell in the pivot table. The context menu will appear.
  2. Select “PivotTable Options…”
    Excel PivotTable Options
  3. Go to the “Data” tab and set the value of “Number of items to retain per field” to “None.”
  4. Click on the “OK” button to save the changes.
  5. 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.

  1. Open the file for which you want to clear the pivot tables cache, press Alt + F11 to open the Microsoft Visual Basic for Applications.
  2. Double-click on “ThisWorkbook” in the “Project” pane to the left. This will open the code window.
    Visual Basic
  3. 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

  4. 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.

Upload Center

  1. Open the Upload Center and click on the “Settings” button.
  2. In the Upload Center Settings, tick the “Delete files from the Office Document Cache when they are closed” checkbox.
  3. Click on the “Delete cached files” button.
  4. Confirm your decision by clicking on the “Delete cached information” button.
    Delete cached information

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.

  1. Once you’ve found the program, open it and select the drive where Microsoft Office is installed.
  2. Click on “OK.”
  3. When the program is finished with analyzing files, go to “Files to delete.”
  4. Select the “Microsoft Office 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.

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.