Hiatus!

A line from an old Clint Eastwood movie went, “A man’s gotta know his limitations…”.  I can relate to this, as I find myself quite enormously busy these days with many and sundry projects, classes, books, etc.  (Always a good thing…).

 It is due to the foregoing that I will be taking a hiatus from writing this blog.  If you are a Kindle subscriber, you should immediately cancel your subscription.

 I very much appreciate your readership and your fine comments of the past, and I hope we meet up again in the future.

 Meanwhile, have you ever Inherited an Excel workbook that simply didn’t make sense? As we discussed in this blog a couple of years ago, problems inevitably happen in Excel. One of the most common issues is, of course, the use of formulas (and, let’s face it, the peculiar way some users set up his or her workbooks).

Troubleshooting is, without question, a Valuable Skill in Excel!

An easy way to identify the cells with formulas (and display them) is to hold down the Ctrl button and press ~. When you press this keyboard combination, all of your formulas will appear in their cells (pressing the combination again will bring back your original view). That is a good start for determining what is going on “behind the scenes”.

Of course the foregoing does not solve all of your investigative challenges. The Vexing Problem can be trying to determine from where a formula is drawing its information. Excel contains an Elegant Tool for clicking on the formula cell and tracing its Precedents. For Excel 2003, go to Tools / Formula Auditing. For Excel 2007 and 2010, go to Formulas / Formula Auditing.

Clicking on Trace Precedents will give you a Graphical Illustration with Arrows that show you from where your information is being derived.

Ctrl + ~ and Trace Precedents are two excellent tools that can help you figure out those cryptic Excel workbooks that you inherit from others. Such a good thing…

All the best,

Bob DeLaMartre

Top Ten Favorite Shortcuts

Every year or so, I like to access 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.

As an Advanced Excel User, I am sure you understand the benefits of Keyboard Shortcuts. They add speed, efficiency, relief from the stress of overusing the mouse, and they are Just Plain Cool!

So, in David Letterman-style, here are my current favorite (and highly recommended) Top Ten Excel Shortcuts in reverse order:

10. Shift + F3 (Pulls up your Insert Function – Handy!)
9. F5 (Brings up the Go TO dialogue box – Great for navigating to a named range)
8. Ctrl + F4 (Closes the Current Window – Quick and easy!)
7. Ctrl + P (Pulls up the Print Dialog Box – Gotta love it)
6. F12 (Brings up the Save As dialogue – Nice!)
5. Ctrl + Z (Undo Previous Action – Oldie, but a goodie)
4. Ctrl + H (Find and Replace – I use it often!)
3. Alt + = (Brings up the AutoSum – Beats going to the toolbar)
2. Ctrl + Home (Brings you to the Start of the Worksheet – It is always good to go home)

And My Current Number One Best Shortcut Is (Drum Roll and Cymbals, Please)...

1. F11 (Creates an Instant Chart – Always a crowd pleaser!)

There you have it! Try a few of them, and see how they can make your Excel life better!

More Lines of Text

Since this is a blog for Advanced Excel Users, I sometimes hesitate writing about a technique that is quite simple. What I have found over the years, however, is that some Simple techniques have been overlooked by some truly accomplished Excel practitioners.

Although the focus of Excel is numbers, there are times when you need to enter text into cells as well. Sometimes you may even want to add another paragraph (or perhaps just a line break) in a single cell. If you are working in Word, that’s easy – just hit the Enter key.

In Excel, however, hitting Enter will just take you to the next row below. “Well, of Course, You Say!” But what if you want to Add More Lines of Text within the selected cell? The solution is so easy it will make you laugh.

All you need to do is press Alt+Enter to start a new line in the cell in which you are typing or editing. That’s all there is to it! (See if you colleagues know that one…). Cheers.

Formatting Data Your Way

This is one of my Favorite Excel Tricks, and it should be in your arsenal of weapons against Poorly Formatted Data.

As an advanced user, you undoubtedly have many times been presented with data that is Not in an ideal format. One common case is when you are presented with list of employee names showing the Entire first and last name in each cell in a column.

As you are aware, Database Best Practices dictate that it is best to Break Down or Parse Your Information into the smallest discreet portions reasonable. Therefore, you would naturally prefer to have the first names in one column and the last names in another column. Of course if you have hundreds of employees in your company, this would be Pure Drudgery (who needs that?...) to convert manually.

Extracting the first names and last names into their own separate fields (columns) with Convenient Excel Formulas is a Breeze (can you feel it whistling through your hair?).

The Excel Functions that we will be Using in the Formulas are:

A) LEN - Returns the number of characters in a text string

B) FIND – Returns the starting position of one text string within another text string

C) LEFT – Returns the specified number of characters from the start of a text string

D) RIGHT - Returns the specified number of characters from the end of a text string

To extract just the First Name, use the following:

=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:

=RIGHT(A1, LEN(A1)-FIND(" ", A1))

These simple formulas can save you tons of work. Now it’s your turn, give them a try!

Just for Fun…

Once in a while, even an Advanced Excel User should have a bit of Geeky Fun. Although of no real Practical use, it can be interesting, (once again, in a geeky sort of way), to play around with Roman Numerals. Even for an Excel guru, it may be surprising to find that the application has a built-in Roman numeral function.

Practicality, of course, can occasionally be overrated, and it is readily apparent that Hollywood, Football, and the Olympics have all used Roman numerals on a regular basis. If you also wish to do this sometime in Excel, (I probably wouldn’t recommend it for your next quarterly report to your boss, unless the individual has a really good sense of humor), you can use the Roman function.

For a Classic Numeral, (other formats are available, but who needs them…), simply enter a value in cell A1 and type, “=Roman(A1)” in cell B1. Hit Enter and Presto, a Roman numeral of the A1 number!

Now you will be all set if the NFL or Olumpics needs someone to come up with the name for the next Major Event!

Micro-Graphs for All!

As most Excel Gurus know, there is a new Micro-Graph functionality in Excel 2010. Cool stuff, but if you don’t have that permutation of Excel, here is a way to make a simple Micro-Graph that resides in your table and Works in Any Version of Excel!

This is actually very easy. Let’s say you have your Products (or sales reps) in Column A as illustrated above, and in Column B you have the Units Sold. Here is the formula you should put in Cell C2:

= REPT( “l” , B2/10) and then copy it to C7

For Each Approximate Count of Ten, the formula puts a Hash Mark, (using an Arial font works well), in Column C. The result is a simple, easily read, Micro-Chart!

Try it out in the office, and show them once again why you are the Excel Guru of the Known Universe!

Concatenation for the Advanced User

This is one of my favorite time savers. I teach Excel, and I have had classes as large as 200 students. Since I believe strongly in frequent communication, I keep in touch with individual students and the group as a whole. Having a spreadsheet of student names (or company employees perhaps) is great to stay organized, but what if you want to use your data for sending emails? This is where Concatenation Shines!

Consider that you have a list of students with the First Names in Column A and Last Names in Column B. The good news is that you can easily combine them into an Email-Friendly column of names in a “Last Name, First Name” format is easy. The secret to Concatenation is using Ampersands and cell references combined with quoted text or punctuation. Here’s how to do it:

Assuming your table starts in cell A1, put the following formula in C1 (be sure to note that there is a space after the comma in quotation marks):

=B1&", "&A1

This simple formula combines the contents of B1 (last name)with a comma, space, and contents of A1 (first name). Select C1, place your cursor over the “handle” in the lower-right corner of the cell, and give a quick double-click. This will populate your Column C Email List down as far as you have data in Columns A and B.

Then it is a simple matter of copying the contents of Column C, and pasting into Outlook. Bamm! You have just saved a Ton of Time!

Creating and Using Excel Templates

As an Advanced Excel User, you want to avail yourself of every accessible efficiency tool. Excel Templates are files containing common data and formatting options used as Models for other spreadsheets.

A Template is a great shortcut for standardizing the look and functionality of your data. Templates can also provide you with a Standard Framework in which to input and share your data. Reusable formulas and fields enable you to Speed Up your input and get the information that you want in the form you want.

Creating a Template:

1. Open a worksheet you want to use as the basis for the template
2. Choose Save As and enter a name for the template
3. From the Save as Type list, select Template
4. Save the file inside the Templates folder

Using a Template

1. Go to the File menu and choose New
2. Click on Templates and select the general category
3. Double-click on the desired template
4. A new worksheet opens and displays the template
5. Be sure to Name the worksheet when you save it

That’s all there is to it! Try it out – Templates can Save You a Lot of Work, and help you maintain a consistent, Professional Look.

The Power of Solver!

Microsoft Solver is one of the most Overlooked tools in Excel, and bears occasional review. . As an Excel Guru, it deserves to be part of your repertoire. Although you may not have an immediate need for it, knowing that it exists, and how this Free Add-in works, can result in a future Brainstorm!

"Solver" is an example of how computing power can save you from a plethora of second-guessing. Let's take a look at what it can do and how you can leverage this Powerful Analytic Tool:

Where is it?

To find it in Excel 2010, click on the File Tab, and click the Excel Options button at the bottom of the dropdown. Then choose Add-Ins and select Solver Add-in.

To find it in Excel 2007, simply click on the Microsoft Button, and click the Excel Options button at the bottom of the dropdown. Then choose Add-Ins and select Solver Add-in.

After you load the Solver add-in, the Solver command is available in the Analysis group on the Data tab.

What it can Do for You!

My Classic Example is one where you have several shifts of call center employees that overlap, and you are trying to optimize the scheduling to best handle the projected incoming calls. By using MS Solver, you can quite quickly find the most Favorable Balance for the schedule.

The trick is set your Target Cell (this may be a cell in which you are trying to find the Best Sum, Average, or Standard Deviation) in the Solver Parameters, and make it subject to various cells that you wish to change (in this case, it would be the totals for each shift). You can also make it subject to Constraints such as Whole numbers (good when counting people...).

MS Solver can be effectively used to maximize Sales/Profit Plans, Strategic Planning, Optimizing a Product Mix, and even picking a Winning Team! There are Countless Applications that are only limited by your imagination.

It can take a bit of effort setting up your worksheet, but the results are Outstanding! Demonstrate once again that you truly are a Genius! Give Microsoft Solver a try!

Match plus Index = Power!

You are an Excel Poweruser, and you say you often use the VLOOKUP function, but would like something a bit more versatile and powerful? Look no further than the MATCH and INDEX functions!

If you want something more versatile than VLOOKUP, MATCH and INDEX can serve as your humble servants. Let’s take a look at a simple example using the Illustration above:

For instance, MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0))

Once you try this, you will be Amazed! Using MATCH and INDEX functions together, now that’s POWER!

Options!

In the old Clint Eastwood movie, Magnum Force, Harry Callahan used the line, “A man’s got to know his limitations”. True as that is, Harry always liked to have Options. For this first post of 2012, I thought it would be interesting to take a look at Option Buttons.

Option Buttons can be very useful in creating Quizzes or Polls in Excel, and are not too difficult to create. There are a few steps, but I guarantee that it will be worth the effort! Here is how you do it:

1. Make sure the Developer tab is visible on your toolbar

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 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

Hooray, We’re Almost 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 $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF(E5=3, "Correct!", "Incorrect"))

Now when the user chooses Option 2 of the three possible, they are rewarded with the “Correct” feedback. Option Buttons, give them a try; “It is Always Good to have Options…”

Happy New Year All!