Thursday, July 28, 2011
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!
Thursday, July 21, 2011
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…
Thursday, July 14, 2011
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 firstname.lastname@example.org 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.
Thursday, July 7, 2011
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:
• Exponential Smoothing (which is so cool!)
• 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!