Contest, Prizes, Plus AND, OR!

Due to the enthusiasm the contest last November generated, I thought it would be fun to run another one for all of the Excel Enthusiast Subscribers on Kindle. There are currently 302 Subscribers from 5 different countries (Thanks so much!).

First, however, a brief look at the AND and OR function in Excel. As with most Logic Functions, the AND and OR functions return a TRUE or FALSE result.

The OR function is very Inclusive. If Any of the conditions, (see syntaxes below), are True, then the OR function will return a True result.

The AND function, on the other hand is Exclusive. If Any of the conditions are False, then the AND function will return a False result (All of the conditions must be True to return a True result).

OR Function Syntax: OR(Condition1, Condition2, Condition3, etc)
AND Function Syntax: AND(Condition1, Condition2, Condition3, etc)

For example, OR(A1>200,A2>200,A3>200) returns TRUE if Any of the cells referenced are over 200.

In the case of the following, AND(A1>200,A2>200,A3>200) returns TRUE only if All of the cells referenced are over 200.

These functions can be used very effectively when nested with other functions, such as the IF function.

Now to the Contest! Please send me an email with your Favorite or Most Used Function in Excel. Three names of 3 Responders will be randomly chosen and each will be sent a genuine One-Ounce Lakota Copper Bullion Coin. These bullion coins are 99.9% solid copper and are a bit larger than an old-fashioned Silver Dollar (an impressive coin to have in your pocket…).

Here are the Simple Rules:

1. Send an email to excelenthusiast@gmail.com with your favorite Excel function
2. Attach an example if you wish
3. Get your entry in by midnight July 28.

I hope to hear from you soon! Prizes will be sent on July 29, and Results of the Contest / Survey will be posted in early August.

Excel Add-ins

Many Excel users, (including some “Old Hands” at the application), have either overlooked or ignored the many fine tools available as Add-Ins. To find the Add-Ins menu in Excel 2007, (currently the most widely used version of Excel), click on the Microsoft Button, and click the Excel Options button near the bottom of the dropdown.

Here are some of the Great Tools that you will find available:

• Analysis ToolPak
• Conditional Sum Wizard
• Euro Currency Tools
• Lookup Wizard
• Solver Add-in

Of the group, my favorites are the Analysis ToolPak and the Solver Add-in. The Analysis ToolPak contains the following Fabulous (can you tell I’m an old geek?...) tools:

• Correlation
• Covariance
• Exponential Smoothing (which is so cool!)
• Histogram
• Moving Average
• Much more…

The Solver Add-in, on the other hand, can be effectively used to maximize sales/profit plans, strategic planning, optimizing a product mix, and even picking a winning team! Really, Really Cool!

Excel Add-Ins. Look them up and give them a Test Drive. You will be glad you did!

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!