Is There a Dinosaur Sitting on Your Excel File?

I am handing few big excel data files lately and these files are loaded with vlookups, sumifs,sumproduct and truckload of loopy VBA code.

Running the VBA code was pain, excel drags as if a dinosaur is sitting on it. The files were so muddled that even the excel in-built functions to convert a range of cells to number takes 5 mins.

Trying to optimise the vba application in the excel file, I did the usual Application.ScreenUpdating = False and Application.Calculation = xlManual in the VBA to speed up all macros. This helped but not entirely.

The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates and re-calculates each cell and resolves dependencies. Excel recalculate’s whenever an event occurs (for example a cell is changed, a VBA code calls for re calculation or anything else). For each change the entire calculation process is carried out, so the trick to gain major benefit came from looking at all the formulas and optimising them.

It’s not easy but there are many fantastic resources that can help. Here are some of my favourite ones.

75 Excel Speeding Tips
Its a long List, many repeated but worth a visit.

Optimize VBA
Excellent tips to optimize vba code. You should do this before you move onto anything.

How to speed up calculation and Improve performance of Excel and VBA
These are excellent tips and must visit to know more about the calculations steps followed by Excel.