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!

COUNTIF and CONCATENATION Revisited

Dear Readers,

It has been a rough week for me. My one true hero since childhood, my Uncle Bob, (after whom I was named), passed away. He was a fine man in so many ways.

I haven’t written much on the COUNTIF function for the last two years, so I thought it was time to revisit this truly powerful weapon all Excel Enthusiasts should have in her or his arsenal. I has a potentially tricky syntax (I have learned this the hard way) that I want to Emphasize in this week’s blog.

COUNTIF is a truly powerful tool that is frequently useful. It can be used by itself, or combined with other functions to make it even more effective. One simple way to maximize the use of this function is to reference a cell location through the use of CONCATENATION (simply an “&” in this case).

For instance, if you wish to count only cells that are greater than the value in a particular cell, (B2 in the example below), you should use Quotation Marks for the Greater Than symbol, and the & preceding the cell reference. Any time you insert criteria (other than a cell reference) in a COUNTIF formula, you must be sure to use Quotation Marks or is simply won’t work.

=COUNTIF(A1:A29, ">"&B2)

How cool is that?! COUNTIF combined with CONCATENATION makes a dynamite combination for creating interactive reports. Give it a try!

Matching Two Databases

Many of us are faced with matching data that is contained in two or more databases. It may be matching customers with product choices, insurance policyholders with claims, and any great number of things. What is often the case is that there will be matches only for some of the data, as is the case in our example today of matching Policyholder Database with a Claims Database.

In our example, we will use the MATCH function, along with an error-handling function, IFERROR.

Here is what you do:

1) In the Claims database, Name your range of address in the database "ClaimsAddresses” (or whatever suits your fancy).
2) Create a "Match" range of addresses on the Policies database
3) Put the following formula in in the first cell of the new "Match" range:
=IFERROR(MATCH($M2, ClaimsAddress, FALSE), "")
4) Fill the formula to the bottom of the Match range with a double-click
5) Sort the Policy database according to the Match range

Voila! The result is a list of policies that have a claim that are indexed to the Claims database.

Try this out! Seriously, this can save you a lot of work when you have multiple databases.  All the best…