How to speed up slow Excel files

 

How to speed up slow Excel files

 Fix Slow Excel Files: Simple Steps to Enhance Performance

Do you often find yourself drumming your fingers waiting for that Excel file to finally load?

Many of us deal with big files that have lots of sheets and thousands of calculations on each one.

I've had my share of anxiety attacks at the office hoping the file will finally open and it's not broken.

We know the stress, so we've put together some tips to help lighten the load on your spreadsheets:

1. Optimize Formulas 

Simplify complex formulas that can bog down your workbook. Break them into smaller, manageable pieces using helper columns or defined names. Also, avoid referencing entire columns. Instead of (C:C) do (C1:C3000). On a related note...

2. Avoid Volatile Functions 

Functions like NOW, TODAY, INDIRECT, RAND, OFFSET recalculate every time you make any change in your workbook. Use them sparingly! That is not to say *never* use them. They serve their purpose and can be extremely helpful. But if your file is slowing down, be aware they might be the cause.

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 actually applies to any one-off formula.

Another method of limiting the impact of volatile functions would be to...

3. Use Manual Calculation Mode 

You can disable automatic calculations (under Formulas > Calculation Options) and recalculate (using the shortcut F9) only when needed.

That way, you can continue working without waiting for all the formulas to recalculate whenever you make any change.

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 (currently in Office Insiders) - 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.

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

In Microsoft 365 "Edit Links" has been renamed to "Workbook Links".

You will still find it in the same place (Data > Queries & Connections) but the experience has also been refreshed, with a side pane replacing the old dialog box.

The "old" experience looked like this. While the interface has changed (it may be a while before you see the change in your version), the principle is the same.

5. Reduce File Size 

Starting with the obvious - delete unnecessary worksheets and cells.

If you have images in your workbook, compress them - select the image, go to Picture Format and (in the Adjust group) click Compress Pictures.

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.

One more way to reduce file size is to...

6. Avoid Unnecessary Formatting (esp. Conditional 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 .

A while back, I introduced you to the Check Performance feature in this #shorts.

Back then, it was only available in Excel for the Web. It has since made its way into the desktop version of Excel for Microsoft 365 (Insiders Beta for now).

Check Performance helps you find and clear empty cells that still contain formatting data. A clean slate!

Unfortunately, it doesn't work on conditional formatting. Too many conditional formatting rules can also slow the workbook down.

That takes me to the bonus tip: if possible, keep your Excel updated to the latest version. Microsoft 365 continues to improve the Office suite with performance optimization in mind. If you check release notes, you'll find a lot of "unexciting" background updates to conditional formatting, recalculation options, filtering etc., that can make a major difference.

So there you have it! A few quick changes and you're on your way to a smoother, faster Excel experience. Try these out and let me know how it goes!

Remember, these tips are not one-size-fits-all, so analyze your specific situation and apply the ones that make sense for you.

What other tactics do you use to overcome Excel slowness?  Feel free to reply and let us know.

​

Shortform makes extracting key insights from books a breeze.

 Dive into various genres: Explore top picks like “Thinking, Fast and Slow” or “How to Win Friends and Influence People” to kickstart your learning journey.

 Grasp and Retain Quickly: Digest core concepts in a time-friendly manner, making learning effortless and effective.

 Same Cost, More Value: For the price of one book per month, unlock a treasure trove of over a thousand book guides.

Pair it with Shortform AI to breeze through blogs and articles, summing up your reading in a snap.

Ready to step up your knowledge game? Grab a free trial at https://shortform.com/leila. "Between the Sheets" readers get an exclusive 25% off the annual subscription, that’s 3 free months!

A hearty thank you to Shortform for sponsoring today’s newsletter. 

BY \ Leila at XelPlus