7 Common Excel Mistakes You HAVE to Fix Today!

[@MyOnlineTrainingHub]

❓I'm curious how many of these mistakes you’re guilty of? Master Excel & Power BI with 20% off all my courses until December 5, 2024: bit.ly/excelmistakes24courses

[@Quidisi]

You are so correct, #6 Merged Cells, are EVIL. But #2 (inconsistent formulas in a column) are even MORE EVIL. A few years back, against my better judgment, and to prevent circular references (because I didn't want to add a helper column) I committed sin #2, and then distributed my spreadsheet template to a wide audience. 🤦‍♂Oh boy, did I ever come to regret that stupid decision. Never again!

[@MyOnlineTrainingHub]

live and learn 😅

[@anupk500]

Thanks a lot! You are my favourite Excel teacher. And hey compliments for your appearance and new look!

[@MyOnlineTrainingHub]

Thank you! 😃

[@thegers91]

I will be using the solution for mistake #6 from now on! Merged cells have always been a bug bared of mine! Thanks for sharing 😊

[@MyOnlineTrainingHub]

Great to hear!

[@kassimbabika]

Brilliant. Absolutely brilliant. These are the sorts of tips and tricks that should help me break out of my Excel learning malaise. Thank you!

[@MyOnlineTrainingHub]

So great to hear! 😁

[@mrashid229]

Wow! 7 lucrative lessons in just 11 minutes! simply awesome!!

[@MyOnlineTrainingHub]

Thanks for watching! 💪

[@RichardJones73]

I wish Excel Devs would add center across selection to be able to add it to quick access toolbar

[@MyOnlineTrainingHub]

Me too, Richard!

[@remediationdepartment4332]

thank you for your useful and smart videos, I love your channel

[@MyOnlineTrainingHub]

I really appreciate the support!

[@GeertDelmulle]

Hey Mynda, while grouping in weeks, are you sure that those weeks always start on a Monday? Or do you have to tweak that using the starting date (which I guess is the case)? PS: there is an ISOWEEKNUM function in Excel, so adding that as a calculated column is no big deal.

[@MyOnlineTrainingHub]

Correct, Geert. If you want the weeks to start on a specific date, then you enter it in the starting date field. You can add the week number with ISOWEEKNUM but this adds data to your file, so if it's already large, then I would prefer the PivotTable grouping.

[@IvanCortinas_ES]

Great tutorial Mynda. Thank you for sharing it.

[@MyOnlineTrainingHub]

Thanks for watching, Ivan!

[@karllinguistik7244]

Excelent job. Congratulations! And amazing infography.

[@MyOnlineTrainingHub]

Thank you very much!

[@mrewan6221]

Centre Across used to be the only sensible way to make table headings. (The nonsense way was to type into the centre column and justify Centre). Then Excel brought out Merged Cells, and tried to hide Centre Across. But I found the problems with sorting and merged cells almost immediately, so I have had a short-cut button for Centre Across ever since. [I started in Excel 4, and have seen some truly awful "innovations". But also the brilliant transition from Excel button-click macros to VBA.]

[@MyOnlineTrainingHub]

A macro shortcut for center across selection is a great idea.

[@LChoiceM]

"merged cells are evil". Hilarious. I changed my practice when I first heard you mention this improvement some time ago-but I recall many years of wasted effort trying to resolve this complication, often introduced via automated report generation tools. Thankyou.

[@MyOnlineTrainingHub]

Wonderful to hear you've ditched merged cells and never looked back!

[@Bhavik_Khatri]

For mistake 2, could you please provide the SUM LAMBDA function for use within =SCAN(0, E5:E21, SUM)?

[@MyOnlineTrainingHub]

SUM in the SCAN function requires nothing else. It's an eta lambda that Excel understands and can use as is.

[@Bhavik_Khatri]

@@MyOnlineTrainingHub Using a named range with LAMBDA =LAMBDA(a,b, a + b)

[@mouhammadwasseemshaabani47]

Thanks a lot, great as always, the sad thing about center across selection that it doesn't work in excel online, and even forcing it to work by switching to desktop version and back to online will make a lot of issues and act wired

[@MyOnlineTrainingHub]

Ugh, I didn't know center across selection didn't work in Excel Online. That's super annoying 😤

[@rkalle66]

Btw. one common mistake is using numbers when the data is not a number to calculate with like telephone numbers or account no. and many more. Worst case is when data is mixed text and numbers.

[@MyOnlineTrainingHub]

Yes, this is a real pain.

[@kdunlap3289]

my running totals must subtract any debits as well as summing the credits...

[@tarquinalgernonmoncrief3670]

if your DRs are in Col A and CRs in Col B then you could do: =-SUM($A$2:A2)+SUM($B$2:B2)

[@JoeMeyer8998]

Very helpful 👍

[@MyOnlineTrainingHub]

Great to hear 🙏

[@chrism9037]

Great video Mynda. I was guilty of the running total trap earlier in my career before learning the right way to do it😅

[@MyOnlineTrainingHub]

Yeah, me too, Chris. 😁

[@GeertDelmulle]

Well, technically it is still the fastest way to calculate a running total. This may be relevant for large tables.

[@MyOnlineTrainingHub]

Yes, it is faster, but it'd have to be a BIG table to be noticable.

[@jairoenriquecastilamendoza8966]

Hi, is it possible to use the sequence function in a format table ?

[@brianxyz]

If you mean in an Excel table the answer is no. Tables and array functions don't work together.

[@MyOnlineTrainingHub]

What @brianxyz said. Any formula that spills cannot go in an Excel Table.

[@chahineatallah2636]

Great video, yes merge cells is the worst thing, it should be removed from excel 😀

[@MyOnlineTrainingHub]

Well said 😁

[@lesleyewilliams]

Hi Mynda. The Sequence function looks very useful but when I tried to use it to number rows in a table I kept getting a Spill error. Is there a way around this?

[@MyOnlineTrainingHub]

No. You can't use functions/formulas that spill in Excel Tables.

[@lesleyewilliams]

@@MyOnlineTrainingHub Okay Thanks. I always learn something new when I watch your videos.

[@janwijninckx3598]

Alpha numeric list: you now introduce an error by spanning the range to some arbitrary number of rows. I prefer using the old trick of having a '-- row which indicates the end of the data. Also use format to format the seq# as A01, as that gives the opportunity to use the number without any difficulty. So format custom "A"##

[@MyOnlineTrainingHub]

Great alternate solutions. Thanks for sharing.

[@Jakeu1701]

To auto apply the alpha numbered list, you could make it a table and use concatenate. "A"&sequence(...

[@MyOnlineTrainingHub]

Not sure what you mean by 'make it a table'.

[@Jakeu1701]

@@MyOnlineTrainingHub Ctrl+T

[@Jakeu1701]

@@MyOnlineTrainingHub make the data source a table as you showed in another part of the video.

[@MyOnlineTrainingHub]

You can't use SEQUENCE in a CTRL+T Table.

[@evilangel4136]

2:02 =sum(F4, E5)

[@MyOnlineTrainingHub]

Yep, you can do that too 😁

[@dispirted8]

Does anyone like merged cells? I can imagine if you were creating a simple document that was never going to change but wanted it to look that way, it would make sense? I never use merged cells on principle. Is there a petition to replace it on the ribbon with “Centre across selection”? 😊

[@MyOnlineTrainingHub]

We MVPs have been asking for the merged cells icon to be replaced with center across selection for years, but no joy so far 😤

[@mogarrett3045]

excellent

[@MyOnlineTrainingHub]

Many thanks!

[@traciesmobile683]

Ref running total what if multiple columns adding up to total

[@MyOnlineTrainingHub]

You can add these to the SUM formula.

[@MauroPena-n8e]

SEQ function is only for Office 365. 😢

[@MyOnlineTrainingHub]

And Excel 2021 and 2024. But you're out of luck if you have 2019 earlier.

[@MaryM-r7i]

I'm a long time Excel user (I remember when Excel only had about 16,000 rows!) however I always learn new tricks from you. Grouping data into week/fortnight bands is something I'll be using in my work 🙂

[@MyOnlineTrainingHub]

So great to hear 🙏

[@tanveerabbas3271]

cool❤

[@MyOnlineTrainingHub]

Thanks for watching 😁

[@rafiullah-zz1lf]

I feel like 🎩 as I know all except weekday in pivot. How skilled am I.😂

[@MyOnlineTrainingHub]

Great to hear! 💪

[@reyt7349]

❤

[@MyOnlineTrainingHub]

🙏😁