Podcast: Play in new window | Download
Welcome back to the podcast!
This week’s edition…
“Excel hates me! When 2 + 2 isn’t always 4″
You know, one of the most frustrating (and often the most fascinating) part of using Microsoft Excel has to be just how helpful it tries to be.
For instance: I type the number 2 in a cell and again in the cell right under it. Go just below that and click the wonderful “add ’em up” sign (if you’re not sure what I am talking about, look on the toolbar for the button that looks like a Greek letter Σ) and presto—I get 4! Great! This is exactly how it should work, right?
Well, I decided to copy a table of information from the Internet the other day and tried to add up the numbers that I pasted, and Excel could not have been more wrong.
I was really curious as to why this might be happening. I didn’t feel like I could trust my good buddy Mr. Gates and his math processing whiz kid Excel. So I asked myself, “Self, are you crazy enough to act like someone in a horror film who knows the monster is going to get you if you go down into that dark place alone?” And just like in the movies, of course, my brain went, “But…it will be different this time. I’ve got your back.” So down the hole I went, searching for that ever-elusive spark of sanity.
First stop: little green symbols… Ooooo! Are they alien lights? Are they indicators of friends waiting for me to find them? Nope. Turns out, it was one of the newer features added a few years back to Excel that I tend to ignore. You know, like that crazy maintenance light on the dashboard of your car that you keep hoping goes away.
So what do they call these friendly helpers? They call them smart tags, as in, “Good day, ol’ chap! Tag, you’re it!” These little indicators can give you a lot of information on things that might be wrong with your data. Like, for instance, formulas that aren’t consistent. Or, in my case, numbers being stored as text.
Numbers being stored as text? Aren’t numbers just numbers? I mean, when I see a 2, it looks like a 2, smells like a 2…so why can’t Excel accept that it is a 2? As it turns out, when you add data to your spreadsheet from other sources like external databases, imported text files, or pasted data from a Web page, sometimes there are hidden little ghoulies in the system that you can’t see.
Here are a couple of common creatures that you sometimes conjure up when you’re trying get your information based on mojo created in other places.
- Spaces. Yes, spaces. Those little blank things between words, or my ears—you know, the empty kind. Just because you can’t see them doesn’t mean they don’t exist.
- Non-printing characters. Often information coming from different database systems can carry things like line breaks or carriage returns, useful for text-based applications, but useless for most people when they need to work in Excel.
So, what do you do? I say treat it like liposuction and just TRIM out the fat. Well, maybe not the same. TRIM is actually a formula function you can use to remove unwanted spaces in the content of another cell.
Or try using Find and Replacê, by first copying one of these “spaces” or even non-printing characters (you may not be able to see them, but you can touch them if you’re real careful), then run the Find and Replacê. When deciding what to use to replacê them with, use nothing. Just remember: you want to get rid of it and substitute in its place what should be there—nothing.
There are lots of methods and little quirks when it comes to getting rid of these little pests, so this week we have included some helpful tips on how to use many different techniques to exorcise that “evil monkey” in your closet. So why not stop on by and check them out at: www.productiveUpodcast.com.
This week’s tips:
- Mass Conversion – Using SmartTags
- You Get the General Idea – Using Text to Columns
- Just a Trim – Using the TRIM Function
- Clean it Up – Matching up values
- Trading Spaces – dealing with non-breaking spaces
- True Value – Fixing problem numbers using the VALUE function
Well, that’s it for this week. If you found these tips useful or have some questions you would like to see covered in a future episode, you can leave us a quick note on Twitter by following @productiveUcast. Or, if you just can’t get enough of us, let everyone know by writing a review of the podcast on iTunes.
See you next week!
Music Provided by Clayton & Fulcrum
