/ How to

How to Clear The Excel Cache: Improving Excel Performance

Microsoft Excel uses a cache to store temporary files and data to improve performance. However, over time a cluttered cache can slow down Excel and even cause errors. Clearing the cache helps optimize Excel by removing unnecessary temporary data. Here are some key things to know about clearing the Excel cache:

Why Clear the Cache

  • Excel’s performance slows down over time as the cache fills up with temporary files, unused data, and outdated previews from recently opened workbooks. Clearing it improves responsiveness.
  • Large caches take up storage space on your computer. Freeing up that space speeds up Excel.
  • Sync errors with files stored on OneDrive or Sharepoint can corrupt data in the cache, preventing Excel from repairing files properly. Deleting the cache fixes this.
  • Add-ins, macros, and other code sometimes store temporary data that can get corrupted. Clearing the cache fixes issues caused by stale cached code.

Ways to Clear the Cache

There are a few different ways to clear the Excel cache:

  • Delete Cached Files
    • Go to File > Options > Save and delete cached files under Cache Settings[1]. This clears all cached Excel files.
  • Disable Recent Documents List
    • Go to File > Options > Advanced > Display and set number of Recent Documents to 0[1]. This prevents caching of preview data.
  • Clear Pivot Table Cache
    • Delete cached data used to build PivotTables to refresh them fully on open[2].
  • Use Office Upload Center
    • Go to File > Account > About Office Upload Center and click “Delete Cached Files”[1].
  • Manually Delete Cache Folders
    • Delete Excel’s cache folders directly from File Explorer[3].

You can also add VBA code to automatically clear the cache when the workbook opens[4].

When to Clear Cache

  • Weekly or monthly to clear temporary files that build up over time
  • When you upgrade Excel or Office to a new version
  • If you get errors opening files or syncing with OneDrive/Sharepoint
  • If Excel starts running slowly or freezing up
  • After deleting a lot of data from a workbook
  • When sending a workbook to other users to remove personal cache data

Benefits of Clearing Cache

  • Faster performance opening, saving, and editing files
  • More available storage space on your computer
  • Resolve corrupted cached data causing errors
  • Ensure latest data is displayed after changes to source data
  • Workbook sizes reduced by deleting unneeded temporary files

By periodically clearing out Excel’s cache, you can boost performance and fix a variety of issues caused by cached files. Pay attention to sluggish behavior or errors opening files as signs it’s time to empty the cache and restore Excel to peak efficiency.

How to Clear Excel Cache on a Mac

The process for clearing Excel’s cache works a bit differently on a Mac than Windows. Here are the steps:

Clear Cache for Admin User

If you have full admin access:

  • Open Excel and go to File > Options > Save
  • Under Cache Settings, click “Delete Cached Files”[11]
  • Confirm deleting the cached files

Clear Cache for Non-Admin User

If you don’t have admin access:

  • In Finder, go to Go > Go to Folder
  • Enter ~/Library/Caches and search for any Excel folders
  • Delete the files inside the Excel folders[11]

This clears Excel’s cache stored locally on your Mac.

When to Clear Cache on Mac

The guidance is similar for Mac:

  • Monthly
  • If Excel slows down
  • After Office/Excel updates
  • If you get file errors
  • Before sending a shared workbook

Clearing the cache on a regular basis helps avoid performance issues and data errors.

Fixing a Corrupted Office Document Cache

If Excel is having issues syncing files from OneDrive or Sharepoint, the Office document cache may be corrupted. Here are the steps to fix it:

  • Close all Office apps like Excel, Word, Powerpoint
  • Delete the cache files manually through File Explorer[2]
    • Typically located in C:\Users\<username>\AppData\Local\Microsoft\Office\16.0\OfficeFileCache
  • Reopen Office apps to rebuild the cache
  • Upload/sync files to OneDrive again

This forces Office to recreate the cache files from scratch, resolving any corruption issues and allowing proper syncing again.

Advanced Tips for Improving Excel Performance

Beyond clearing the cache, there are some other helpful tips for speeding up Excel:

Simple Fixes

  • Set calculation to manual in Formulas > Calculation Options[8]
  • Disable hardware graphic acceleration in File > Options > Advanced[12]
  • Close unused workbooks and reduce number of worksheets open
  • Use 64-bit Office instead of 32-bit if working large data sets[5]

Formula and Code Optimization

  • Replace array formulas with native functions like SUMIFS() where possible[8]
  • Simplify complex nested formulas
  • Set workbook calculation to manual before running macros[8]
  • Streamline VBA code by removing unused variables[8]

Data Management

  • Organize data on one worksheet instead of collecting from multiple sheets
  • Break apart and archive old, unused worksheets
  • Use Excel data model to manage connections to large external data sets[19]

Following Excel best practices for managing formulas, code, and data minimizes the number of cache files created and helps avoid performance bottlenecks.

Conclusion

Clearing Excel’s cache and fine-tuning your workbooks provides a quick performance boost and resolves many common issues. Learning these best practices empowers you to work efficiently with large data sets and ensure reliable file sharing and cloud syncing. Pay attention to sluggish behavior as a clue it’s time to clean out old temporary cached content. By staying on top of cache management, you can streamline Excel and eliminate frustrating errors and delays.

Related Posts