Excel is slow? Here are possible causes and ways to make Excel run faster

You’ve spent days in perfecting your Excel model. Sophisticated formulas spanning multiple Workbooks are generating the results you need. But it is now heavy with long delays and response times. What could be the reason and what can you do about it?

The magic of live formulas has its toll

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.

Main contributors to slowing Excel files

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.

• Volume. Makes sense, isn’t it? The more connected cells you have – the more you require from your system upon every change. I’m considering any calculated objects here, such as Pivot Tables and Charts. A graphical object (such as a chart) requires also resources to be repainted on the screen – that’s the presentation layer.
• Formulas. Some formulas are more resource-intensive than others. Complex formulas with many nested formulas are a burden. Array formulas are a big burden and volatile formulas (those that gets calculated upon every change regardless of their dependencies: NOW(), DATE(), INDIRECT(), INFO() and some others) – also has their price tag.
• Conditional Formatting. This is a big one. In addition to sorting out the formulas (as with any other “cell”), there’s the presentation layer that needs to be refreshed.
• Linked Workbooks. Formulas reaching out to other Workbooks will take longer to calculate.
• Network. Your Excel file may reach out to resources over the network (either in your organization or over the Internet). Requesting data over a network is significantly slower that working within the boundaries of your own machine.
• Poor VBA code! This has the potential to be a huge breaker. While we presume Excel’s built-in formulas are efficiently implemented by Microsoft’s engineers, those Macros we wrote are subject to our own programming skills and experience. As you’ll see, VBA can be a culprit, but also the solution, in some cases.
• Your system’s resources. Obviously, more compute (CPU) and memory are always welcomed!
• MS–Office 32bit. MS-Office 64bit allows Excel to take full advantage of the memory space you have. If you really run into memory limitations – there might be a good hope by upgrading Office to the 64bit version. Read my Blog post here on how to make your VBA code compatible for both Office 32bit and 64bit.

It all starts with a good design

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.

Push more to efficient VBA code

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.

Formulas-related tricks for faster calculation

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 😉).

Don’t reference cells you are not using

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.

Simplify formulas

A lot of potential is hidden here, and some experience to know what’s better usually helps. Few tips:

• Replace VLOOKUP with INDEX + MATCH, or use the newer XLOOKUP function.
• Replace IF() formulas with math arithmetic and Boolean expressions. This can take many forms. For example, if either A2 or B2 is zero and you need the other one greater than 0:
  Instead of =IF(A2>0,A2,B2)*$A$1
  Write: =(A2+B2)*$A$1
• Break complex, nested formulas into separate formulas with the use of helper cells. The following example shows a double hit combining a helper column and Boolean expressions from the previous point:

  

• Reduce repeating formulas. If you are comparing a cell value in each row of a table to MAX($A$1:$A$2500) – take out the MAX part to a single cell outside the table, and reference this cell in each formula along the column’s cells.
  Do the same with the Now() function if you’re using it in many places in your Workbook.
• Avoid Array Functions if possible. Smart use of the SUMPRODUCT function can do wonders. Read all about it in this Blog post of mine.
• Use IFERROR() instead of IF() with ISERROR().
• Use MAX() instead of IF() when comparing for the highest value. Example:
  Instead of =IF(B3>C3, B3, C3)
  Write: =MAX(B3,C3)

Reduce Formatting

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!