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…)!

Copying Formulas

As we probably all know, you can use the fill-handle (small black box that appears when you hover your mouse over the lower right corner of a cell or range) to drag (and therefore, copy) a formula down the rest of the range. Now that is fine when you have just a few cells, but if the range has any significant length, “dragging” can be, well, a drag!

A better solution is to hover your mouse over the handle (you will see it turn into a crosshair) and Double-click it. Bamm! You have copied your formula down the entire range. It doesn’t matter whether the range is 3 rows or 30,000, the results are for all practical purposes, instantaneous!

Now, let’s say we have something just a bit more complex. It is important to be aware of Absolute and Relative (I know, let’s keep our relatives out of this…) references. For instance, if you want to lock into a column, put a $ sign in front of the column (e.g. $B2). If you want to lock into a row, put a $ sign in front of the row (e.g. B$2).

Make a worksheet similar to the illustration below and try constructing the following simple concatenation formula in cell B2:   =$A2&" Sales in "&B$1

Then grab the handle on cell B2 and drag it through cell E5 and watch what happens…

Is that cool or what?!?! Go on, give it a try!

Insert Function Wizard

Regardless of the version of Excel you may be using, the Insert Function Wizard can be a very helpful in performing a bit of magic when you need it. The magic it provides is translating your wishes into dreams come true (well, that is overstating it, but I think you will see my point).
Let’s face it, there are times when you may think that Excel has a Function that you can use in a formula, but you don’t know what it is called or how to use it. You can describe it, but what Excel calls it is simply a mystery. The solution is (a little magic music here…) the Function Wizard!

Just go to the icon that looks like an fx on your toolbar (or the Insert Function under Formulas if you are using Excel 2007), and type a brief description of what you want to do. For instance, let’s say you want to calculate what your loan payments are going to be when you buy the new car you have had your eye on. All you need to do is:

1) Pull up the Insert Function Wizard
2) Type your description in the search textbox (in this case, “loan payment”)
3) Select the function (in this instance it would be PMT)
4) Complete the Function Arguments and click OK

Presto! You have your customized function for your formula! Give it a try; it is easy and can perform a bit of magic when you need it.

Databases are Cool!

When it comes to maintaining the integrity and ease-of-use of reports over a length of time, nothing makes life for the Excel user more trouble-free and uncomplicated than using a well-constructed database and a few simple formulas. Creating a good database can solve a lot of miserable problems in Excel (I am sure most of us have seen how reports can build up to many, many pages, negatively impacting their usefulness).

Although setting up and maintaining a viable database is quite simple, there are a few basic principles by which you should abide. Let’s look at three simple rules:

1) When you first set up a database, be sure to include data in its most granular form. Each column should be characterized by conveniently small and independent parts. For instance, do not create a single column (or “Field” as it is commonly referred to) using a person’s full name. Use separate fields for first, middle, and last names. The same applies to locations, where it is best to separate city and state for instance.

By doing this, you simplify extracting specific data for your reports.

2) Eliminate any blank records, (a row is a record in a database), as well as any blank cells. Use “N/A” or similar notation for any cells in your database.

3) Finally, and very importantly, do not include any calculated fields in your database. The purpose of a database is to store data, not provide any computed information (that is what your linked reports are for).

Okay, you say, what do we do with this database? That is where the fun begins! Next week we will look at how to use some basic database formulas that will make your Excel life soooooo easy! (And that, as Martha Stewart would say, “Is a Good Thing”)