Three Terrific Tricks!

In the 4+ years this blog has been published, three of the most popular Excel Tricks have been the following. They are Easily Mastered, and they can definitely make your life a bit easier in Excel World…

#1 Selecting Data or the Entire Worksheet
Select any cell in your database and click Ctrl+A on your keyboard; Bamm! You have selected the database. Want to select the entire worksheet? Just click the “A” once again!

#2 Copy the Formatting of a Cell or Range and Apply it to Another Cell or Range
Select the cell or range with the formatting you wish to copy, (Note: This works for Conditional Formatting as well), and click the Paint Brush on your toolbar. Your cursor will then turn into a brush that you can use to “Paint” any other cell or range with the formatting you have picked up from the previous cell or range (think of the selection as the Paint Can).

#3 Display Formulas So You can Troubleshoot Issues
This is so easy, you will laugh. Select any cell on cell on your worksheet and while holding down the Ctrl key, press “~” on your keyboard. Alacazam! All of your formulas will be visible!

Are these Simple Tricks off the Hook, or What! Take five minutes and give them a try!

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!