Contextures News

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..

Clean Your Excel Data

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!

Change the Way Excel Saves Files

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.

  • Click the File tab at the top left of Excel, then click Options
  • Click the Save category, and in the Save Workbooks section, select a macro-friendly file format, such as xlsm or xlsb
  • While you're there, you might want to add a check mark for "Save to Computer by Default", if Excel keeps trying to make you save in the cloud.

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.

change default file format

Excel Articles

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.

What's In the Soup

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.

weekly photo

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 @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




Get weekly Excel tips from Debra






Last updated: March 15, 2017 11:51 AM