THIS is Why Your Excel Files are SLOW and How to FIX Them
HTML-код
- Опубликовано: 7 июл 2024
- Slow Excel files that take forever to load or recalculate are a common headache for anyone who uses spreadsheets regularly. Fortunately, there are many tips and tricks you can use to improve Excel performance. In this tutorial, we'll identify the most common causes of slow Excel files and how to speed them up so you can improve productivity.
🔻 DOWNLOAD PDF HERE: www.myonlinetraininghub.com/h...
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
0:00 Why Your Spreadsheet is Running Slowly
0:27 Data Size
0:44 PivotTables
1:01 Array Formulas
1:45 Nested Formulas
2:31 Volatile Functions
3:04 Lookup Formulas
4:00 Conditional Functions
4:39 Defined Names
5:08 Error Handling
5:21 External Links
5:46 Formatting
6:22 Redundant Formatting
7:30 Excel Tables
8:10 Passowrd Protection
8:23 UDFs
8:37 Outdated Software 
Наука
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.
Thanks, Ivan!
You're captivating Mynda, you're getting lovelier and lovelier each time I watch your videos. Thanks!
🙏
great video Mynda! Packed with good tips! Thank you.
Glad you liked it!
great insights! Thank you very much indeed!
My pleasure!
Excellent video! Comprehensively covered all potential areas. Thanks!
Glad you enjoyed it! 🙏
Thanks so much, Mynda! Great tutorial! Have a lovely day! 😊
Thank you! You too!
Good morning Mynda, thank you for the updates and have a blessed day 🙏
Thanks you, and you 😊
Another great video Mynda
Thanks, Graham!
Great tips Mynda, thank you! - Always pending to receive the Excel Newsletter on my inbox to see what new tips are in there 😀
So great to hear 🙏
Great content and great presentation. Thanks.
Thank you!
Very good hints and tips. 🙂
Thank you 🙏
Thank you! really very helpful tips! Surely will help me.
Glad to hear that!
Thanks Mynda, great tips! That CEILING formula at 1:47 was insane
Cheers, Chris 🙏 I rewrote that crazy formula and didn’t use any IFs here: Excel Case Study - IF Formulas and what not to do!
ruclips.net/video/nm7co2Y5BPY/видео.html
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...
Great to hear 🙏
Thanks for the info
My pleasure!
I approve this message 👍
💯
😁🙏
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.
Thanks so much! All the best with your channel.
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.
Thanks so much, Roy! Great to hear you're making use of Power Query.
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.
Thanks for sharing and your kind words 🙏😊
Thanks!!!
My pleasure, Chris!
Very good❤
Thank you!
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 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
Thank you Mynda.
Another important alternative way to reduce file size is to use VBA.
Thanks! Can you elaborate on how you'd use VBA?
@@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.
Very Interesting video thank you ! just one think the SUM.IF function is more faster if we select column instead range
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.
Thanks! Just loaded 10M rows!
Awesome!
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. 😞
Great idea!
Conditional formatting probably deserve a mention. They are considered to be volatile and as such can contribute significantly to a spreadsheet's poor performance.
…especially if you have ‘more than a few’ rules in there.
And those rules tend to multiply if you work on the file afterwards!
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.
@@MyOnlineTrainingHub 👍
@@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.
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
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.
Awesome to hear!
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.
Ah, yes good point. User Defined Functions are typically way less efficient than the build in functions.
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?
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
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.
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
Great video. Is-it better to merge two files with Power Query or with Power Pivot?
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.
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.
Great tip! Thanks for sharing.
👍👍👍👍
🙏😊
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 😔
Yes, they have turned it off while they deal with some issues it was causing 😢
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
@@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?
@@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.
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.
Thanks for sharing! I've had shapes propagate in a file like Gremlins before too.
❤
🙏
I find formulae don’t change things much but conditional formatting creates much bigger file sizes.
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.
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!
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.
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.
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!
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?
Possibly. Certainly worth a try.
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?
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
Sharepoint slows all files. It is so bad to use with shared files.
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.
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!
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
I agree, the background music is distracting and unnecessary. I do hope it doesn't become the norm.
@@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.
Don't using vlookups...deprecated them for years ! Index/Match behaves well in large datasets !
Sorry but I do not understand. Do You prefer functions Index/Match in large datasets over relationships?
@@zdzislawkes to be clear : "Over vlookups"
Lots of INDEX & MATCH formulas can still result in slow workbooks if you have enough of them. If so, use Power Query.
@@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.
Another reason is excessive use of objects such as shapes.
Yes, good point. Thanks for sharing.
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.
you just need the latest ryzen 9 processor and RTX 4090 graphics cards to load these excel files..
🤣
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).
😆 nice story!
I have a reset.all.last.cells button for dealing with other's worksheets with no end in sight 👍😎✊
Great idea 👍