Let’s say you are working on a big Excel file with many formulas, and your computer starts running slowly. Clearing Excel’s cache can speed things up by eliminating old, stored data that is no longer needed.
In Excel, “cache” refers to temporary storage files where data is saved to speed up processing. But sometimes, the cache makes your file slow and takes storage space from your system.
- Formula Cache: Excel caches formula results to improve performance. When a formula is calculated, Excel stores the result in its cache. If the formula needs to be recalculated, Excel first checks if the inputs have changed. If not, Excel uses the cached result instead of recalculating the formula.
- PivotTable Cache: Each PivotTable has a data cache that stores a copy of the source data in the PivotTable. This cache allows the PivotTable to quickly recalculate when you make changes to the layout or apply filters.
- Clipboard Cache: When you copy data in Excel, it is stored in the clipboard cache, which can then be used to paste the copied data within Excel.
There are two ways to clear the cache: the first is to create and use the Microsoft Upload Center App, and the second is to use the Excel option.
How to Clear Excel Cache (Steps)
- First, go to the file tab and open the Excel options.
- From there, go to the save option and scroll to the end of the dialog box.
- Now from the Cache Settings, click on the “Delete cached files”.
- After that, you get a dialog box to confirm if you want to clear the cached files.
- In the end, just click on “Delete Cached Files” to clear the Excel’s cache.
At this point, all the cached Excel files have been deleted from your system. Apart from this, there are a few more options in the same setting group that you need to know.
- Days to Keep Files: You can change the settings to decide when you want Office to keep your files (cache data) in the cached memory. It is 14 days, by default, but you can specify a different day.
- Delete Files from Cache when they are Closed: This will delete files from the cached memory once you close the file.
Using Office Upload Center to Clear Cache in Microsoft Excel
You can also use the Office Upload Center with the Office software package. Once you open the Upload Center, click on Settings ⇢ Delete cached files ⇢ Delete cached information.
In Office 365, the Office Upload Center has been removed and replaced by Files Needing Attention. This option is found in File Tab ⇢ Open ⇢ Files Needing Attention.
How to Clear Excel Cache from Temp Folder (Cached Files)
- Ensure that Excel is completely closed. All Excel files should be closed, and the application should be exited if opend.
- Press the Win+R keys on your keyboard. This will open the Run dialog box.
- In the Run dialog box, type %temp% and press Enter. This will open the Temp folder, where the cache files are stored.
- In the Temp folder, look for any files that begin with ‘Excel’. These are the Excel cache files.
- Select all of the Excel cache files. You can do this by clicking on one file and pressing Ctrl+A on your keyboard.
- You can delete the selected files by right-clicking on them and choosing ‘Delete’ or pressing the Delete key on your keyboard.
- Close the Temp folder after all the Excel cache files have been deleted and Restart Excel.
But Why is it Important to Clear the Excel Cache?
Clearing the Excel cache can provide several benefits, such as improving the application’s performance. The Excel cache is a temporary storage type that the application uses to quickly access frequently used data. Over time, this cache can become quite large as more and more data is stored.
This accumulation of data can result in the application slowing down because it requires more processing power to access and navigate the cache.
By clearing the cache, you essentially free up this processing power, allowing Excel to run faster and more efficiently. This can be particularly useful when dealing with large spreadsheets or complex calculations, as it can help reduce loading times and improve the overall user experience.
Clear Excel Cache with VBA
VBA does not directly support clearing the entire cache, including all temporary data in Excel, because Excel manages its memory and cache internally.
But you can indirectly help Excel clear its internal caches by forcing a full calculation of all formulas in the workbook.
Sub ClearExcelCache()
Application.CalculateFull
End Sub This code recalculates all formulas in all open workbooks, which can help clear any cached formula results.
This method does not directly clear caches like a browser cache clear function, but it does force Excel to re-evaluate data, which can indirectly clear some types of cached information.
Tips to Keep Cache Low in Excel
It’s really easy to clear the cache, and you can do it whenever you need to. But there are a few things you can keep in mind to follow so that the cache is limited and you don’t have to worry about it much.
- Complex formulas, especially volatile ones like NOW, TODAY, and RAND, can increase cache load. Having multiple worksheets within a single workbook increases memory consumption.
- Using conditional formatting rules on large ranges can also increase memory usage.
- Workbooks with links to external data sources or other workbooks increase memory usage due to the need to maintain these links.
- Embedded objects, images, and charts can increase the workbook size and cache.
- Sharing workbooks for collaboration can increase cache usage as Excel needs to store multiple versions.
- Multiple or complex pivot tables, mainly those with large source data, can consume a lot of memory.
One of the reasons we clear cache in Excel is to make it work fast. But sometimes, a few problems cause a workbook to crash while opening. In that case, you can open Excel in safe mode to troubleshoot the problem, or you can also clear formatting to make your workbook faster.