Are your Excel Spreadsheets so big and slow that you could literally go and make a cup of coffee while the workbook updates? Good news, there is a simple and quick solution to Speed Up Your Excel.
I recently had a request from a client to assist them with improving the speed of their Excel Workbooks. While working on their data it was taking so long for their workbooks to update that it was seriously affecting their ability to deliver their financial reports. Running the initial report took nearly 6 hours (they would run it overnight). Each time new data was entered, it would take a couple of minutes for Excel to recalculate, equaling hours per workbook! It doesn’t take an expert to see that this is not good for business.
It didn’t take me long to find the problem, there were far too many worksheets in a formula heavy design. The formula used were old look-ups (e.g: Index & Match combination resolves quicker and uses less system overheads with more flexibility than the traditional VLookup formula) and too many nested IF statement variables. They weren’t using List referencing, Named Ranges and there were hardly any Pivot Tables (a great way to quickly summarise and analyse data, again, using less resources).
Newer Business Intelligence report writing methodology using newer software functionality greatly assists with the ever increasing volume of business data sources that need analysing and help to Speed Up Your Excel
That being said, you don’t have to rewrite all your reports just because they’re ‘old school’. Sometimes we just have to make do with what we’ve got. Luckily for you there is another easy way that one can use to speed up your Excel Workbook even if you really have to make use of a nested formula-heavy design.
Speed Up Your Excel by setting your Excel Workbook formula calculation to manual.
What that means is that Excel won’t update your formula until you tell it to. This leaves you free to open your Spreadsheet, edit your information, and, when you’re ready, calculate your formula.
By changing this setting I brought the report run time at my client down from over 6 hours to just over 15 minutes. They are now able to run the report, make edits, and, update their information multiple times in a day, something they previously couldn’t do.
Here’s how to set your Excel Workbook Formula Calculations to Manual:
Go to the Formulas Tab in your Excel Workbook Ribbon, then select ‘Calculations Options’
Set to ‘Manual’
NB: Don’t forget, now that Excel calculates your formula manually, every time you’re finished updating your spreadsheet or want to update your workbook, you will need to come back here and tell Excel to ‘Calculate Now’
Hopefully this tip helps to Speed Up Your Excel and make your life using Excel a bit easier 🙂