Optimize Formulas
You’d be hard pressed to find an Excel file without any formulas. But overly complex formulas can significantly slow down your workbook.
Long nested formulas sure are impressive, but they are also difficult to read, and, crucially, can take much longer to calculate. It’s better to break them into smaller, manageable pieces using helper columns or defined names.
You should also avoid referencing the entire column in a formula. Instead of (C:C) use (C1:C3000) or until wherever your data is.
Use the most efficient function possible to get your results. Reduce workarounds.
Too many nested IFS that could be replaced with INDEX/MATCH, or IFs and SUMs in one formula instead of SUMIFS, using VLOOKUP on a huge matrix – all of these can impact the speed of your calculations.
If you have Microsoft 365 you can take advantage of the new functions that have been designed with efficiency in mind. Functions like FILTER, SORT, VSTACK, TEXTSPLIT solve problems that previously required a complicated formula combining several different functions.
Dynamic arrays are also much better for performance than the old Ctrl+Shift+Enter arrays.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Avoid Volatile Functions
Functions like:
- NOW
- TODAY
- INDIRECT
- OFFSET
- RAND
- RANDBETWEEN
- RANDARRAY
recalculate every time you make any change in your workbook.
Even if you’re doing something “innocent”, like entering values in an unrelated cell, deleting or inserting a row or column, filtering, renaming or moving a worksheet, those volatile 💥 functions – because that’s what they’re called – will recalculate, using up resources. If your file is slowing down, be aware they might be the cause.
That is not to say you should never use them. They serve their purpose and can be extremely helpful. But don’t overdo them and if there is an alternative solution, go with that.
If you need to use them to generate one-off results (for example some random numbers using the RAND family of functions), paste them over with values. That’s actually good practice for any one-off formula, not just the volatile ones.
Use Manual Calculation Mode
If your workbook is freezing every time it recalculates, you can disable automatic calculations (under Formulas > Calculation Options).
This will minimize the impact of volatile functions and other “heavy” calculations. You can continue working without waiting for all the formulas to calculate whenever you make a change. Use the shortcut F9 to calculate only when needed.
If you’re worried you might forget to run “Calculate Now” and your values will not update, you can take advantage of a new feature (available to Office Insiders as of October 2023) – Stale Value Formatting. Selecting this option applies strikethrough formatting to any cells containing values that need your attention because they are dependent on values that changed.
It might take some time getting used to. But it wasn’t your colleague who crossed out your values, it was Excel’s Stale Value Formatting. Recalculate the sheet and all is well.
You can also make sure that “Recalculate workbook before saving” is enabled by going to File > Options > Formulas > Calculation options. (Recommended only if Autosave is off – otherwise it defeats the purpose.)
Partial Calculation Mode
If you don’t have a problem with volatile functions but are using data tables (not to be confused with a “normal” Excel table) to calculate multiple variables or Python dataframes, you may want to change the Calculation mode to Partial. Before the introduction of Python to Excel, this used to be called “Automatic Except for Data Tables”.
Both those features can affect performance, as they conduct multiple dependent calculations. Each change can start a computational chain reaction. It might be better to switch to partial until you’re done with all the changes and are ready to recalculate.
Featured Course
Excel Essentials for the Real World
Keep External Links in Check
Links to other workbooks can slow things down. Make sure to update references that changed or break any links you no longer need using the Edit Links feature (recently renamed to Workbook Links in Microsoft 365). In this video I show you how.
You will find it in the Data tab > Queries & Connections. If you see “Workbook Links” instead of “Edit Links”, you will get a side pane instead of the old dialog box, but the principle is exactly the same.
If you want to bring over data from another workbook, consider using Power Query rather than creating external references.
Featured Course
Master Excel Power Query – Beginner to Pro
Reduce File Size
Keeping raw data and analysis separate is a good way to keep the file size low, and a good practice in general. Power Query lets you do just that. I’ve covered it extensively in my tutorials and courses.
Other size-reducing steps involve deleting any unnecessary worksheets and cells. Often, when we’re working, we create temporary sheets, or write a quick formula on the side to double-check something. And sometimes, even if we delete the values, some metadata remains. Press Ctrl + End on each sheet to determine the actual used range of your sheets. You may be surprised that it’s not where you thought it was, when the selection jumps to a (supposedly) empty cell. Delete any rows or columns that you don’t need.
If you have images in your workbook, compress them. Select the image, go to Picture Format and (in the Adjust group) click Compress Pictures.
You’ve taken all the steps above and the file still appears too large? Sometimes you have to play a detective. When I’m not sure where to start, I try to identify the sheet that’s causing the most trouble. Here’s a simple way to do it:
- Make a copy of your file to keep the original safe.
- Delete one sheet at a time and save the file.
- Check the file size after each sheet is deleted to see if it changes.
- If the file size drops, you’ve likely found the “problem” sheet.
- Now you can focus on fixing issues on that particular sheet.
Avoid Unnecessary Formatting
Admit it – how often, when highlighting some data, you end up highlighting the entire row?
Or you remove values from cells without clearing all, unaware that you’re potentially leaving formatting metadata behind?
All of this excessive and hidden formatting increases the file size and potentially impacts performance. But (with Microsoft 365), you can get rid of redundant formatting in 2 clicks 🤯.
If you go to the Review tab in Excel for the Web (or Desktop for Office Insiders as of October 2023), you will find the Check Performance feature that I demonstrate in this video.
Check Performance helps you find and clear empty cells that still contain formatting data.
Simply press “Optimize Sheet” or “Optimize all” and any remaining formats are gone. A clean slate!
Add-in alternative
If you don’t have Microsoft 365, you can achieve this with add-ins, like Inquire.
To activate the Inquire tab on the ribbon, go to File > Options > Add-Ins. Select COM Add-ins in the Manage box, and click Go. Next, check the box next to Inquire Add-in and click OK. Note: Inquire is available with enterprise editions of Excel.
Bear in mind, too many add-ins can also slow down the workbook.
Too much conditional formatting
Unfortunately, “Check Performance” doesn’t work on conditional formatting and too many conditional formatting rules can also cost you speed.
Don’t apply it to the entire column/row unless you absolutely have to. Try to limit it to the range actually in use.
To find the cells with conditional formatting, go to Conditional Formatting > Manage Rules, where you can check sheet by sheet and delete or edit rules.
Bonus Tips
There are also some things you can implement on the system level.
If possible, keep your Excel updated to the latest version. Microsoft 365 continues to improve the Office suite with performance optimization in mind. In 2023 there have been several “unexciting” background updates to conditional formatting, recalculation options, filtering etc., all of which should speed up your workbooks.
Note: If you’d like to learn more about those updates, go to Release notes and look for:
- Reducing unwanted fragmenting of conditional formatting rules
- Optimized Excel recalculation on devices with constrained resources
- Reducing slowness and freezes when multiple workbooks are open
- Faster filtering when cells contain unique or duplicate rules
Opt for 64-bit Excel, if your hardware allows it. It makes a significant performance difference, especially when working with large files.
The 32-bit environment is limited to using 2 GB of RAM for all concurrent processes. The more data it has to process, the sooner it will start slowing down. Meanwhile, there are no hard limits on the 64-bit application (you are only limited by your system). Since it can access more memory, it’s able to process more data and perform more complex calculations more quickly.
You can check which one you have by going to File > Account > About Excel.
And finally, you can go to File > Options > Advanced > Formulas, and make sure that you have multi-threaded calculation enabled. This allows Excel to use multiple processors on your computer to perform several calculations at the same time, instead of one by one. After all, it’s so much faster to move boxes as a group, when everyone takes one, than having to do it alone.
Final Thoughts
So there you have it! A few quick changes and you’re on your way to a smoother, faster Excel experience.
Best is of course, to never end-up with a slow spreadsheet by following these tips from the start. However, in many cases you might not be the creator of the original file. Put some of these tips to practice – identify and fix the problems – and you’ll surely see an improvement in speed.
Remember, these tips are not one-size-fits-all, so analyze your specific situation and apply the ones that make sense for you.
Cheat Sheet
Download the handy Cheat Sheet summarizing all the tips:
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
