Removing excess formatting from an Excel spreadsheet

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:

excel-excessformatting-20170612-1

Select COM Add-ins from Manage dropdown field and click Go:

excel-excessformatting-20170612-2

In COM Add-ins window, check Inquire and click OK:

excel-excessformatting-20170612-3

An Inquire Tab is now available on Ribbon. Click on Clean Excess Cell Formatting from Inquire menu and follow the prompts.

excel-excessformatting-20170612-4

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s