Errors, Errors, Errors!

Okay, so you are humming along creating yet another grand Excel masterpiece when Bamm! You are alerted that there is an Error! But what does it mean? What does this cryptic warning by the Microsoft gods represent? The following is a concise list of Error Messages and what they mean:

 • #N/A A return value of the function is not available. (You messed up on the formula.)

• #NAME? Okay, you probably just misspelled a name in the function (It happens…).

• #NULL! You specified an intersection of two areas that do not intersect (Whoops).

• #DIV/0! Well, you divided by 0 or by an empty cell (You know better than that…).

• #VALUE! The wrong type of argument or operand is used in the formula (Right!).

• #REF! An incorrect range is mentioned in a function (A cell reference is not valid).

• #NUM! There is a problem with a number in a formula or function (Is it really a number?).

When a human works with Excel, errors are inevitable. After all, To Error is Human…

Using Validation to Create a DropDown Box

As an instructor of Excel classes that are as large as 250 professionals, it is common for me to field repetitive inquiries on certain Excel functions. One that I see a lot is “Is there an easy way to make a dropdown box in my Excel report?”

This is a good topic to revisit, as using a DropDown Box in an Excel report can add interactivity, efficiency, and a professional style to your worksheet.

The easiest and most effective way to do this is by using Validation:

1. Make a Source list that you want displayed in your DropDown box.

2. Select the cell in which you want the dropdown, (such as the green-shaded cell in the graphic).

3. Choose Validation / Allow List and then select the range from your Source list.

Presto! Instant DropDown Box!

By combining a dropdown and elements such as a VLookup function, you can create a powerful and interesting report in Excel. Give it a try, it’s Easy!

Excel 2010 Beta is Here!

Well, I have taken the plunge and downloaded the beta version of MS Office 2010! I have been exploring Excel 2010 this week and I have found numerous new or refined features that are commendable.

Among the many cool new features, I have found the “Sparklines” (funny name, but very useful) and the new Screenshot tool to be the most interesting and beneficial.

Sparklines are essentially mini-graphs that you can automatically insert in cells within your table, giving context to the numbers. Highly effective in giving at-a-glance information!

 The new Screenshot tool allows you to intuitively paste a picture of any open program or clipping from an open program. Very easy to use and all included within the application (no need to use a specialized program to accomplish this any longer). Manipulations of graphics have also been greatly enhanced (this can particularly useful in PowerPoint, but is also nice in Excel).

Go to Microsoft Office Beta Download to snag a copy. A word of caution, however: Be sure to backup all of your data and your current version, in case you wish to revert to your tried-and-true Office roots.

Happy Excelling!

Currency Rates in Excel

So, you are getting ready for that trip to Europe, (or wherever in the world you are going), and you would like to download the most up-to-date currency exchange rates directly into the Excel workbook you are using for your trip planning. Piece of Cake!

Here is what you do in Excel 2003:
1) Select cell A1
2) Go to Data / Import External Data / Import Data and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

Bamm! In a few seconds, you will have the exchange rates for Argentine Pesos to Venezuelan Bolivars!

If you are using Excel 2007:

1) Select cell A1
2) Go to Data / Get External Data / Existing Connections and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

Does that Rock or What? I see that today you can get 9,360 Indonesian Rupiahs for one US Dollar (sounds like a bargain…)!