Sometimes when working with Excel, you may end up with a spreadsheet that has an excessive amount of formatting. That formatting can come with a cost, such as large file sizes and long wait times to launch the spreadsheet.
A colleague recently helped someone clean out the formatting in their Excel spreadsheet with the following results:
“With the information you shared with me, I was able to remove the excess formatting that brought the workbooks from over 11,500 kb each, to a under 150 kb. The files now take just 11 – 30 seconds to open and save instead of 3-10 minutes. The savings in time and frustration is wonderful!”
You can follow the steps below to clean out the formatting in your spreadsheets…
Issue: Basic copy/paste/change actions as well as opening and saving files take a significant amount of time to complete.
Reason: The slowness may be caused by old formatting in the spreadsheets.
Resolution: Copy the contents of the sheet into a new sheet using the “Paste Values” option when pasting (which strips out all formatting and leaves only data), or use the Microsoft-provided Add-In called Inquire which helps perform this clean-up.
To activate Inquire on your spreadsheet:
While in your spreadsheet, click on File > Options > Add-Ins:
Select COM Add-ins from Manage dropdown field and click Go:
In COM Add-ins window, check Inquire and click OK:
An Inquire Tab is now available on Ribbon. Click on Clean Excess Cell Formatting from Inquire menu and follow the prompts.
NOTE: Before running the clean option it is recommended that you save the file with a different name to protect against data loss. Once you have verified that the data is okay, save the file back to the original name.
One Minute Office Magic 