Fix the Most Common Causes of Slow Excel Files (Free Files Included)

[@gospelmoto2833]

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

[@MyOnlineTrainingHub]

🙏

[@Fabi_terra]

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

[@MyOnlineTrainingHub]

Thank you! You too!

[@MarkRizzoTheDragonWizard]

Mynda you are 'DA BOMB'. I continue to say how unbelieveablely generous you are with your experience and resources. A true delight to be subscribed to your channel. Thank you so much for today's video as it will help me enormously with some of the larger, more intensive workbooks I have been trying to deal with. Keep up the great EXCEL work!!!

[@MyOnlineTrainingHub]

You're most welcome, Mark! Glad this video will be helpful!

[@claudineyribeiro518]

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.

[@MyOnlineTrainingHub]

Thanks so much! All the best with your channel.

[@IvanCortinas_ES]

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.

[@MyOnlineTrainingHub]

Thanks, Ivan!

[@aiasaiascon3894]

Reall really valuable video - with lots of stuff to know about. Especially the issue with redundant formatting and scraps of data here and there. causing too much disruption sometimes. Named Ranges are indeed one of the most useful things in Excel, and it is really hard sometimes to skip it as a concept. Tremendous job! You and Excel are a Love!

[@MyOnlineTrainingHub]

Much appreciated! 🙏😊

[@MegaTrickykid]

great insights! Thank you very much indeed!

[@MyOnlineTrainingHub]

My pleasure!

[@RenierWessels]

Great video. Thanks for sharing Mynda!

[@MyOnlineTrainingHub]

Thanks for watching!

[@alexb9312]

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

[@GeertDelmulle]

…especially if you have ‘more than a few’ rules in there. And those rules tend to multiply if you work on the file afterwards!

[@MyOnlineTrainingHub]

Yes, good call on Conditional Formatting. FYI, recently the Excel team released an update to 365 for Conditional Formatting in tables to prevent fragmenting of the rules.

[@alexb9312]

@@MyOnlineTrainingHub 👍

[@TSSC]

@@GeertDelmulle I was once asked to troubleshoot performance in a large and complex calculation model. The main culprit was duplicated conditional formatting rules … more than 22000 on one worksheet! Cleaning that up removed the performance issue.

[@patrickschardt7724]

Yes if the only thing changing is the reference, try to use one rule for multiple ranges. I think the more rules a sheet has, like formulas, the slower it will calculate

[@h.ragavendranhariharan7085]

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.

[@MyOnlineTrainingHub]

Thanks for sharing and your kind words 🙏😊

[@teoxengineer]

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

[@MyOnlineTrainingHub]

Thanks! Can you elaborate on how you'd use VBA?

[@teoxengineer]

@@MyOnlineTrainingHub By using VBA, you can finish many logical tests in Excel very quickly. In a study, time was kept by using VBA against the formulas in the excel content, and the result was proven that the solution with VBA is very fast compared to the excel formula system.

[@FIRETIGER49]

Excellent video! Comprehensively covered all potential areas. Thanks!

[@MyOnlineTrainingHub]

Glad you enjoyed it! 🙏

[@grahamparker7729]

Another great video Mynda

[@MyOnlineTrainingHub]

Thanks, Graham!

[@captainteach007]

Thank you for another excellent video - I really enjoy your presentation style - you cover lots of detail in a very user-friendly way, making the information easy to understand.

[@MyOnlineTrainingHub]

Glad it was helpful! Thank you 😊

[@topnotchsupport9383]

This makes me want to learn Excel now 🥰| Excellent captivating presentation style!

[@MyOnlineTrainingHub]

Wonderful to hear!

[@chrism9037]

Thanks Mynda, great tips! That CEILING formula at 1:47 was insane

[@MyOnlineTrainingHub]

Cheers, Chris 🙏 I rewrote that crazy formula and didn’t use any IFs here: Excel Case Study - IF Formulas and what not to do! kzbin.info/www/bejne/pJ6alKJojpp1hrs

[@GSC-zz8zt]

@@MyOnlineTrainingHub I rewrote this monstrosity with named Lamdas, but the nested Ifs are crazy. Wondering if there's a way to reduce those nested ifs without destroying the integrity of the formula. =ROUND(IF(AM$4

[@marjoriefrancis4560]

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

[@MyOnlineTrainingHub]

Thanks you, and you 😊

[@barttitulaerexcelbart9400]

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

[@MyOnlineTrainingHub]

Great to hear 🙏

[@patrickschardt7724]

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

[@MyOnlineTrainingHub]

Great tip, Patrick 🙏If you format your data in a Table the Conditional Format will automatically copy down, so no need for dynamic array formulas. BTW, the fragmenting issues that used to haunt CF in Tables has been fixed in 365. If you must use a dynamic array, try using INDEX instead of OFFSET as INDEX is not volatile. See point 4 in this tutorial: www.myonlinetraininghub.com/excel-dynamic-named-ranges

[@luisquismondo]

Great content and great presentation. Thanks.

[@MyOnlineTrainingHub]

Thank you!

[@jaisbr]

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.

[@MyOnlineTrainingHub]

Ah, yes good point. User Defined Functions are typically way less efficient than the build in functions.

[@YvesAustin]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@andrewerlewein7160]

I have this exact problem, and now I know why and how to mitigate. Thank you.

[@MyOnlineTrainingHub]

So pleased it was helpful!

[@JaimeHaddad]

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

[@MyOnlineTrainingHub]

So great to hear 🙏

[@IamTheReaper911]

I approve this message 👍 💯

[@MyOnlineTrainingHub]

😁🙏

[@craigwhitthread3050]

Very good hints and tips. 🙂

[@MyOnlineTrainingHub]

Thank you 🙏

[@MikeCummings-ki2nk]

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.

[@MyOnlineTrainingHub]

Awesome to hear!

[@koltregaskes8385]

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

[@MyOnlineTrainingHub]

Great idea!

[@gnanpra3092]

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

[@MyOnlineTrainingHub]

Glad to hear that!

[@hanasuli613]

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

[@MyOnlineTrainingHub]

According to Microsoft it is recommended to minimize the number of used cells in functions like SUM and SUMIF. Calculation time is proportional to the number of used cells (unused cells are ignored). Therefore, it shouldn't matter if you select the whole column or not.

[@Muuip]

Thanks! Just loaded 10M rows!

[@MyOnlineTrainingHub]

Awesome!

[@pierreblanchard9752]

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

[@MyOnlineTrainingHub]

If you're merging tables, then with Power Query. If you want to create relationships between the tables i.e. one is a fact table and one is a dimension table, then use Power Pivot. You can use Power Query to merge a dimension table with a fact table, but typically it's better (results in a smaller file) to keep them separate and use Power Pivot relationships.

[@nicein2000]

Awesome video. I use Excel for a checkbook register, and I switched from using simple sum and subtraction formulas for the total column and went to OFFSET to prevent #REF errors when I wanted to cut/paste register entries around. But OFFSET is volatile and really slows down my Excel checkbook. Is there an alternative?

[@MyOnlineTrainingHub]

Thanks! The alternative to OFFSET is to use INDEX for dynamic ranges as explained here under number 4: www.myonlinetraininghub.com/excel-dynamic-named-ranges

[@matroosoft4589]

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 😔

[@MyOnlineTrainingHub]

Yes, they have turned it off while they deal with some issues it was causing 😢

[@erikguzik8204]

That is odd, it works for me if i have a list, say the days of the week, i type T, i get Tuesday, and Thursday showing up in the list

[@matroosoft4589]

@@erikguzik8204 that's odd indeed. Read a lot of complaints of people where it no longer worked. Maybe they turned it back on? Are you working in the desktop application or in Excel online?

[@erikguzik8204]

@@matroosoft4589 Desktop, so maybe that is why. I don't recall mine not working at any time, but most of my Workbooks I use are using a USERFORM drowdown too, but even the ones i have on just the worksheet, i think have always worked.

[@kleinboertjie]

Thanks for the info

[@MyOnlineTrainingHub]

My pleasure!

[@mozgus79ify]

My big workbook works OK, but my Power Query loads slowly. Any tips for that? Source files stored on the Sharepoint

[@MyOnlineTrainingHub]

There could be many reasons a query is slow. This post has some ideas you can try: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query

[@mozgus79ify]

@@MyOnlineTrainingHub thank you, deselecting data preview made a huge difference

[@ivanbork4175]

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.

[@MyOnlineTrainingHub]

Hi Ivan, it's not clear where you're wanting to do the count, in PQ or once loaded or whether it's of the grouped rows or pre-grouped rows. Due to the volume of comments I get, I won't see any replies to this thread, so please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@roywilson9580]

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.

[@MyOnlineTrainingHub]

Thanks so much, Roy! Great to hear you're making use of Power Query.

[@kwanhangcheung691]

Very good❤

[@MyOnlineTrainingHub]

Thank you!

[@ahmed007Jaber]

i am noticing a trend in all excel files if i want to save or create a new one ctrl+n takes ages, any ideas why?

[@MyOnlineTrainingHub]

I hadn't noticed that. Not sure why it would be. If you're using 365, maybe it's connecting to the cloud for some reason and this is causing the delay.

[@erikguzik8204]

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.

[@MyOnlineTrainingHub]

Great tip! Thanks for sharing.

[@ExcelWithChris]

Thanks!!!

[@MyOnlineTrainingHub]

My pleasure, Chris!

[@TSSC]

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.

[@MyOnlineTrainingHub]

Thanks for sharing! I've had shapes propagate in a file like Gremlins before too.

[@jtmh31]

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!

[@MyOnlineTrainingHub]

I agree, most people still using VLOOKUP in Excel 2021 or 365 are doing so because they haven't heard about XLOOKUP. Let's spread the word! Are you sure about this: "Both 2016 and 2019 can calculate an XLOOKUP". My understanding is if you open a file with XLOOKUP in Excel 2019 or earlier, the formula will still display the result, but if you edit the formula, it will return an error. I can't test it though as I don't have earlier versions of Excel anymore.

[@jtmh31]

For Excel 2016/2019 users, you are correct they can't edit or create new formulas. The formula begins with "xfn:" and will return the calculation. I always ask a client which version of Excel they have first, and what if that is consistent throughout their organization. If they tell me 365, and they're using VLOOKUP, I tell ask them how much do they love counting columns for the index number. Then show them XLOOKUP. You can see the "I've been doing it the hard way all this time" look on their face.

[@jtmh31]

Also, for users that still insist on INDEX/MATCH, from what I have been able to find, it's better for larger datasets because it calculates faster(?), but I have not had enough data in a file I've worked with to test that. Have a great day!

[@rogeriopenna9014]

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?

[@MyOnlineTrainingHub]

Possibly. Certainly worth a try.

[@koltregaskes8385]

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?

[@MyOnlineTrainingHub]

I'd say there's definitely an alternative to using INDIRECT they way you describe, but it's difficult to propose alternatives without seeing your file. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@Krypto_Knight_33]

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

[@MyOnlineTrainingHub]

Yes, some formulas are low impact. It's usually the lookup and logic type of formulas that cause the most problems. Conditional Formatting is a good one.

[@petr6617]

Power Query is not always the answer and the solution, it's still a "baby". Power Query doesn't know search functions (it solves via join or lists). Power Query is slow (see if buffer is the way to go). Power Query can't do a lot of things (Select Case instead of Else If, Horizontal Append, extended editor is terrible, parameter/security problem, relative paths!!! etc). It's not always possible to move data from a worksheet into Power Pivot's data model. Otherwise, Mynda, that's a nice summary :-)

[@MyOnlineTrainingHub]

True. Every tool has limitations, but IMO Power Query has far more benefits than shortfalls.

[@pedrodfg]

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.

[@julieo4580]

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

[@MyOnlineTrainingHub]

Sounds like your files are very large as I don't see any lag with my files that are on SharePoint, but they are typically small.

[@h.esther9400]

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!

[@MyOnlineTrainingHub]

That's a shame. Perhaps you'd be best to download the eBook instead. You can get it here: www.myonlinetraininghub.com/how-to-improve-excel-performance#file

[@henryg5735]

I agree, the background music is distracting and unnecessary. I do hope it doesn't become the norm.

[@h.esther9400]

@@MyOnlineTrainingHub Thanks! Very interesting, and I love that the solution is so often to use Power Query! It would be nice to have a similar video on making queries more effective.

[@NoTengoIlusiones]

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

[@zdzislawkes]

Sorry but I do not understand. Do You prefer functions Index/Match in large datasets over relationships?

[@NoTengoIlusiones]

@@zdzislawkes to be clear : "Over vlookups"

[@MyOnlineTrainingHub]

Lots of INDEX & MATCH formulas can still result in slow workbooks if you have enough of them. If so, use Power Query.

[@NoTengoIlusiones]

@@MyOnlineTrainingHub ​ @MyOnlineTrainingHub Hi Mynda, tnx for your kind reply. The statement I wrote is based on real life data (vlookup vs Index/match). From time to time I've to analyze comms between servers, +1 million lines with several columns. These large files are slow (and I have a powerful machine i7 32GB of ram etc) due to size. Index /Match was the way I found that can digest large amounts of data. vlookups I've simply stop using them. Another tip to increase speed on workbooks it's to avoid tables. As you explain excessive format slows down (included in tables), but tables also create variables (that are arrays) .... cheers ps- and thank you for your wonderful work.

[@GeertDelmulle]

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

[@MyOnlineTrainingHub]

😆 nice story!

[@YogeshkSharma-i1c]

🎉

[@MyOnlineTrainingHub]

🙏

[@aloobi]

Another reason is excessive use of objects such as shapes.

[@MyOnlineTrainingHub]

Yes, good point. Thanks for sharing.

[@zaighamuddinfarooqui1705]

👍👍👍👍

[@MyOnlineTrainingHub]

🙏😊

[@pawelsadoch5394]

❤

[@MyOnlineTrainingHub]

🙏

[@mrgod5139]

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

[@MyOnlineTrainingHub]

🤣

[@steven.h0629]

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

[@MyOnlineTrainingHub]

Great idea 👍