Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).
Optimize Excel with the Excel Optimizer
The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.
This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.
See this video for a quick showcase of how the Excel Optimizer works.
Documentation
The Excel Optimizer consists of 2 features:
Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement
Below a screenshot of how the main window looks like:
Rules
Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):
Rule
Scope
Description
XLSB
Workbook
Checks if workbooks is in XLSB format if not suggests so
UsedRange
Worksheet
Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP
Range
Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula
Range
Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions
Range
Looks for volatile native Excel functions and suggests removing or replacing them
External links
Range
Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula
Range
Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error
Range
Looks errors within cells in the entire Workbook and suggests correcting them
