Mehedi Hasan Shimul
In computing, cache memory is the hardware or software component that temporarily stores data for future use. It saves a lot of processing time by storing frequently used data temporarily. With time the amount of data stored in cached memory increases which can slow down the processing time. When we work in Excel, some of our frequently used data are stored which can reduce the efficiency of Excel. But we can easily clear the cache memory following different methods. Here, we will discuss 6 simple methods to clear cache memory in Excel.
Download Practice Workbook
You can download the practice workbook from here.
6 Simple Methods to Clear Cache Memory in Excel
In this section, we will discuss 6 simple methods to clear cache memory in Excel. In the methods, we will use different options of Excel and some general applications of computers to clear cache memory.
1. Use Excel Options to Clear Cache Memory
In this method, we will reduce the number of recent workbooks to 0 which will free up a noticeable amount of cache memory. That can be done using Excel Options. Let’s follow the procedures given below.
- Firstly, open an Excel worksheet and go to the File tab.
- Then, select Options from the appeared pane.
- Consecutively, Excel Options window will appear, select Advanced.
- Afterward, scroll down to the Display section.
- Further, select 0 in Show this number of Recent Workbooks.
- Finally, click on OK and we removed the recent workbooks list which will clear cache memory.
2. Clear Cache Memory Utilizing Disk Cleanup
The Disk Cleanup application of our computer can be utilized to clear cache memory. Basically, this will clear all our cache memory which will include cache memory used by Excel also. Follow the stepwise procedures given below.
- Firstly, open the Disk Cleanup You can find it using the Search option of Windows.
- Then, select the location where the cache memory is stored and press OK.
- Afterward, select Temporary files in the appeared window and press OK.
- It will clear all cache memory from your computer.
3. Apply Excel Options to Remove Cache Memory Automatically
The cache memory can be removed automatically from Excel. So, there’ll be no need to delete it manually each time we use Excel. This method will work for 2018 or later versions of Microsoft Office. Let’s walk through the procedures.
- Firstly, go to the File tab and select Options.
- Then, from the Excel Options window select Trust Center > Trust Center Settings.
- Consecutively, the Trust Center window will appear. select Trusted Add-in Catalogs.
- Further, put a tick on Next time office starts, clear all previously-stated add-ins cache from the Trusted web add-ins catalogs section and press OK.
- Finally, cache memory will be removed automatically each time we open Excel.
4. Delete Local Files Manually to Clear Cache
Cache memory is definitely stored in our hard drive. So, it’s possible to delete them manually. Follow the stepwise procedures given below for this method.
- Firstly, open the Run dialog box by pressing Win + R keyboard shortcut.
- Then, write the following address there and hit OK.
%LOCALAPPDATA%\Microsoft\Office\16.0\Wef\
- Instantly, Windows Explorer will open the file location for cache memory.
- Finally, delete the files manually.
5. Clear Excel Pivot Table Cache Memory
Pivot Table is an excellent feature of Excel. It eases many of the complex works like sorting or adding slicers. So, using a pivot table means it will create its own cache which will be stored in Excel. But it’s possible to clear the cache memory used by the pivot table. Follow the steps given below.
- Firstly, create a pivot table. You can follow the article Example with Excel Pivot Table, if you don’t know how to create a pivot table.
- Then, right–click on any cell of the table and select Pivot Table Options.
- Afterward, from the Pivot Table Options window select Data and select None in the Number of items to retain per field box.
- Also, press OK.
- Again, right–click on any cell of the pivot table and select Refresh.
- Thus, we cleared the cache memory of the pivot table.
Read More: How to Clear Pivot Cache in Excel (3 Effective Ways)
6. Insert VBA Code to Clear Cache Memory
Visual Basics for Applications is an event-driven programming language for office applications. A piece of VBA code can clear the cache memory in an instant. Follow the stepwise procedures given below for this method.
- Firstly, open a worksheet in Excel.
- Then, press Alt + F11 to open the VBAÂ window.
- Afterward, from VBA Projects right click on the active worksheet.
- Further, select Insert > Module.
- Consecutively, a code window will open. Write the following code there.
Sub clearcache()
Dim xPtbl As PivotTable
Dim xWks As Worksheet
Dim xPcc As PivotCache
Application.ScreenUpdating = False
For Each xWks In ActiveWorkbook.Worksheets
For Each xPtbl In xWks.PivotTables
xPtbl.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next xPtbl
Next xWks
For Each xPcc In ActiveWorkbook.PivotCaches
On Error Resume Next
xPcc.Refresh
Next xPcc
Application.ScreenUpdating = True
End Sub
- Finally, press Run button from the VBA window and we successfully clear the cache memory from Excel.
Read More: Clear Excel Memory Cache Using VBA (3 Easy Ways)
Conclusion
We discussed 6 simple methods to clear cache memory in Excel. Hopefully, this article will help you. If you have any queries or suggestions, please let us know by commenting. Visit our ExcelDemy Website for more articles regarding Excel.
