How to accelerate excel performance?

Efficiency is one of the key elements to evaluate any application. Excel has always topped the race in efficiency when compared to other spreadsheet applications. But there are some instances where Excel lags in performance which could affect productivity. In this article let us look at some of the key elements that would help to enhance the performance of Excel.

Excel 2007 and later versions have “Big grid” (1 Million rows and 16,000+ columns) compared to their previous versions. Since the capacity of the application has increased, there are more chances for performance degradation as we would be dealing with huge amount of data.

1. Switch to Manual Calculation mode from Automatic

By default, automatic calculation is the default option while working on Excel. In this mode, whenever we change the content of any cell in our workbook, all formula in all cells of all open workbooks would be recalculated. We won’t realize the time spent in formula recalculation as long as it is less than a tenth of a second, all put together. As this calculation time increases, annoyance starts to increase, especially for repetitive tasks. In this situation’s users can switch to “Manual calculation” option in the formulas tab. In this mode, formula would be recalculated only when ‘Calculate Now’ or ‘Calculate Sheet’ button is pressed.

2. Avoid Circular references

As calculations are single threaded, multiple references to one or more formula could slow down the performance. Minimizing the use of circular references can boost the formula calculation time.

3. Avoid links across workbooks

Try to avoid links between workbooks. If links are not removed, opening of that workbook could end up in evaluating the link from the source. This can easily increase the calculation time. Best practice is to paste special the formulas into values, which can save your memory and increase calculation time as well.

4. Write efficient formulas

Writing efficient formulas will add value to the calculation time. Nested formulas, array formulas, unsorted columns, redundant data can lead to performance degradation. For example, VLOOKUP and HLOOKUP with exact match would take more calculation time than approximate match after sorting the same data. So, better understanding of logic and using the right formula can increase Excel performance.

5. Use conditional formatting cautiously

We should be very cautious while using any formatting option in Excel, especially while using conditional formatting because of its volatile nature. Apply the formatting only to the used range and make sure you haven’t selected the entire column/row while applying these formats. This can increase Excel performance.

There are many other elements to be considered to improve performance of an application. The above mentioned are few among them and these are the key to accelerate the performance of an Excel application.

How to accelerate excel performance?

Check out our Tableau pages

Leave a Reply Cancel reply