Cleaning up Data in Microsoft Excel

Sigh.

Wait, edit that.

Siiiiiiiiiiiiiiiiiiiiigh!

So you’re running a moderately successful business selling medical equipment, and these are good times. You’ve built up a great team of bright, enthusiastic people who genuinely love their work, are good at what they do, and are responding well to your leadership. Sales are on the up, and the money is pouring in from all sides. There’s just one teensy-weensy problem – these guys are horrible at Microsoft Excel.

And not just at the complicated stuff – you don’t expect them to run macros or build complicated pivot tables – but surely they can manage basic data entry?!

Here’s a very basic table that these guys are expected to build every week, except for the last column, which your secretary updates for you based on their performance.

So the agent’s name goes in first, sales managed in the previous month next, average selling price in the third column, total revenue in the fourth. Once this is done, the secretary, Chris, fills in the final column based on your recommendations, and you receive this report on the first Monday of every month. Simplicity itself, right?

Except that it ends up looking like this:

Roget’s thesaurus has a word for that kind of feeling. It goes something like this: Arrrrrghhhh!

Why can’t names be capitalized? Why can’t per unit prices and revenue be in dollars? Why can’t performances have the words (again!) capitalized?

Well, it’s an irritant, I’ll admit. But it’s nothing a little bit of Microsoft Excel can’t cure, let me assure you. Let’s get cracking, shall we?

Choose a new cell, and for the first colum, let’s use a formula called “Proper”. Proper does what proper says – it writes names properly.

Enter the same formula for all the cells in the first colum (and all the cells in the last column, while you’re at it), and things are looking much better already.

Now, as far as the dollar conundrum goes, you could change the formatting using this little feature on the ‘Home’ tab:

But did you know about the ‘Dollar’ formula itself?

The zero in the argument specifies that there should be zero decimal places – but you can change that as per your convenience, of course.

There – aren’t things looking much better already? And if you like a little bit of fancy-pants formatting, while try good ole conditional formatting on the last column! Conditional formatting is a button on the home tab in the Styles section, by the way – and if you haven’t tried it already, you really should give it a whirl.

Some borders, a little color, and voila!

Your spanking new table is ready to go!

The one on the left was what Chris sent you, and the one on the right is what you ended up with:

Now all you need to do is mail this blog post over to Chris, and you can actually look forward to the first Monday of next month – can’t you?