I tried Flash Fill instead of the Proper function. It didn’t work 100%. Is this just an example where Flash Fill won’t do the job?
Hey John,
For the cells that Flash Fill does not bring out the data they way you want, just manually enter the data again the correct way and press Flash Fill again.
This will tell Flash Fill that similar data needs to be updated the correct way.
I hope this helps.
Thanks,
John
Muito obrigado por compartilhar essas dicas!
You are welcome Guilherme 🙂
Cheers,
John
Amazing knowledge, i do love flash fill feature, save a lot of time.
Thanks for sharing this tip
Hey Lusman,
Yes FLASH FILL is a great feature in Excel 2013 & 2016.
This can save hours of manual work and there are still lots of people who do not know about this powerful feature.
Cheers,
John
Hi Joann, thanks for teaching this miracle formulas, had great learning.
You are welcome Nitil, glad that I could help & make you better at Excel!
Watch out for video #3, it is going to be awesome!
Cheers,
John
I often use the TEXT function combined with a concatenation to create dynamic chart headers. Dates and percentages need a little formatting help. I’m going to print the TEXT sample file because I always end up Googling how to format the text the way I want it. (I need to add the code to convert a number to a 2 place decimal.)
Thank you for the handy reference.
JoAnn
JoAnn, you are so welcome! And thanks for joining the class.
One more excellent video
Thanks for sharing and spreading the knowledge
You’re so very welcome
Excellant vedio. I have learnt one may issue I was having problem with. Text to number. This will be very helpful in my job
Thank
Hey David,
Text to Numbers is one of the biggest problems when we receive data from an external data source!
Now you know how to combat this 🙂
Cheers,
John
You’re so very welcome.
Excellent as usually, however I am using 2010 and does not have flash fill can it be downloaded.
Thanks,
No. Flash Fill was released with Excel 2013 and can’t be downloaded.
Great videos.
Thank you, Marty.
Hello John, this is awesome. You helped me to learn some new tricks to efficiently deliver on my job. Thank you for putting up this lesson.
Hello Abiodun,
You are welcome and it is my pleasure to teach you my Excel tricks so you can become a better Excel user!
Thanks,
John
Thanks John , really good lesson , it was all new to me, so I hope to apply this tips
Hey Colm,
Its great to hear that you have learned some new features as I am sure that these will help you become more efficient.
Reach out to me if you have any more questions as I’m here to help 🙂
Thanks,
John
John
Great stuff. You have a great way and present the information so it is easy to understand.
Thanks for taking the time to do these lessons.
David
Hey David,
You are welcome buddy and it is my pleasure to teach you Excel in the most comfortable way 🙂
Cheers,
John
Great video, picked up a few new tricks that I did not know before. Thanks!
Hey Carine,
That is just awesome and your Excel skills just incremented by a few percentage points 🙂
Cheers,
John
Useful much-needed.Thx
Fantastic!
My learning Curve Highlights:
John:
• Clean Function – new Excel skill;
• Trim Function – new Excel skill;
• Mid Function – new Excel skill;
• Convert Text to Number – very useful Tips!!!
• Find Blank Cells with Color – very useful!
• Text Function – Refreshed formula to my excel skills (haven’t used it for a long time) very useful.
Just want to share to you John that after I watched the video #2 and Learn about the Clean Function and Trim Function I apply the functions right away!, exactly what I need it!, you help me save the day at the time to import some data to the ERP system, it won’t import the excel file, only after applying the Trim Function, the data on the spreadsheet has some spaces at the end of the numbers and the import app didn’t recognized the data as numbers.
Many thanks!
John – I liked the tip with the double minus sign to convert text to number. I have also used @value() to do the same thing. Ron
That double-minus took me a while to understand what it does and when to use it. It’s very handy when it can be comfortably implemented.
thank you for the data cleansing part 1 i have gone through. its really nice.
i needed to know if data cleansing can be applied to data arranged in 3 lines per row heads. can it be sorted into distinct columns?
the paste below did not do it right but you can quess what i mean. thank you.
GL:6001/Ent./Curr.Proj# Short Desc Debit Credit Running Balance
Sr Reference Date Jrnl Group Prd
Explanation Region
6001-5DA1127-USD- POSTAL ORDER C 0
AR 136994 08/01/2016 2 37945 1 14
136994/PROPERTY SERVICE Southern
AR C136994 31/01/2016 2 38849 1 14
136994/PROPERTY SERVICE Nothend
Period 1 Totals for GL: 6001 POSTAL ORDER C Division 5DA1127 14 14 0
Account Totals for GL: 6001 14 14 0
GL:6002/Ent./Curr.Proj# Short Desc Debit Credit Running Balance
Sr Reference Date Jrnl Group Prd
Explanation Region
6002-5EB2101-USD- MONEY ORDER CO 0
AR 31/01/2016 2 38401 1 179.57
10131016/MONEY ORDER COMM Eastern
Period 1 Totals for GL: 6002 MONEY ORDER CO Division 5EB2101 179.57 179.57CR
Wilson, it seems like you might be trying to unstack a column of data. Here is a solution in a video I did a while ago. Inside the course I show various methods for unstacking data–including a really sweet method in Power Query.
Very, very, very nice guys!! Thanks a lot for your invaluable help and keep up with this excellent job you are doing.
Cheers!!
Juan, it’s great to be of service to you.
Keep your data clean!
Suggestion regarding ‘Remove Duplicates’, I work with invoices, so I rarely want to delete duplicates based on a single column (not even invoice number as an invoice may have more than one line). So my ‘golden rule’ is to look for at least two columns to analyze for duplicates.
In your example, if you delete duplicates on both Customer and the Order Date (both checked at the same time), then if you had an entry on a different date for Fake Brothers, you would be left with two lines. (I realize that your data did not have two different dates for that company, but just giving an example.)
Looking forward to video 3, I”m learning more about Power Query slowly 🙂
thank you both for taking the time to do this!
Heather, in the course content, I show how to flag for possible duplicates and use conditional formatting. Excel’s duplicate remover scares the hell out of me because my situations have been more like yours: I need to SEE possible duplicates and make decisions. An example is 3 records for the same person and one of the records is the only one with an email address.
Another example is common names like “Jim Smith.” With 30 of those in a dataset, they could be 30 different people.
The duplicate remover is worth knowing about. It’s handy when you know that you’ve got true true true duplicates and Excel just needs to clear stuff out for you.
Hi
I’m australian too, live in Madrid, sometimes have to travel to Vitoria, I work at the Mercedes-Benz offices in Madrid, and ocasionally have to go to the factory.
Anyway, how do I get rid of indents in cells, are they the same as spaces, so I can use trim?
Goodonya
Sadly, I’m not Australian and never been there. My Navy ship was supposed to have gone to Australia but we only got as far as Singapore. Any way …
If you’re dealing with indents, they can be decreased and increased on the Home tab in the Alignment section.
Hey Ricardo,
Great to see another Aussie near by 🙂
Let me know when you are in Vitoria next and we should definitely catch up for a beer or two….
Until then,
John
A really useful and well-presented summary of useful functions – some vaguely familiar, but rarely used, and some completely new. Many thanks
Hey thanks Norman for your comment and it is our pleasure to bring you new ways of learning Excel and its powerful (but sometimes hidden) features!
Cheers,
John
Fantastic – clearly explained the functions and provided a handy reference guide.
John
We are glad you found this video useful for you John!
Watch out for video #3 tomorrow!
Cheers,
John
As always, all techniques work in a variety of different situations that help expedite processes that would take forever to accomplish so that you can move to the important work of data analysis. These are the building blocks that provide the stable foundation for the important work at hand. Bravo again. You make these solutions a normal part of the day and the explanations make sense…….which makes the learning not only interesting but memorable.
“Memorable.” I’ll take that. 😀
You’re talking about the central theme of all of this: expediting the pre-work and cleansing process so that you can get on with the analysis and do what you gotta do. Amen.
You guys are doing a great job. I really like how you handle other people’s suggestions with grace.
I have a challenge where I need to extract information from audit notes. I get about 1000 lines of information that have notes without form or consistency and I need to extract question numbers and rule numbers from the notes and have them populate in separate cells. There could be multiple question numbers and rule numbers in each note depending on the audit failures that occurred. Below are a couple of cells of different notes. Questions are referenced with a Q before the letter most of the time. Let me know what you think please.
Example 1:
Audited 9/9/2015 failed due to sections 3. Staffing Impacts and 4. Ownership and License were removed from template WorkOrder1v6.31(NorthAmerica)(US)(English)(Feb2011) from the Contract Site version. As the original contract was not accessible to the auditor the Exhibit A could not be validated. Q2.2 failed due to Contract Ready for Signature date in Retention site is 7/7/2015 while the signatures from the customer is on 7/1/2015 and Company’s on 7/6/2015. Re-audited 9/14/2015 auditor could not locate approval msg within the Retention site. Since the Original Contract was dated back in 2013 the approval should be attached as a KP in Retention2 site; but there are only two entries: One for the Retention link and the other the fully executed work order. Since no additional information was found the failure will remain. Re-audited 9/18/2015 the failure will remain unless MGMT lead directs the audit team to reverse as the approval was not located under the KP’s in Retention2 for the engagement nor the account level.
Example 2:
Audited 8/6/2015 Failed Q 2.2 & 2.5 Processing sequence not followed Q 8.2 BIF not attached in Retention2.
Re-audited 8/17/2015 Failure Remains.
Thanks for your work,
Bob D.
Bob, here’s an initial thought. There may be details in your real world that make this solution less than perfect. Also, I only addressed the questions and not the rules. Still, maybe this will give you some ideas.
In this image, helper columns E:J are used to discover if a question is found. Column C uses the new TEXTJOIN function to bring all of the question numbers together in one cell. This way, E:J could be hidden or put on another worksheet, reducing clutter where you need your results.
Is this helpful? (click to see the image larger)
Thanks Oz–this will be very helpful as I build this out next month. I can’t tell you how tired I am of doing this manually every month and these data cleansing classes made me realize that I should ask you guys about my issue. This definitely gives me a kick start on how to proceed less manually. 🙂
Thanks for your help,
Bob D.
Bob, I’m glad that gives you a more efficient path.
Also, you can download the workbook here.
YOU ARE FREAKING AWESOME 🙂
Thanks Justine but I’m just doing my job which I love 🙂
Keep on Excelling!
Cheers,
John
Good morning!
I think using:
conditional formatting–> Rules–>Format Only Cells that contain–>Blanks
is a better option Ctrl+G–>Special–>Blanks, then filling the cell, since this allows for dynamic highlighting of the cells. If you enter a value in a cell and the cell isn’t blank anymore, you don’t want to have to then go back and select “No Fill”.
Hey Alissa,
That is a super awesome tip and one that is very useful as your data expands, especially if your data is in an Excel Table.
Share with the rest of us some more of your hidden gems!
Thanks,
John
