How to Clear Cache in Excel (7 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

The cache is the hardware or software component a device uses for storing data temporarily. It helps in serving future requests at a relatively faster rate. While this is a helpful component in day-to-day operations, it all adds up over time. Caches clog up memory and spaces in the system over a long period of time and cause a drop in the performance of the application, and sometimes in the operating system. And in Microsoft Office applications, they may sometimes cause issues with cloud app syncing too. So it is a good idea to clear caches regularly. This article will cover how to clear the Excel cache.


Download Practice Workbook

You can download the workbook containing all the illustration sheets from the link below.


7 Effective Ways to Clear Cache in Excel

Now we will cover a total of seven different ways you can clear the Excel cache. Some of these are directly caches of the program, and some are included in other parts like add-ins. Nevertheless, they still add up as Excel caches. Also, some methods vary on your Excel version. We will include the version notes in their sub-sections.

Either way, we recommend you try out all to remove all of the caches and make your requests execute faster.


1. Disabling Recent Document List

One of the major ways you can reduce the Excel cache is to clear out the recent document list from the application. This will boost and improve your application’s performance and make the runtime faster. The main idea is to reduce the number of recent document to show to zero.

Follow these steps for a more detailed guide.

Steps:

  • First, select the File tab on your Excel ribbon.
  • Then select Options from the left side of the backstage view.

    • Consequently, the Excel Options box will open up.
    • Now select the Advanced tab from the left side of this box first.

how to clear excel cache from reducing number of recent workbooks

  • Then scroll down on the right until you found the Display sections.
  • Under that, set zero in the box beside the Show this number of Recent Workbooks. option.
  • Finally, click on OK.

This will disable the recent document list in Excel and clear a lot of the cache.


2. Using Office Upload Center

The Office Upload Center is a part of Microsoft Office applications and was used to come with the Office installations. But Microsoft has recently replaced it with a new feature- “Files Needing Attention”. However, if you have the upload center available in your system, you can use it to clear the Excel cache.

You need to follow these steps for that.

Steps:

  • First, open the Upload Center by searching it in the windows search bar.
  • Then go to Settings.
  • In the box, check the Delete files from the Office Document Cache when they are closed option under the Cache Settings.
  • After that, click on Delete cached files.
  • Next, a pop-up will appear to confirm the action.
  • Finally, confirm it by clicking on the Delete Cached information in the confirmation box.

3. Using Disk Cleanup

Windows has a certain tool called Disk Cleanup. This helps not only to remove the Excel cache but also any other applications for that matter. Follow these steps to see how we can use the Disk Cleanup tool to clear Excel or any Office cache.

Steps:

  • First, open up Disk Cleanup by searching it through the Windows search bar.
  • Next, select the file where your Office files are located.

how to clear excel cache using disk cleanup

  • Then click on OK.
  • At this instant, another box will pop up for that particular disk’s cleaning.
  • Now check the Temporary files option under the Files to delete section in the box.

how to clear excel cache by removing temporary files from disk cleanup

  • Finally, click on OK.

Thus, the Disk Cleanup will clear all the cache of Microsoft Office including Excel.


4. Remove Cache Automatically

This method works for the Office version of 2018 or later in Windows. It is also a recommended one for regular usage of add-in developers. Anyway, this method is helpful to automatically clear out caches every time you open Microsoft Excel. Thus, you don’t have to manually deal with some of them every time.

Follow these steps to see how this method works and try it out.

Steps:

  • First, select the File tab on your ribbon.
  • Then select Options from the left side of the backstage view.

  • Next, select the Trust Center tab from the right of the Excel Options
  • On the right, select Trust Center Settings as shown in the figure below.

  • As a result, the Trust Center box will open up this time.
  • Now select Trusted Add-in Catalogs tab in it from the left.
  • Then check the Next time Office starts, clear all previously-started web add-ins cache option on the right.

how to clear excel cache using trust center settings

  • Finally, click on OK.

Now Excel will automatically clear out the cache every time you restart the application.

Read More: Clear Excel Memory Cache Using VBA


5. Manually Deleting Local Files

As mentioned earlier, caches are just temporary files the system uses for a faster execution time of later requests. So you can find them in your hard drive at designated locations.

To find the local cache files of Excel and clear them, follow these steps.

Steps:

  • First, open up the Run dialog box by pressing the Win+R key on your keyboard.
  • Then insert the following in the box.

%LOCALAPPDATA%\Microsoft\Office\16.0\Wef\

opening manual temporary files

  • After that, click on OK.
  • As a result, the file explorer will open up with the local cache files.

temporary excel files

  • Now delete them all and restart Excel.

This way you can clear the Excel cache by deleting local files manually.


6. Clear PivotTable Cache

Pivot Table is an excellent feature in Microsoft Excel. This helps ease up processes from sorting to adding slicers with simple techniques. But using this feature means having its own caches filled up with Excel. So clearing Pivot Table caches are important too, to clear up the Excel cache.

Follow these steps to clear the Pivot Table cache in Excel.

Steps:

  • First, right-click on any cell of the Pivot Table.
  • Second, select PivotTable Options from the context menu.

how to clear excel cache using pivot table options

  • As a result, the PivotTable Options box will open up.
  • Now select the Data tab in it first.
  • Then select the Number of items to retain per field to None.

pivottable options to clear cache in excel

  • Once, you have done that, click on OK.
  • The action should be set into motion at this point. But to see its effect at the instant, right-click on any cell of the pivot table and select Refresh from the context menu.

refreshing to clear excel cache after changing settings

This will remove the pivot table caches and won’t store them. As a consequence, you will reduce the Excel cache.

Read More: How to Clear Pivot Cache in Excel (3 Effective Ways)


7. Embedding VBA Code

We can also use VBA to clear the cache in Excel. Visual Basic for Application (VBA) is an event-driven programming language developed by Microsoft that can help us with simple processes like inputting a value in a cell to automate large and complicated processes.

But to use VBA in Excel or any other Office applications, you need the Developer tab to show on your ribbon. If you don’t have one, click here to display the Developer tab on your ribbon.

Once you have that, you can follow these simple steps to clear the Excel cache using VBA.

Steps:

  • First, go to the Developer tab on your ribbon.
  • Then select Visual Basic from the Code

  • As a result, the VBA window will open up.
  • Now click on Insert and select Module from the drop-down menu.

how to clear excel cache using VBA

  • Next, select the module if it isn’t selected already and insert the following code in it. (you can also find the code in the workbook.)
Sub Workbook_Open()
Dim xPtble As PivotTable
Dim xWkst As Worksheet
Dim xPcch As PivotCache
Application.ScreenUpdating = False
For Each xWkst In ActiveWorkbook.Worksheets
For Each xPtble In xWkst.PivotTables
xPtble.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next xPtble
Next xWkst
For Each xPcch In ActiveWorkbook.PivotCaches
On Error Resume Next
xPcch.Refresh
Next xPcch
Application.ScreenUpdating = True
End Sub

Finally, press F5 on your keyboard to run the code instantly.

Read More: How to Clear Pivot Table Cache Using VBA in Excel


Conclusion

So these were all the methods we can use to clear the cache of Excel and its components. Hopefully, you can use these methods with ease and can successfully remove caches from Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides and solutions like this, visit Exceldemy.com.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

2 Comments
  1. you can just delete this file: C:\Users\(username)\AppData\Local\Microsoft\Office\16.0\OfficeFileCache

    • Reply Avatar photo
      Abrar-ur-Rahman Niloy May 8, 2023 at 4:28 PM

      Thank you for pointing this out. There are some cache files in the location and the article has been updated for later uses.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo