Clean Your Excel Data
September 13, 2016
How to clean Excel data, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there..
I spent many years helping my clients create Excel reports, and the worst part was always cleaning up that data before we could use it. Some clients had to pull data from the company database, using Cognos, or other tools. They had little or no control over how those reports were set up, and things often changed without warning. Other clients had an on-site database, but data entry errors caused problems there too.
If you're in the same situation, trying to create reports from messy data, you can learn useful new tricks in a FREE 3-part Data Cleansing video series from John and Oz at MyExcelOnline.
The first video will show you how to format data and automate reports using Power Query. If you haven't used Power Query before, it's a good opportunity to see what it can do. It's the best part of the Power BI toolkit! You'll be able to download Excel workbooks, to follow along with the video. While watching the video, you can ask questions, and John or Oz will help you out!
If you record a macro in a workbook, then try to save it, Excel might show you a message that warns, "The following featues cannot be saved in macro-free workbooks: VB Project". That slows you down -- you have to click No, then choose one of the file formats that supports macros
If you run into that problem frequently, you can change the default file format that Excel saves in.
And don't worry -- Excel won't show a macro security warning when those xlsm/xlsb files are opened, unless they actually do contain macros.
Here are a couple of Excel articles I read recently, that you might find useful.
Simple Bullet Charts - A bullet chart is like a set of bar or column charts, piled on top of one another. Jon Peltier explains what they are, and a "simplified" way to make them. (Imagine what the complicated version looked like!) If you want a version that's really simple, the Science Goddess shows how to cheat, and just stack two charts instead. (Level - Intermediate)
Bad Power BI Experience - Ken Black was excited to attend a Power BI course (Dashboard in a Day), but things didn't go well. You can read about his experience, and maybe you've been in classes like that too. I hope he learned about Power Query on the second day -- that's a useful tool! (Level - Intermediate/Advanced)
Also, see all my Excel products on my Contextures website.
First, thanks for your help with last week's photo of a strange insect in my garden. You were right, it's a dragonfly, but a kind that I'd never seen before. You can see details on which type it is, on my Gardening page.
This week's photo is a little more appetizing. I put all the dry ingredients for bean soup into the slow cooker, and before I added the stock, it looked like a pie chart! It was 50% meat and 100% delicious.
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:51 AM