No doubt one of Excel’s magic is the “live grid” in which formulas connect cells, charts and formatting for real-time calculations and rendering. It Is so appealing, that we tend to forget that maintaining the web of connected cells requires resources. Compute and memory resources.
Connected cells are like a web of chained and inter-related nodes, with inter-dependencies. Automatically calculating this web of connected cells on every change you make to reflect a stable model at all times – is quiet a commitment Excel has made before us users.
However, as the web of cells grow big and complicated, this promise is hard to maintain, and we feel the toll on every change we make.
Some relief can be gained if we compromise and set calculation to be manual, but we lose the magic of real-time results we were promised.
When we are aware of the possible contributors to an Excel file being slow, we can already start thinking about solutions: reduce the cause if you can.
I often receive Excel files from customers that reflect a built-in mechanism of poor performance, due to poor design of the Excel solution in accordance with the business process it supports.
A typical example is one in which a new Worksheet (or Workbook) is created every month, for a monthly report, with ever growing set of linking formulas across all Worksheets. I’m not even talking about the manual time it takes every month to do and maintain that.
A better design for a repetitive structure / report would be to separate the data from the report template and have a VBA function generate the monthly report automatically every month, based on the data you conveniently maintain in data structures designed for data entry (perhaps with the use of User Forms). As each report is generated, your VBA program can do whatever you want with it: archive it as a separate, formula-less Workbook. Save it as a PDF and attach it to an Email, or else.
So much performance culprits can be eliminated this way, as well as your own time, and this tested program reduces errors, as it is tested over time and not relying on your manual actions.
In general, pushing more calculations from Worksheet cells to VBA code has the potential of significantly improving performance.
Sometimes you can identify even a single column with heavy toll formulas taking up 80% of your calculation time, that can be designed to be calculated with VBA and save you most of that time.
Incorporating VBA calculations needs to be done smartly, considering the business process, frequency and triggering events of the calculations. The web of cells do calculate automatically upon every change, but VBA functions needs to be called, and it must not become a nuance to remember calling them every time.
I will point to the Worksheet_Change() and Worksheet_SelectionChange() events that may come handy here. I will try to avoid User Defined Functions that are called within cells, as they are typically volatile.
Having said that, it is of course important that you employ efficient VBA programming, as the implications can be HUGE, as I demonstrate in but one example here.
When you design your model, go over the above list and see where you can eliminate potential resources-eaters up front. You will be surprised how easy it is sometimes, if only you pay attention to these issues in the planning phase.
The list here can be very large. I’ll try to emphasize the main culprits I see most of the time in my customers’ Excel files (the ones they built, not those received for me. Just to be clear on that 😉).
Are you writing formulas that reference a whole column: SUM(A:A)? Don’t.
Have you applied cells formats to a larger range that you need? Don’t.
Are you in the habit of selecting a complete column before you apply cells formatting? Drop it.
Remember that any cell you apply something to, stores that information, grow you file bigger and takes a toll in calculation time.
The only time I’d select a complete column (or row) is when I want to change its width, delete it or Shift-Drag it to switch places with another column.
To clear all info stored with unused cells, apply the “Clear All” option in the Editing group of the Home ribbon to the cells, or delete complete rows/columns.
A lot of potential is hidden here, and some experience to know what’s better usually helps. Few tips:
Formatting are definitely a deal maker – it can be worth a fortune in business value when used correctly.
However, they come with a toll, usually in both calculation and presentation (repainting).
If you have a loaded model, consider simplifying the formatting of cells and other objects, especially conditional formatting.
When writing conditional formatting formulas, try to see which of the above tips can be applied also here. Maybe one of the conditions rules can be eliminated by setting the default formatting of the cells to that look.
A common mistake is to apply some number format to many cells not really used. One such case is when selecting a whole column (A:A) and another is when formatting extra cells below a range to accommodate future values.
I strongly suggest you start working with Excel Smart Tables instead of just ranges, they have many benefits, and their formatting is automatically maintained efficiently when needed. Opt for one of the pre-formatted tables styles instead of applying different formatting to each column. In Smart Tables, formulas and formatting can be applied at the column level, and this reduces the toll even further.
Remember that formatting can also be efficiently applied using VBA. If, for example, you are processing a large table, you may want to populate raw data and at the end, based on the resulting report range, apply formatting with VBA.
Speaking of VBA, in next week’s Blog post I’ll elaborate on how to write VBA code that runs faster – stay tuned!
Be the first to be invited to the upcoming Excel VBA Inner Circle.
The first to enroll as founding members will enjoy an exceptional opportunity to become part of this professional club.
Click the button below to enlist yourself to the waiting list.