How to Clean Data in Excel in 10 Minutes (Free File)

[@MyOnlineTrainingHub]

❓What's your most memorable "oops" moment with data? Master Excel formulas with real-world examples: bit.ly/course240305

[@whazee]

I think my most memorable (and something I made sure to never do again) was leaving a blank column in a large dataset, applying a filter to about half of it, then proceeding to re-organise (sort) those columns without realising that the rest of the sheet wasn't being sorted with it. Did I have to start from scratch again? Yes... yes I did. 😣

[@GregoryGlynn]

I've been using Excel since Release 1.0 on Windows 3 and Mynda still shows me things I haven't considered. I love watching her well-constructed and informative videos.

[@MyOnlineTrainingHub]

Thanks for your kind words, Gregory! I'm so glad it was worth your time to watch 🙏🥰

[@Subhajit_Goraii]

As a newbie in excel in Excel, I'm very grateful to find your channel. Thanks a ton Mynda.

[@MyOnlineTrainingHub]

You're very welcome!

[@chrism9037]

Thanks Mynda, great video for those building their early Excel skills!

[@MyOnlineTrainingHub]

Thanks, Chris. Appreciate you taking the time to watch.

[@ignatiusmba-bo5rq]

Thank you so much for this video. Its Slow, steady and with a soothing way of talking that makes it easy to listen.

[@MyOnlineTrainingHub]

That's wonderful to hear. Thank you so much! 😊

[@agborfionarn7837]

This is super insightful. Especially for Excel newbies like us. Thanks a lot Mynda❤

[@MyOnlineTrainingHub]

Glad you liked it! 😉

[@vijayarjunwadkar]

Thank you Mynda, for this very useful video! While I knew some techniques, I learnt new ones as well and even with known ones, there was something extra I could pick up. Keep them coming! 🙂

[@MyOnlineTrainingHub]

Wonderful to hear, Vijay!

[@SothearithKONGMrMuyKhmer]

That’s really helpful, as always! Thank you! ❤

[@MyOnlineTrainingHub]

So glad to hear!

[@MikandaMaboto]

Thank you for the video, it is easy to follow along. And I must say, you have a very impressive list of clients.

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏

[@tristangeoffroy9286]

Thank you so much for the short cut: control shift V !!!

[@MyOnlineTrainingHub]

Glad you can make use of it 😊

[@drendelous]

i really like these short to the point guides

[@MyOnlineTrainingHub]

Awesome to hear! 🙏

[@randovids7147]

You should trim before checking for duplicates !

[@MyOnlineTrainingHub]

Yes! Good tip 👍

[@GeertDelmulle]

And also use CLEAN() for that matter. ;-)

[@randovids7147]

@@GeertDelmulle that’s a good one.

[@Locomaid]

Excellent! Was my first thought, also

[@warrennalty6599]

I had trouble using Clean function because what looked like a space was a non breaking space that has a different ASCII. I had to use Substitute() with Char() in conjunction with Clean().

[@peevee2552]

Thank you, perfect for data processing starting positions :)

[@MyOnlineTrainingHub]

Glad you found it useful 🙏

[@kebincui]

Brilliant as always 👍, thanks Mynda

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@intotheforestbiking6300]

Love your videos! Would you make a tutorial on how to track net worth with live stock investment updates? I see a lot of interactive stock dashboards, but would also like to include my cash savings and other investment forms as well. Thanks!

[@MyOnlineTrainingHub]

Great suggestion! I’ll keep it in mind 👍

[@williamthatsmyname]

Awesome . A quick an easy alternative to power query.

[@MyOnlineTrainingHub]

Yep, perfect for one off cleaning tasks 👍

[@vickymade8381]

I love thi video... You are now in my list of fave.. you are topping the list😅❤❤❤❤

[@MyOnlineTrainingHub]

Thanks so much! 🥰

[@williamzhangzhijun]

Hello. As always, thanks for the great video. I want to ask, how to use multiple value filters on pivot tables. Others showed filtering on label + value. What i want is filters on multiple values. E.g. filter the 'Sum of A' greater than X number, AND display top 10 of those based on 'Sum of B'. Could you help? Thanks.

[@MyOnlineTrainingHub]

You'd have to use Power Pivot and write a DAX measure for that. i.e. it can't be done with a regular PivotTable.

[@williamzhangzhijun]

@@MyOnlineTrainingHub i see. I'll try that. Thank you for the reply. 👍❤️

[@reginaoon8239]

Really helpful video! Avoiding having text (e.g. missing) in a column with numbers is new to me. Anyone knows the reason why we shouldn't do so?

[@MyOnlineTrainingHub]

Because mixing numbers and text creates an ambiguous data type for the column preventing Excel PivotTables from knowing whether it should sum the data or count the data by default. Also, text in a numeric column will return errors in formulas that are expecting numbers, as you saw in this video where I had to use IFERROR to handle the errors returned by text.

[@RJCapeTown]

Wonderful summary thx!

[@MyOnlineTrainingHub]

Glad you liked it '🙏

[@ajdesigns1660]

Thanks for the information and the data of famous personas was cool 😂

[@MyOnlineTrainingHub]

Glad you liked it!

[@MohammadMonirHossain-uo2mm]

Thank you very much for your video.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@silversolver7809]

Very good, thanks :)

[@MyOnlineTrainingHub]

Glad you liked it!

[@Sweetandhandmadebyz]

Thank you so much.

[@MyOnlineTrainingHub]

My pleasure!

[@nasflix_2.0]

On Microsoft word a function called sentence case, it helps to the data typing, but not default in Excel. So each time Capslock on and off after and before, hope you can fix my problem.

[@MyOnlineTrainingHub]

Ah, you can use the text functions PROPER, UPPER and LOWER to switch the case.

[@Vlog88999]

Thank you very much 🎉🎉

[@MyOnlineTrainingHub]

You're welcome 😊

[@ThePianoEvangelist-kg2qw]

Can you help us with how best we can show the work on portfolio for prospective clients..

[@MyOnlineTrainingHub]

I think it would be cool if you created an Excel file containing your portfolio. I don't have any examples though, sorry.

[@Omar9557-oc6zf]

Can you provide some guidance on how to complete rearrange a date format in excel. For example, i have 203 cells with mixed date of birth formats. I.e.: (1)01/02/1995 (2)02/01/1995 (3) 1/2/1995 (4)1/02/1995 and so on. how do i go about correcting this?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@tridibbiswas3361]

Thank you for the video. Can we have the file for follow through

[@MyOnlineTrainingHub]

Sure, you can download it here: www.myonlinetraininghub.com/how-to-clean-data-in-excel

[@tridibbiswas3361]

@@MyOnlineTrainingHub Thank you

[@cant-blame-people]

as a beginner is it okay to use this or i must learn on how to use formula first??? thnxxx guys

[@MyOnlineTrainingHub]

You can definitely use this, but you should also learn to use formulas.

[@asadkakuji248]

why is accounting format not good for analysis?

[@MyOnlineTrainingHub]

I personally don't like the dollar signs it puts on every row. It's unnecessary clutter. Better to put the currency sign in the header if you need to specify it.

[@md.rehanshaikh1162]

@@MyOnlineTrainingHub that is 1 excellent tip, Alex Freberg said similar thing in one of his tutorial

[@markpodesta4605]

Thank you Mynda.

[@MyOnlineTrainingHub]

My pleasure, Mark!

[@mitoali5151]

Is there a certificate after finishing your course?

[@MyOnlineTrainingHub]

Yes, all our paid courses come with certificates.

[@arjaymoldez2736]

nice, thanks

[@MyOnlineTrainingHub]

Welcome 😊

[@hotcarlsf]

Nice video. I physically cringed though when you did the formula in the random blank cells to reference the value above them. Someone did something like that in a spreadsheet they provided me before. It screwed me over. Using F5 on the keyboard is a another quick way to get to go to special

[@MyOnlineTrainingHub]

Yeah, probably a good idea to paste as values if you plan on sorting the data. Thanks for the F5 tip 👍

[@abbottkatz8830]

You can also populate the Region dropdown list by selecting the entire Region column in the data validation dialog box instead of typing the regions. The resulting dropdown list will return a unique instance of each region.

[@MyOnlineTrainingHub]

But then it would allow you to type anything in the cell since the list is referencing itself.

[@abbottkatz8830]

@@MyOnlineTrainingHubThanks. I may be misunderstanding your point, but my approach will obstruct non-list entries. I meant that you can cite the Region range as the Source for Validation criteria.

[@MyOnlineTrainingHub]

When I test that I can enter anything in the Region range without the data validation flagging it.

[@drendelous]

5:09 i have to use ; instead of ,

[@GeertDelmulle]

That is because you use "," as a decimal 'point' and "." as a thousand separator.

[@progbadger7042]

Proszę w o więcej takich filmów:)

[@MyOnlineTrainingHub]

Thanks for watching. I will try!

[@Shuvoorahman]

Ma'am, it would be better if you give some examples in a google sheet, then we can practice.

[@MyOnlineTrainingHub]

I don't use Sheets, sorry.

[@IamTheReaper911]

Oh, I'm sure I'll still be embarrassed 😂

[@MyOnlineTrainingHub]

😆

[@renilroy5718]

🆒

[@MyOnlineTrainingHub]

🙏

[@steven.h0629]

👍😎✊

[@MyOnlineTrainingHub]

Thanks for watching, Steven!