Seven Super Shortcuts!

I admit, I love Keyboard Shortcuts. It takes a bit of time for some of them to become second-nature, but in the long run, it is definitely be worth the effort. All of the following involve holding down the ALT key while pressing other keys:

First of all, some cool ways to insert a Chart:

1. Choose a data range & Press ALT + N + A to insert an Area Chart
2. Choose a data range & Press ALT + N + B to insert a Bar Chart
3. Choose a data range & Press ALT + N + E to insert a Pie Chart

You say you want to apply Sorting or Filtering? We have you covered:

4. Press ALT + D + S to apply Sort
5. Press ALT + H + S + F to apply Filter

Finally, if you wish to insert Shapes or Clipart into Excel, you can:

6. Press ALT + N + SH to insert Shapes
7. Press ALT + N + F to insert Clip Art

Well, what are you waiting for?   Give these a try, and raise your Excel Guru Rating a notch or two!

So, You are in the Stock Market, eh?

A little over two years ago, I wrote about how you can easily pull up current Stock Quotes without ever leaving Excel. This is one of those “Oh, Wow!” Excel Guru Tricks you can use to amaze your friends and coworkers (not to mention gather excellent information). In light of the improvements and changes in User Interface (UI) that Excel 2007 and Excel 2010 have brought about, it is time to readdress this Very Cool feature in Excel.

Happily, the days of having to access your daily stock information via the local newspaper is gone. I remember scanning several stocks in the St. Paul Pioneer Press each day when I was a kid (I was a geeky kid – go figure…). Now, of course, there are a great many websites that can provide this information, but since you can do this in your Excel Workbook, (where you may wish to put the information anyway), why not do this with a few quick clicks and look like the Excel Rock Star that you are?

Here is how you go about doing this in the two newest Excel versions:

1. First of all list a few of your favorite stock symbols in a column (Column A is a good place to start) on your worksheet (e.g. SBUX, GOOG, AAPL, GM, etc)
2. Then choose a cell such as C1 and go to your Data Ribbon and select Existing Connections from the Get External Data grouping.
3. This will bring up a short list from which you should choose MSN MoneyCentral Investor Stock Quotes
4. An Enter Parameter Value box will appear, and you can simply choose the list of stocks you made in Column A
5. Excel will access the Internet in a few seconds, and Bam, your Information will be displayed in Glorious Detail!

Well, there you have it! Get your Stock Quotes Here! Get ‘em while they’re hot! Yeah, that’s the ticket…

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!