How to Clear Cache in Excel?

Think of a cache as a temporary storage space on your computer that Excel uses to store files.

These files help Excel to perform better by storing information such as frequently performed tasks and functions or your recently used documents.

While this is helpful on a short-term basis, in the longer run, as you use the program more and more, the cache files start to build up.

This may cause Excel or, in the worst case, your operating system to slow down. Your files may take a long time to load.

This can decrease productivity, especially on older systems with low hardware specs. Hence it is a good idea to clear the cache occasionally. Large caches can also cause issues with the app syncing to the cloud.

Files stored inside the cache can come from many different sources in Excel.

The Recent Documents List uses the cache to remember which Excel files you opened recently. Similarly, the Office Upload Center and Pivot Table also make use of the cache for better processing.

In this article, we will take a look at how to clear the cache in Excel using different methods.

Method 1:  Turning Off The Recent Documents

One way of reducing the cache in Excel is to disable the Recent Documents.

Let’s see how we can do that using the steps below.

1. From the ribbon, select the File tab.

2. The following window will appear.

3. Select the Options button from the panel on the left.

4. The Options window will appear as shown.

5. Go to the Advanced section.

6. Settings for the Advanced section will appear on the panel to the right.
7. Scroll down until you find the Display section.

8. In the box next to ‘Show this number of Recent Workbooks’, set the value to 0.

9. Finally, click on OK.

The next time you open Excel, you will no longer see any recently opened documents.

But the cache will have been cleared from these files.

So in this method, we have seen how to clear the cache by not letting Excel store any information regarding recently used documents.

Method 2: Clearing Files from the Office Document Cache

In this method, we will be clearing the Excel cache from files that are stored by the Office Upload Center.

The Office Upload Center is an application that comes bundled with office installations. Let’s see how to clear the cache through the steps below.

1. Open the Office Upload Center app. (On Windows 10 you can search for it in the windows search bar.)
2. The Office Upload Center Window will appear as shown.

3. Click on the Settings button at the top.

4. The Settings window will open as shown.

5. Under the Cache Settings, check the box which says ‘Delete files from the Office Document Cache when they are closed’.

6. Click on the ‘Delete cached files’ button.

7. A confirmation window will open as shown.

8. Click on the ‘Delete Cached information’ button.

9. Finally, click on OK and close the window.

This will clear any existing cache files in the Office Document Cache.

So in this method, we have seen how to clear the cache from files stored by the Office Upload Center app.

Method 3: Clearing Cache from the Disk Cleanup App on Windows

In this method, we will use the Disk Cleanup utility available on windows to clear the cache.

Follow the steps below, which will guide you through the process.

1. Open the Disk Cleanup app. (On Windows 10, you can search for it in the windows search bar.)
2. The Disk Cleanup Drive Selection window will appear as shown.

3. From the Drives drop down, select the drive on which Microsoft Office is installed. (In my case, it is the ‘C’ drive)

4. Click on OK.

5. The Disk Cleanup for C: window will be opened as shown.

6. Under the ‘Files to delete’ section, scroll down until you see the Temporary Files option.

7. Click on the checkbox next to the Temporary Files option.

8. Click on OK.

9. A confirmation window will appear as shown.

10.  Click on the ‘Delete Files’ button.

The Disk Cleanup will run, and any Temporary Files on the C: drive will be cleared.

So in this method, we have seen how to clear the cache using the Disk Cleanup app available in windows.

Method 4: Clearing the Cache Used By Pivot Tables

Pivot Tables are extremely useful in Excel. But they also save temporary files in the cache for use.

In this method, we will take a look at how to clear the Pivot Table cache from the Excel cache.

1. Right-Click on any cell of your pivot table.
2. You will see the context menu show up as shown below.

3. From this menu, select the Pivot Table Options.

4. The Pivot Table Options window will appear as shown.

5. Go to the Data tab.

6. Click on the drop-down next to the ‘Number of items to retain per field’.

7. Select the None option.

8. Click on OK.

9. Right-Click on any cell of your pivot table.
10.  The context menu will appear as shown.

11.  From this menu, select the Refresh option.

This will clear any cache files stored by the pivot table and will reduce the Excel cache.

So in this method, we have seen how to clear the pivot table cache in Excel from the right-click context menu.

Method 5: Using VBA Code to Clear the Cache

For more advanced users, we will be using VBA code to clear the Excel cache. Follow the steps below to see how to do it.

1. Open the VBA editor by pressing ‘Alt + F11’ on your keyboard. The following window will appear.

2. From the menu bar at the top, click on Insert.
3. Under the Insert menu, select the Module option.

4. This will insert a new module where you can write your VBA code.

The VBA script to clear the cache is shown below:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Clear_Cache()
Dim pivotTable As PivotTable
Dim worksheet As Worksheet
Dim pivotCache As PivotCache
Application.ScreenUpdating = False
For Each worksheet In ActiveWorkbook.Worksheets
For Each pivotTable In worksheet.PivotTables
pivotTable.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pivotTable
Next worksheet
For Each pivotCache In ActiveWorkbook.PivotCaches
On Error Resume Next
pivotCache.Refresh
Next pivotCache
Application.ScreenUpdating = True
End Sub

The above VBA code goes through each worksheet in the workbook, and then in each worksheet, it goes through every Pivot Table there is and clears the cache for each pivot table

5. Copy and Paste the above VBA code into the module window.
6. To run the code, place the cursor anywhere in the code and press the ‘F5’ key on your keyboard. (Alternatively, you can click on the green play button in the toolbar)

So in this method, we have seen how to clear the cache in Excel using VBA code.

This article has presented different methods for clearing the cache in Excel.

As you have seen, there are different sources that generate cache files like the Office Upload Center, the Recent Documents List or the Pivot Table Cache.

If you do not want to get rid of the Recent Documents List, use Methods 2 & 4 only.

Method 3 uses the Disk Cleanup app on windows to clear the cache.

However, this app will clear the caches for all other apps stored on your drive as well (e.g., Word, PowerPoint, etc.). If you want to clear the cache for Excel only, skip this method.

If you have multiple worksheets in your Excel file and each worksheet has a pivot table in it, then clearing all the pivot table caches using Method 4 can be cumbersome.

Instead, use the VBA code from Method 5 to automatically clear the pivot table cache from all the Pivot Tables in all worksheets.

I hope this article was helpful to you. If you have any questions, let us know in the comments section below.

Other Excel articles you may also like:

• Why is Excel so Slow? 11 Tips to Fix Slow Excel Files!
• How to Find out What Version of Excel You Have
• Microsoft Excel vs Google Sheets – Which One Is Better for You?
• How to Edit Document Properties in Excel?
• Excel Shortcuts Not Working – Possible Reasons + How to Fix?
• How to Make Excel File Read Only
• How to Turn Off Autosave in Excel?