How to reduce time to open a workbook, improve performance and reduce workbook size

Spreadsheets Microsoft Excel
,
1

Is your Excel workbook taking a longer time to open up lately?
Would you like to improve the overall performance of the workbook?
Are you interested in reducing the overall size of the workbook?

I recently came across a command in Excel called “Check Performance”. The command will check for cells that have formatting and no data in cells which could be impacting performance. A simple example is where a complete row has color formatting and only a few hundred rows are being used. Given that a spreadsheet can have 1,048,576 rows and 16,384 columns the impact of this type of formatting across a number of spreadsheets in a workbook can be significant.

Executing the command will show you where there are potential performance impacts and a choice to have it cleaned up or not.

I tested this command out on a older backup copy of my Tiller Money template which also included a few community solutions and some of my own spreadsheets and pivot tables. I accepted all of the recommended items found for my test. The command reduced the size of the workbook from approximately 6.5 MB to .5 MB. I found that the workbook opened up quickly and felt that the overall performance improved. I don’t have any time or measurement data to quantify the speed and performance improvements. I did not find any obvious issues with the workbook or functionality of the template or community solutions I use.

After this test, I went ahead and ran the command against my official 2024 financial template (I have it backed up too). I have been using the cleaned up template for about a month now without any issues. I am not an expert at Excel nor do I know the details of the Tiller Money template or community solutions. There may be implications to using this command that I am not aware of nor considered. Hopefully, the experts will provide feedback to this post should there be significant risk.

If you want to test out the command for yourself, I recommend using a backup copy of your workbook. Do not use it against you current financial template without testing it out first for yourself and having a backup file! As the saying goes; “Your mileage may vary.”

The command is found under the main “Review” menu as “Check Performance”.

The following are two links that I found that show what running the command looks like and explain it in a little more detail:

Make your workbooks more performant with Check Performance in Excel for Windows

Cleanup cells in your workbook

2 Likes
2

Has this been tested and approved by Tiller?

3

@tjones4852 No. Not that I am aware of. However, I would not expect them to test or approve it since it is just a Microsoft command and not a community developed solution.

4

I’ve been getting the “Optimize Performance” nag from Excel for months. Thanks to Clint, I finally made a copy of the template and executed the optimization command. The results were astounding:
Reduced file size from roughly 11 Mb to 5 Mb
Reduced launch time (from when Excel splash screen shows until file opens) from 2’58" to an mind-boggling 3 seconds.
I’ve only used the optimized template for a few days and as Clint said, I might encounter problems, but so far it uploads transactions and runs AutoCat without problems. I’m a very basic user and have not customized the template to any significant degree. But just thought I’d post this experience.

1 Like
5

Hi all….I did the same ‘improve performance’ step in my Excel spreadsheet and the spreadsheet opens quickly enough. But that was never really an issue for me. What is interminably slow is populating the Transactions sheet category drop down. I make a selection for a category, and seconds go by before it finishes. I have less than 2 years of data in the spreadsheet with about 3,200 lines. Is anyone experiencing that issue and any suggestions? Thx much!