There’s nothing more annoying than when a document you’re working on just stalls. Business analysts grow accustomed to this with Excel, especially for reports that become more complex over time as they add more rows of data, formulas and macros (and sadly, more errors).
One client recently told us that she used to come into the office early on Monday to run one report, then leave the company headquarters to get coffee and come back an hour later. Even that wasn’t always enough. Sometimes the report would still be churning data. Other times the report errored out, and she would have to restart the whole process. This report itself was years old, and had been touched (and coded) by many different developers.
It was in desperate need of some TLC. Our team at eAlchemy often gets S.O.S. calls from our clients with this problem: Poorly structured, legacy Excel reporting tools fraught with errors that need to be rebuilt to reflect the business needs of today.
Related
- BLOG – Learn how eAlchemy used Excel as a part of a supplier intelligence tool that improved a leading food producer’s profit margin — and spared its planning team a lot of wasted time.
- BLOG – Dear CIOs: Stop shunning Excel — and your internal customers
In the case of the above, our team worked some reporting wizardry to rebuild that report so that it now runs reliably in under 5 minutes. The key word here is reliably: In addition to the time savings, the report no longer errors out.
There are many ways to optimize Excel so that reports will run faster — and ultimately minimize wasted time (and frustration). Here are eight of our favorites:
1. Use VBA Arrays to manipulate large quantities of data
One client came to us recently with a worksheet that was exporting more than 100,000 rows of data as a text file for downstream data consumption. As part of the export process, a pipe delimiter was being added to the end of each cell for downstream systems. The export process looped over each cell in the worksheet’s UsedRange and added a pipe to the end. This process took over 25 minutes just to add a simple delimiter to each cell of data.
The old code looked like this:
Const Delim = "|"
Const StartRow = 1
Const EndColumn = 51
Dim RowCount as Double
For i = StartRow to Startrow + RowCount
For j = 1 To EndColumn
Worksheets("Sheet").Cells(i,j).Value = _
Trim(Worksheets("Sheet").Cells(i,j).Value)
Next
Next
This method is problematic because addressing each object (cell) one at a time in a large dataset is incredibly slow. So, we eliminated that process entirely.
Rather than slowly looping over each cell in the worksheet range, we simply passed the range into an array, added the delimiter to each index of the array, and then pasted the entirety of the array back into the worksheet.
The new code looks like this:
Const Delim = "|"
Const StartRow = 1
Const EndColumn = 51
Dim RowCount as Double
Dim arr()
arr = wsSheet.Range(wsSheet.Cells(StartRow, 1), _
wsSheet.Cells(StartRow + RowCount, EndColumn))
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
arr(i, j) = arr(i, j) & Delim
Next
Next
wsSheet.Range(wsSheet.Cells(StartRow, 1), _
wsSheet.Cells(StartRow + RowCount, EndColumn)) = arr
This simple method led to a huge performance improvement. The output of the report decreased from from 25 minutes to just over 10 seconds.
2. Use Prep helper modules
Excel has many processes that are unnecessary during the running of code. To speed things up, we use what we call Prep helper modules. These helper modules have functions that are called right before big processes are run to “prep” the application for performance. Here’s some sample code we used from a recent project:
Application.Calculation = xlCalculation Manual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual turns off auto-calculations. This is essential when your workbook has a lot of formulas and you are transferring new data to these cells.
Application.ScreenUpdating = False turns off screen updating. Excel will constantly be updating the screen when you run your procedures unnecessarily. This can be a big hit on performance. It is much more efficient to update the screen once your code is finished.
Application.DisplayStatusBar = False turns off the status bar. If you do not need to show users the different statuses while code is running, it is a good idea to just turn this off.
Application.DisplayAlerts = False turns off messages and alerts when code is running. If you are running a process that would normally cause Excel to display an alert to the user, you can set this to false if you do not want the user to see alerts.
ActiveSheet.DisplayPageBreaks = False turns off Excel’s recalculation of page breaks each time the count of rows or columns is modified.
Make sure you are aware that these are off when your code is finished, and to turn them back on when necessary!
3. Work from top left to bottom right
Excel formulas always calculate in reading order, from left to right, top to bottom. If you have formulas that are referencing data on the same sheet (a good idea), it’s best to have that data above and to the left of the formula. You want the dependent data to load before the formulas that reference them. This provides a smoother flow of calculations.
4. Avoid excessive Volatile Functions
Volatile Functions recalculate each time there is an update to a worksheet, even if there is no dependency. These function values cannot be assumed to be the same at all times. As you might guess, having many of these Volatile Functions in a worksheet can significantly hamper performance.
It’s best to limit using these Volatile Functions when possible:
Offset()
Cell()
Indirect()
Info()
Today()
5. Use INDEX and Match instead of VLOOKUP
VLOOKUP is perhaps the most commonly used advanced formula in Excel workbooks. It is extremely useful when you need to find data in a range of cells.
However, in large, unsorted data sets, VLOOKUP’s performance takes a hit. A common alternative to VLOOKUP is doing an INDEX-MATCH, which will increase performance but only slightly. A much faster way is to break up the INDEX-MATCH into two separate formulas by using the same stored MATCH many times with INDEX.
For example, let’s say you have a worksheet with many formulas that need to return data from the same row or column.
First you want to run a MATCH to find the position, and then store it in a cell, like this:
(A1) =MATCH(LookupValue,LookupArray,0)
Then reference the value in the stored cell with INDEX.
=INDEX(Array,$A1, Col_num)
The beauty of this method is simple: you can greatly reduce the amount of functions that need to be calculated. In normal INDEX-MATCH, you have two formulas in each per function, but with two separate formulas, you can keep referencing the MATCH.
6. Check UsedRange to identify unnecessary rows/columns
The UsedRange contains all the cells that have ever been used in a specific worksheet. Sometimes the UsedRange of a worksheet can be greatly extended — and operations that include UsedRange will therefore also be extended. This can happen if the user accidentally holds enter while in a worksheet, or if a large number of rows are pasted into a worksheet erroneously, or a number of other random reasons. UsedRange can help you identify all columns and rows that are empty or not being used — and then you can remove them, which will make a big performance difference.
7. Excessive use of conditional formatting
Everyone loves conditional formatting. It can be very useful to point out any important metrics in your large reports by highlighting text or adding color to cells. However, if you notice a slowdown in your spreadsheet, check the conditional formatting. Sometimes conditional formats get created on top of each other, and you have multiple rules serving the same purpose.
8. Check worksheet for extra shapes
Sometimes when importing shapes or copying them from one worksheet to the next, extra shapes accidentally can be added to a workbook — making the file size larger and bogging down performance.
We had one client who was running a process that copied the entirety of one worksheet to a second worksheet where the data was scrubbed further. The original worksheet had a button on it that was also unnecessarily being copied to the second sheet. Over time, this added a lot of these duplicate buttons to the second worksheet. The tool in question was run a couple of times per month, and had been in use for quite some time. As a result, the derivative worksheet had more than 16,000 buttons pasted on top of each other.
Finding and deleting extra shapes can greatly reduce the size of your workbook, and increase performance.
To find all the shapes on a worksheet, follow these simple steps:
- Go to Home > Find & Select > Selection Pane
- Click “Show All” (as shown below)
Need some wizardry to optimize your Excel reports?
Implementing some or all of above tips in your Excel report is guaranteed to improve performance, reduce errors, and save everyone time. If you need some advice on how you might re-tool some of your business intelligence reports — from Excel to Power BI and beyond — we can help. Email us and we’ll set up a time to talk.
