Wednesday, July 25, 2012

Traveling with Excel

With the Internet and ever-expanding jet travel, it is axiomatic to say that we are in an “Ever-Shrinking World”. National Currencies have frequent fluctuations, of course, and our friend Excel Can Help!

So, let’s say you have scheduled a business/pleasure trip to Japan, 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. It Couldn’t Be Easier!

If You are Using Excel 2007 or Excel 2010:

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

If You are Using the Older (but still serviceable) 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

Presto! In a few seconds, you will have the exchange rates for Australian Dollars to Venezuelan Bolivars!

Okay, You World Traveler, You – Is that Cool or What? I see that today you can get 78 Japanese Yens for one US Dollar (sounds like a bargain to me…)!

Wednesday, July 18, 2012

Excel 2013 Preview is Here!

The Excel 2013 Preview is now ready for download, and it is Pretty Exciting! While presenting an updated, but familiar interface, Microsoft has made several improvements to our favorite spreadsheet program.

What strikes me as particularly imaginative in this new version is the advances in Excel’s ability to Predict what the user wants to do. Based on your data, Excel 2013 will recommend Charts, Pivot Tables, or a Fill for a range (called “Flash Fill”). Excel learns from the patterns in your work and makes intelligent and time-saving suggestions.

The new Quick Analysis tool that appears to the lower-right of a selected range is a tremendous boon to any power user. Symbols or colors help you analyze data in just a click, as Excel 2013 uses these elements to identify and highlight trends and changes. I really like this cool new tool, as it can immediately add Pizazz and (Gasp!) comprehension to your worksheets.

Microsoft has truly leveraged the Internet this time. Workbooks are by default saved on SkyDrive (Free!) or SharePoint. You can send a link to anyone, along with editing permissions if you wish. You can even select portions of your work and share it on Facebook or other social networks!

Although I haven’t had the new software long enough to explore All of its many new features, I can unguardedly say that this innovative new version really Rocks the Casbah! Go to Microsoft.com to download a free trial version today.

Wednesday, July 11, 2012

A Deeper Look at Trendlines

Trendlines in charts are essential when it comes to Viewing the Big Picture. The overall trend of your data is valuable information that may not be readily discernible. Adding a Trendline can make all the difference in the communicative value of your chart.

First of all, let’s review how to add one to a chart. To add a Trendline, simply 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 Which Trendline Should You Use?

Aye, that is the question! Trendlines come in several different flavors. The key is to choose the one which most reflects the, well, Trend of your data (that may or may not be obvious…). This can be ascertained by determining the R-squared value of the various trendlines. You can easily do this by checking the Add R-squared value to chart box in the Trendline Options area.

To Help you Choose the Best Fit, the Following is a Concise Description of the Various Trendlines:

Linear Trendlines
For simple linear data sets, a linear trendline is a best-fit straight line. Note: Your data is linear if the pattern in its data points approximates a line, and typically shows that something is increasing or decreasing at a steady rate.

Logarithmic Trendlines
A logarithmic trendline that describes a curved line that is used when the rate of change in the data increases or decreases quickly and then levels out.

Exponential Trendlines
An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing rates.

Power Trendlines
Power trendlines are curved lines typically used with data sets that compare measurements that increase at a specific rate. Often used in examples of acceleration.

Polynomial Trendlines
This is a bit of a different animal. A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. Though more challenging to use, it certainly has a place in your Excel Tool Belt.

When you know how to use the easily-mastered Trendlines, a world of quick Excel analysis opens before your eyes. Gotta love it!

Friday, July 6, 2012

It is Good to have Options

Greetings All! I hope you have had a great week so far!

In this post I thought it was a good time to “Walk on the Wild Side” a little bit, and look at the (surprisingly easy) use of Option Buttons.

Option Buttons have several applications, and can be especially useful in creating Quizzes or Polls in Excel. There are a number of steps, but I guarantee that it will be well worth it if you hang in there and give them a try!

Here is how you do it:

1. Make sure the Developer tab is visible on your toolbar (available in Excel Options)
2. Under the Developer tab, choose Insert / Option Button (Form Control)
3. Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices
4. Now, (this is Very Important), to make the buttons work together, return to your Insert dropdown and choose Group Box (Form Control)
5. Then draw your Group Box all the way around your Option Buttons

Just Three More Steps and We’re There!

6. Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate
7. Now is where the Fun really begins. Create a Formula that is based on the value that is shown in the Cell Link
8. For example, let’s say you have linked three Option Buttons to cell $A$1 and you wanted Option 3 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell A2, type =IF(A1=0,"", IF(A1=3, "Correct!", "Incorrect"))

Now when the user chooses Option 3 of the three possible, they are rewarded with the “Correct” feedback. Option Buttons, give them a try; It is always good to have options…