Use Excel for Exchange Rates of Currency

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 from countries feom Argentina  to Venezuela!

If you are using Excel 2007 or 2010:

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 1,883 Columbian Pesos for one US Dollar today(sounds like a bargain to me…)!

Database Best Practices

It has recently occurred to me that the design, construction, maintenance, and information-mining of Good Databases are the quintessential keystones of knowledge for any Advanced Excel User.

Creating a Database in an ongoing Excel workbook can save you time, money, and frustration. By creating a database for information that is routinely updated, you can automate your reports and simplify your users’ interface.

Today we will look at what constitutes a Good Database, and what pitfalls to watch out for.

First of all, a database should contain data, and that is all! No formulas should exist in a database, just pure Data waiting to be turned into Information on a separate worksheet.

Secondly, there should be no blank rows (as you know, they are called “Records” in a database) and No blank columns (called “Fields” in a database).

Thirdly, put only one piece of data in each field. This will eliminate the need for repeating fields, and make your information-mining much easier.

Lastly, make sure the information is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

Now that you have a Magnificent Database, you can Mine it for Information!

Certification in Excel 2010

In these uncertain economic times, it is always good to be able to set yourself apart from your competition through the pursuit of Education and objective Certifications. Anyone can claim to be an Expert in Excel when applying for a new job or elevated position, but those who have bonafide certification from Microsoft will inevitably be held in higher esteem (unless, of course, you are competing against the owner’s nephew…).

Passing the Microsoft Exam 77-882 awards you the Microsoft Office Specialist certification in Excel 2010. Having successfully taken MOS certifications in the past, I can tell you that the preparation work for the testing will most probably introduce you to new skill sets that are outside of your comfort zone. This is, in itself, a good exercise for any Excel Professional.

One note of caution, however: Currently, Microsoft provides very little in the way of formal study materials for the Excel 2010 exam. This can be circumvented, however, by using the plethora of study materials available for the Excel 2007 certification exam and then making sure that you study the new features introduced in Excel 2010. This is important, as Microsoft always likes to test on the new elements being introduced in its latest software upgrade.

In addition to studying the materials for the Excel 2007 certification, it would be wise to become at least reasonably familiar with the following New Excel 2010 tools:

• Sparklines
• Backstage
• Slicers
• New Pivot Table Features
• New Statistical Functions

Becoming Microsoft certified (my wife tells me I am Certifiable, but I think she may be referring to something else…) in Excel can give you a Distinct Advantage in these competitive times. You may wish to consider it…

Spinner Buttons

Since they control the data that can be entered and are easy-to-use, so-called “Spinner” buttons can be a clever addition to a spreadsheet. To add a spinner in Excel 2003 and in earlier versions of Excel, click the Spinner button on the Forms toolbar, and then draw your spinner on your worksheet. (You can size the Spinner to your liking.) To add a Spinner in Excel 2007 (similar in version 2010), click the Developer tab, click Insert, and then click Spin Button in the Form Controls section.

Now you are all set to have some fun! Right-click on the spinner, and then click Format Control. On the Control tab complete the values as follows (this is a test, ma’am or sir, only a test…):

1. Current value: 1
2. Minimum value: 1
3. Maximum value: 10
4. Incremental value 1
5. Cell link: $C$7 (Note: Any of these values can be of your own choosing.)

Now when you click the Spinner control, cell C7 is be updated according to the parameters you set. If you have created a worksheet where other cell values or results are dependent on the value in C7, your worksheet will update according to the quantity you select with the Spinner.

How cool is that! Make your Excel worksheets look like someone spent hours of programming time on them in just 5 minutes. Give it a try (people will think you are a Star!).