Top Ten Keyboard Shortcuts for 2012

As any of you long-time readers of this blog know, every year or so, I like to take a look at what my Current Favorite Excel Keyboard Shortcuts are. This changes over time as I learn new tricks and incorporate them into my daily work with Excel.

Have no doubt about it, learning and using Keyboard Shortcuts in Excel will greatly enhance your speed and efficiency. Using shortcuts also will help relieve stress from overuse of the mouse, and will Set You Apart from Mere-Mortal Excel Users.

So, in Reverse Order, here are my current favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2012:

10.  CTRL+SHIFT+% (Applies the Percentage Format with no decimal places – Handy!)
 9.   CTRL+; (Enters the Current Date – I’m a time freak…)
 8.   ALT+F1 (Creates an Embedded Chart of the selected data – Far Out!)
 7.   Ctrl + H (Find and Replace – I wouldn’t want to live without it!)
 6.   CTRL+9 (Hides the selected rows – Tidies up your worksheet…)
 5.   F5 (Brings up the Go TO dialogue box – Great for navigating to a named range!)
 4.   SHIFT+TAB (Moves to the Previous Cell – I use it all of the time…)
 3.   Ctrl + Home (Brings you to the Start of the Worksheet – Oldie, but a Goodie!)
 2.   ALT+F4 (Closes Excel – Hey, you’ve got to knock off work sometime…)

And My Number One Favorite Shortcut for 2012 is (you may think this is a curious…):

 1.   CTRL+K (Displays the Insert Hyperlink dialog box for new hyperlinks – Very useful when making sophisticated, interactive workbooks!)

There you have it! Try a few, and see how they can make your Excel life Even Better!

The Efficacy of Goal Seek

After our stroll through one of the cool new features of Excel 2013 last week, I thought it would be good to take a look at a Classic Management Tool that has been available in Excel for several years.

"What If” scenarios are essential instruments of analysis for nearly any business. Excel provides a great tool called Goal Seek which allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. As with so many little-used tools in Excel, it can save you a ton of work, and make you look good in the process.

Now we will utilize a very Simple Example to explore how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the executive branch. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Assumption: The Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)

1) Put the number of Expected Calls into Cell B2
2) Insert the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

Obviously, this tool can be used to greater advantage with more complex scenarios, but you can see by our the example that Goal Seek can be a handy tool for the boardroom or any planning venue. Give it a try sometime!

Excel 2013 Quick Analysis Tool

Have no doubt about it – Excel 2013 brings an improved interface and several Really Cool New Tools!

One of my favorites is the Context-Sensitive Quick Analysis Tool. This great new gizmo makes it considerably easier to take the data in your worksheet and enable the creation of:

1. Charts
2. Tables
3. Pivot Tables
4. Sparklines
5. Conditional Formatting
6. Totals

All that you need to do is select a range of data, click the Quick Analysis icon that appears to the lower-right, and an easily navigated box of suggestions pops up giving access to all manner of analysis options.

The Quick Analysis tool is Truly Brilliant at allowing users to perform complicated tasks with a few quick taps. For instance, in the example below, the range from B3:D18 was selected, generating the Quick Analysis tool Icon that reveals the Major Categories of Formatting, Charts, Totals, Tables, and Sparklines. Clicking on any of the Major Categories gives you several options from which to choose.

The Quick Analysis Tool. One more reason to consider upgrading to the latest version of Excel. As I often tell my students, it is good to stay current with the latest versions of software, lest you wake up one day terribly behind the technology curve.

Multi-Tiered DropDown Lists

I am sure that we all agree that DropDown Boxes are highly useful when creating Interactive Reports. Having only one dropdown box in a report can be insufficient, however.

Let’s say that you have a large database with 60 different cities and 11 states. If you want to use the City Name in an Interactive DropDown List to pull up reports based on that city, you are faced with at least Two Potential Problems:

     • You are going to be faced with a Long List of City Names in your dropdown (60 in this case)
     • Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)

So wouldn’t it be Cool if you could build a Dropdown List of States that would generate a second Dropdown List of Cities for that state only? This way you could use the parameters of Both the State and the City to glean your information!

Here is How You Do This:

First of all, let’s assume you have a Horizontal Database containing your States and Cities in E2:N12, and your interactive cells set up in B2:C3 as per the illustration above. Note: The list of States would be in the first column of the database, E2:E12.

1. Select the table, E2:N12
     a. Go to Formulas / Defined Names and choose Create from Selection
     b. Assure that the check box with Left Column only is checked and click OK

2. Select B3 and create a dropdown box by using Validation
     a. Under “Allow” choose List and select $E$2:$E$12

3. Select C3 and create a dropdown box using Validation
     a. Under “Allow” choose List and insert the formula =INDIRECT($B$3)
     b. Click OK (Click Yes if you get an error alert…)

4. Now for some cleaning up…
     a. Select, F2:N12 (Note: Do Not include Column E)
     b. Go to the Home tab and select Find & Select from the right-hand side of the ribbon
     c. Select Go to Special and choose Blanks and click OK
     d. Right-click the selected area and Delete / Shift Cells Left

That’s It! This really isn’t very difficult, and the resulting Multi-Tiered DropDown Lists are incredibly useful when setting up Sophisticated Interactive Reports.

Have no doubt about it, people will think you are a Genius when you show them this stunning technique. Give it a Try!

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

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.

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!