Cleaning Up Your Data

If you ever have had problems downloading useable data into Excel from another software source, you may have wondered how to clean it up. The Good News is that there are several ways to do this! Here are a few of my favorite solutions:

First off, we have the CLEAN function. This removes the nonprinting (but often, irritating) characters, as well as other garbage characters that may have tagged along in your imported data.

Another useful function is TRIM, which eliminates any Unwanted Spaces. This is very helpful, since extra spaces are frequently imported from some sources.

Then we have one of my very favorites. The REPLACE function is quite versatile and a good tool to have at your disposal. Let’s say that you have imported data which has 5 leading unwanted characters (such as “0.00 ”). Please note that the characters may be numbers, letters, punctuation, or even spaces. If, for example, your data starts in cell A1, you could put the following formula in B1 and drag it down for the remainder of the range:

=REPLACE(A1,1,5,"")

Presto! No more unwanted leading characters. So, there you have it! Three easy-to-use methods of Cleaning Up Your Data. It is always better when things are clean!

Happy Fourth of July, All!

Using the CONVERT Function

The CONVERT function is one of those obscure little tools in Excel that can come in very handy.

For example, let’s say you want to convert the number of Feet in a measurement to the corresponding number of Meters. CONVERT to the rescue! In fact, CONVERT can translate a Wide Variety of measurements that include time, distance, temperature, weight, and much more.

The Syntax of the CONVERT function is as follows:

CONVERT(Number, From Unit, To Unit) where:

1. Number is the Amount in From Units to convert
2. From Unit are the units Being Converted
3. To Unit are the Units in the Result

The table below shows the results of some of the more Commonly Used Conversions:

For a Full List of all the measures that can be converted, please see the CONVERT function Help topic.

The CONVERT function. It is always there when you need it. Check it out when you have a moment!

Paste Special is Special!

Paste Special is one of those Excel tools that once you discover what you can do with it, you wonder how you ever lived without it. To find it, simply copy something on your worksheet and Right-Click. Paste Special will be near the top of the dropdown list.

Here are the Top 5 Ways I use Paste Special on a regular basis:

1) Paste Values: Simply copy the results of formulas, and Paste the Values in a new range of cells
2) Multiply or Divide: You can copy a value and use Paste Special to multiply or divide an entire existing range of numbers
3) Paste Validation: Easily copy a cell that has Validation applied, and paste that validation only in a new cell or range
4) Transpose: You can easily transpose a column into a row, or a row into a column
5) Paste a Link: Simply copy a cell in another worksheet, and using the Paste Link button on Paste Special, you can paste a dynamic link to a new location.

Paste Special is truly one of the indispensible tools in Excel. If you haven’t been using it, give it a try today. You will wonder how you ever lived without it!

Trendline Before and After

Charts are, of course, a wonderful way to communicate information in your Excel Worksheet. A well-designed chart can immediately translate your data in a compelling visual way. Sometimes, however, your chart may need a little help.

The overall trend of your data is valuable information that may not be readily discernable. As in the case of the illustration above, is the trend up, down, or flat? It is not easy to tell. This is where a simply applied Trendline can make all the difference in the communicative value of your chart.

To add a Trendline, simple right-click on the Data Series Line on your chart, and choose Add Trendline from the dropdown list. This will immediately insert a Trendline on your chart.

But don’t stop there! Right-click your Trendline and add some formatting magic to make it look professional in every sense. Changing the Color, Weight, and Line Style can make a world of difference.

As in the illustration below, it is now easy to see that there is an overall upward trend in your data. Good News, if you are in Sales!

Trendlines. Easy stuff. Give them a try!

Top Ten Favorite Shortcuts for 2011

Every year or so, I like to access what my Current Favorite Excel Keyboard Shortcuts are. This changes over time as I learn new tricks and incorporate them into my daily work with Excel.

Believe me; if you are not using keyboard shortcuts on a regular basis, you are letting yourself down. They add speed, efficiency, relief from the stress of overusing the mouse, and they are Just Plain Cool!

So, in David Letterman-style, here are my current favorite (and highly recommended) Top Ten Excel Shortcuts in reverse order:

10. Shift + F3 (Pulls up your Insert Function – Handy!)
 9. F5 (Brings up the Go TO dialogue box – Great for navigating to a named range)
 8. Ctrl + F4 (Closes the Current Window – Quick and easy!)
 7. Ctrl + P (Pulls up the Print Dialog Box – Gotta love it)
 6. F12 (Brings up the Save As dialogue – Nice!)
 5. Ctrl + Z (Undo Previous Action – Oldie, but a goodie)
 4. Ctrl + H (Find and Replace – I use it often!)
 3. Alt + = (Brings up the AutoSum – Beats going to the toolbar)
 2. Ctrl + Home (Brings you to the Start of the Worksheet – It is always good to go home)

And My Current Number One Best Shortcut Is (Drum Roll and Cymbals, Please)...

 1. F11 (Creates an Instant Chart – Always a crowd pleaser!)

There you have it! Try a few of them, and see how they can make your Excel life better!