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!
@gospelmoto2833
Жыл бұрын
You're captivating Mynda, you're getting lovelier and lovelier each time I watch your videos. Thanks!
@MyOnlineTrainingHub
Жыл бұрын
🙏
@captainteach007
2 күн бұрын
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
2 күн бұрын
Glad it was helpful! Thank you 😊
@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!
@andrewerlewein7160
Күн бұрын
I have this exact problem, and now I know why and how to mitigate. Thank you.
@MyOnlineTrainingHub
Күн бұрын
So pleased it was helpful!
@claudineyribeiro518
9 ай бұрын
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
9 ай бұрын
Thanks so much! All the best with your channel.
@Fabi_terra
7 ай бұрын
Thanks so much, Mynda! Great tutorial! Have a lovely day! 😊
@MyOnlineTrainingHub
7 ай бұрын
Thank you! You too!
@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 🙏
@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.
@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 🙏😊
@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
@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 🙏
@FIRETIGER49
9 ай бұрын
Excellent video! Comprehensively covered all potential areas. Thanks!
@MyOnlineTrainingHub
9 ай бұрын
Glad you enjoyed it! 🙏
@marjoriefrancis4560
Жыл бұрын
Good morning Mynda, thank you for the updates and have a blessed day 🙏
@MyOnlineTrainingHub
Жыл бұрын
Thanks you, and 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.
@MegaTrickykid
Жыл бұрын
great insights! Thank you very much indeed!
@MyOnlineTrainingHub
Жыл бұрын
My pleasure!
@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! kzitem.info/news/bejne/z6NtyaNokmdzg44
@GSC-zz8zt
2 күн бұрын
@@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
@MikeCummings-ki2nk
8 ай бұрын
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
8 ай бұрын
Awesome to hear!
@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
@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!
@YvesAustin
Жыл бұрын
great video Mynda! Packed with good tips! Thank you.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
@Muuip
Жыл бұрын
Thanks! Just loaded 10M rows!
@MyOnlineTrainingHub
Жыл бұрын
Awesome!
@IamTheReaper911
Жыл бұрын
I approve this message 👍 💯
@MyOnlineTrainingHub
Жыл бұрын
😁🙏
@gnanpra3092
6 ай бұрын
Thank you! really very helpful tips! Surely will help me.
@MyOnlineTrainingHub
6 ай бұрын
Glad to hear that!
@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.
@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.
@luisquismondo
Жыл бұрын
Great content and great presentation. Thanks.
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
@craigwhitthread3050
Жыл бұрын
Very good hints and tips. 🙂
@MyOnlineTrainingHub
Жыл бұрын
Thank you 🙏
@grahamparker7729
Жыл бұрын
Another great video Mynda
@MyOnlineTrainingHub
Жыл бұрын
Thanks, Graham!
@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.
@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.
@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.
@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
@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.
@kleinboertjie
Жыл бұрын
Thanks for the info
@MyOnlineTrainingHub
Жыл бұрын
My pleasure!
@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
2 күн бұрын
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.
@mozgus79ify
2 күн бұрын
My big workbook works OK, but my Power Query loads slowly. Any tips for that? Source files stored on the Sharepoint
@MyOnlineTrainingHub
2 күн бұрын
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
12 сағат бұрын
@@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
@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.
@ExcelWithChris
Жыл бұрын
Thanks!!!
@MyOnlineTrainingHub
Жыл бұрын
My pleasure, Chris!
@kwanhangcheung691
Жыл бұрын
Very good❤
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
@rogeriopenna9014
4 ай бұрын
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
4 ай бұрын
Possibly. Certainly worth a try.
@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.
@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
@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!
@pedrodfg
7 ай бұрын
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.
@YogeshkSharma-i1c
Күн бұрын
🎉
@MyOnlineTrainingHub
Күн бұрын
🙏
@zaighamuddinfarooqui1705
Жыл бұрын
👍👍👍👍
@MyOnlineTrainingHub
Жыл бұрын
🙏😊
@aloobi
Жыл бұрын
Another reason is excessive use of objects such as shapes.
@MyOnlineTrainingHub
Жыл бұрын
Yes, good point. Thanks for sharing.
@pawelsadoch5394
Жыл бұрын
❤
@MyOnlineTrainingHub
Жыл бұрын
🙏
@mrgod5139
Жыл бұрын
you just need the latest ryzen 9 processor and RTX 4090 graphics cards to load these excel files..
@MyOnlineTrainingHub
Жыл бұрын
🤣
@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!
@steven.h0629
Жыл бұрын
I have a reset.all.last.cells button for dealing with other's worksheets with no end in sight 👍😎✊
Пікірлер: 125