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

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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).

Fragment of an Excel ribbon - Formulas tab with Calculation Options Expanded, and Manual and Format Stale Values highlighted.

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.

Animation of a spreadsheet where formula results get struck through when values they depend on are changed. Strikethrough disappears on press of the F9 key.
Stale Value Formatting in action

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.)

Excel Options dialog box open on Formulas - Calculation options, with Manual - Recalculate workbook before saving selected and highlighted.

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.

Fragment of an Excel ribbon - Formulas tab with Calculation Options Expanded, and Partial option highlighted.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Fragment of the Excel ribbon showing the Data tab, Queries & Connections group, with Workbook Links highlighted.

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.

Animation showing the Workbook Links pane and the process of breaking links.
Breaking links between workbooks using the Workbook Links pane

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

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.
Learn More
Power Query Course cover

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.

Fragment of an Excel ribbon on the Picture Format tab with Compress Pictures highlighted.

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:

  1. Make a copy of your file to keep the original safe.
  2. Delete one sheet at a time and save the file.
  3. Check the file size after each sheet is deleted to see if it changes.
  4. If the file size drops, you’ve likely found the “problem” sheet.
  5. 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.

Review tab with the Check Performance button

Check Performance helps you find and clear empty cells that still contain formatting data.

Workbook Performance pane with Empty cells to be optimized.

Simply press “Optimize Sheet” or “Optimize all” and any remaining formats are gone. A clean slate!

Workbook Performance pane with Workbook optimized message.

Add-in alternative

If you don’t have Microsoft 365, you can achieve this with add-ins, like Inquire.

Inquire menu with Clean Excess Cell Formatting highlighted.

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.

Conditional Formatting Rules Manager dialog box.

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.

Screenshot showing the Excel version in About Excel window

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.

Excel Options > Advanced, with Enable multi-threaded calculation highlighted.

Download Our Cheat Sheet

You’ve just learned some quick changes that can lead to a smoother, faster Excel experience.

Ideally, you’ll prevent issues by using these tips from the start. But if you’re working with a spreadsheet someone else created, these tips can still help you. Apply them to identify and resolve problems, and you’ll likely see your Excel speed improve.

Remember, not all tips will suit every situation. Evaluate your specific needs and choose the right ones for your tasks.

Download your free Cheat Sheet now! This handy guide summarizes all our top tips to help you speed up your Excel workbooks efficiently.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.