THIS is Why Your Excel Files are SLOW and How to FIX Them

Good morning Mynda, thank you for the updates and have a blessed day 🙏

Very good hints and tips. 🙂

I have a reset.all.last.cells button for dealing with other's worksheets with no end in sight 👍😎✊

Thanks Mynda, in my educational environment, the files are not that big, but this video can be of help for my students later. With workbook statistics you can easily find the last non blank cell...

Excellent video format. Great content, as always. The Check Performance part in the online version is very interesting.
Thank you for explaining the news and leaving us all these wise tips, Mynda.

Conditional formatting probably deserve a mention. They are considered to be volatile and as such can contribute significantly to a spreadsheet's poor performance.

great video Mynda! Packed with good tips! Thank you.

Thanks for the info

you just need the latest ryzen 9 processor and RTX 4090 graphics cards to load these excel files..

Great video. Is-it better to merge two files with Power Query or with Power Pivot?

Another reason is excessive use of objects such as shapes.

Dear Mynda
After seeing a ton of your very informative tutorials, I hope that I can reach out with one specific problem that I have with Power Query. That is the Group By function, whenever I use it is not possible to do a Count rows or count distinct rows. All other functions work well.
Perhaps it´s down to languages (Danish) if it´s not translated, although I´m using English version.
If you have any suggestions, and can find the time to give an answer, I’ll be very grateful.

Great content and great presentation. Thanks.

Here’s a small anecdote:
A few years ago I was contacted by a colleague with the following problem:
She: “I have an Excel file with a large table (all necessary) and I need to filter it and if I try to do that, my PC crashes.”
Me: 😲 oh?…
She: “and we took the problem to ICT and they can’t help me. So, I turn to you.” (I was the guy who knows Excel quite well…)
Me: Wait, what?!… What do you mean: “they can’t help you”?! 😮
Long story short: fairly large table, lots of formulas (lookup from other table),…
…made a pivot table of said table, and that can be ‘filtered’ in real time.
Best performance improvement “ever”! 😅
Wasn’t even hard. 😊
Ever since I upgraded to “local Excel guru, with general (local) recognition” 😊
I have many ‘Excel friends’: and they come find me, whenever they have serious Excel challenges (of which there have been quite a few over the years).

Don't using vlookups...deprecated them for years ! Index/Match behaves well in large datasets !

Thanks!!!

Thanks for the interesting video. I am with you on updating to Excel 365 - It is finally moving in the right direction and addressing some of the issues with laggy performance. As you stressed in the video, the very best way to improve performance and get over the 1.4M records limit is to use Power Query, I find myself doing more and more of the hwavy work with Power Query which with a little forethought can produce great parametised reports only requiring the user to input filter etc. criteria and hit refresh. Even more time savings can be made with a few simple lines of VBA to provide a button to refresh only the specific report the end user is interested in. Can't wait for your next video.

Another great video Mynda

Hi Mynda, did you notice that autocomplete is no longer working on data validation lists? It used to be that if you started typing, it would show a list of filtered values. Looking at my workbooks and reading from people online, it appears they removed this feature 😔

Very Interesting video thank you ! just one think the SUM.IF function is more faster if we select column instead range

Would you consider releasing a version of this video without background music? I love your content, but I’m not able to watch videos that have music, even if it’s at low volume. Thanks for all your brilliant tutorials!

Very good❤

❤

I approve this message 👍





💯

Thanks! Just loaded 10M rows!

How about excessive use of shapes and/or shapes with hyperlinks?
• Once, a client gave me file to analyse. It took perhaps a minute to open and each cell change took perhaps 10 seconds to take effect.
• What caused this was thousands of “invisible” shapes, each having a hyperlink to a webpage.
• The user had manually “scraped” data from a webpage by copy-pasting to Excel. The copied information included a shape per item (no fill or border colour) with a hyperlink … per copied item.
• After removing the shapes, the file opened quickly and responded without delay.

one of the biggest things i have found in peoples workbooks is excessive SHAPES, and Check boxes. They start off with a small file, 50-80 items, then they go to expand the WB, and copy/paste a few times, now have 500 or every way more than that, now the WB is bogged down and runs everything super slow.

Thank you Mynda.
Another important alternative way to reduce file size is to use VBA.

For custom functions, LET is pretty much a must use. The variable feature is nice but I think the reduced reference calculation is better…. Because it only calculates the initial reference, stores it, and the uses the stored variable

Great tips. I need to look at my conditional formatting rules in regards to dynamic arrays. There’s still no great way to allow for growth on dynamic arrays

Great tips Mynda, thank you! - Always pending to receive the Excel Newsletter on my inbox to see what new tips are in there 😀

Sharepoint slows all files. It is so bad to use with shared files.

great insights! Thank you very much indeed!

Dear Mynda, your videos are awesome. Just wanted to share my experience here. I was struggling to figure out what was causing slowness to an excel file which had about 10k rows of data. Removed all formats, data validation, conditional formats, etc but with no improvement in speed. Finally tried searching if there r any objects by using go to option and yes it was the culprit which caused all the trouble. After removing the object, great speed.

I find INDIRECT to be quite slow. I use it with XLOOKUP as the number of rows of data in most of my Excel sheets changes every day. Can anyone suggest an alternative ways to handle this?

Very useful video. I would love to have a video like this for PowerQuery, Data Model, PowerBi, cloud/not cloud (which are best for large datasets or data connections) and what are the bottlenecks (processor, RAM, connection...). We deal with Salesforce data, and the connector in PowerBi and Excel but running queries is very slow. 😞

I find formulae don’t change things much but conditional formatting creates much bigger file sizes.

My colleagues frequently use cell references to functions they have written in VBA. This causes terrible performance with large data sets. I rewrote everything using xlookup and power query and the performance is much better.

You're captivating Mynda, you're getting lovelier and lovelier each time I watch your videos. Thanks!

The only reason to use VLOOKUP is if the user has a legacy version of Excel such as 2013 or older. Both 2016 and 2019 can calculate an XLOOKUP, which has an optional argument If Not Found. No need for nesting in an IFNA(). Most companies I deal with have moved/are moving to Microsoft 365. At this point, the users I see using VLOOKUP are doing so only because they don't know of anything else. Or, they're using INDEX/MATCH to look right to left/have a lot of data to look through.

Also, 2019 and newer versions run Lookup functions up to 75% faster than before. The common Table Arrays load to logical memory once, versus for every lookup function. So, they're not the performance hit they once were. Have a great day!

👍👍👍👍

Hi from Brazil, I liked a lot your tuto. I am a vba programmer and just started my channel teaching how to work with OpenGL inside Excel through VBA.

Excellent video! Comprehensively covered all potential areas. Thanks!

I was out of options and I uploaded my sheet to MS excel online and it fixed it. There is a Check Performance under Review but I didn't use that. Excel opened up and mentioned something about empty textboxes. Its 100x faster.

Thanks so much, Mynda! Great tutorial! Have a lovely day! 😊

This was not that much of a problem in the past lets say T minus 20 years. Whilst we have better computing power, software performance like Excel just decreased or not taking the pace. Its ridiculous.

Thank you! really very helpful tips! Surely will help me.

I have an Excel someone else did which has 406 thousand objects. I think they are transparent.

I was able to select them with Find and Select Special, but the spreadsheet is so slow it crashed Excel while deleting that crap.

Wouldn´t maybe an online conversion program delete these objects when converting?