In this post, we look at increasing efficiency when using Excel in relation to calculation and cell opening times. Excel is a great tool for data analysis, but it can be slow when you need to perform complex calculations. In this post, we’ll look at how to improve the speed of your Excel calculations.
Improve Calculation Speeds
Some common problems with large Excel workbooks are lengthy load times and updates. There are two major reasons for this (apart from an inherently large amount of data): a large number of complex calculations or excessive formatting. Here’s how to increase your Excel productivity.
If your Excel file is taking too long to recalculate or update, here are a few things you can try:
Manual calculations
Turn off automatic calculation so Excel doesn’t automatically force a calculation after all model changes. This can help when you have a large number of calculations that require you to wait for Excel to calculate every time a change is made. Change the setting to Manual calculations in the Formula ribbon. You manually calculate the workbook at any time with F9.
Note – F9 recalculates the entire workbook. If you want to only recalculate the active sheet the shortcut is Shift+F9.
Saving the file as an Excel Binary File Format
Excel 2007 (and later) has introduced a new file format .xlsb. This file format considerably reduces file size and may also increase calculation speed without compromising any functionality of the workbook. This file format is useful in all instances, but of particular advantage in large files (i.e. 20+ MB).
Clearing styles and formats to improve calculation speeds
Remove unnecessary styles and formats to reduce file size and reduce opening times. If formats are not of significance, highlight the entire worksheet by pressing Ctrl+A and then click on “Clear Formats” (in the Home ribbon).
Other Excel tutorials you may also like: