Names and Better Databases

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

If you have been working with Excel for a long time, 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 I am sure 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 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 comparatively simple formulas can really save you tons of work. Don’t be shy, give them a try!

Scatterplots and COD

No, we're not talking Scatterplots and Codfish... As was discussed about back in November of 2010 in this blog, Scatterplot (XY) Charts and the Coefficient of Determination (COD) function are powerful tools for some quick analysis. This combo truly should be in your "Bag of Excel Tricks"

A Scatterplot Chart is commonly used to show the relationship between two variables or sets of data. For example, a sales manager could plot the Number of Sales Calls Taken with the Number of Sales Made. Another example is comparing the Average Length of Time a customer service representative takes per call and the Overall Quality Score of their calls.

To determine how strong the correlation is between the sets of data, wily Excel users such as yourself can make a Scatterplot Chart and:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your aesthetic preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

The R-Squared value is your Coefficient of Determination (COD) that will tell you how strong your data on your two axes. In the graph example above the COD value is .5574 (or approximately 56%) representing a Strong Correlation (and therefore reliable).

Wow! That's all there is to it! Try using a Scatterplot and Coefficient of Determination sometime when seeking the strength of a correlation of data sets. It’s remarkably easy and you can reveal valuable information in the blink of an eye!

Custom AutoFilters

The AutoFilter is one of those Wonderfully Simple, yet Powerful tools that are readily available to any user of Excel. To find AutoFilter, simply click on the Data tab and go to the Sort & Filter grouping.

Then select the data that you wish to filter on your spreadsheet, and choose the Filter (looks like a Funnel). DropDown arrows will immediately appear on your column headers, allowing you to Slice & Dice your data as you wish.

Now is Where the Fun Begins!

Let’s say you are a teacher of manager that wishes to quickly Identify all of the Scores in your database that are greater-than 80% and less-than 90%. Piece of Cake!

All you need to do is click on the Down-Arrow of your Scores field and choose: “Number Filters”. This will bring up a dialogue box that will enable you pick for a vast variety of Customizable Parameters.

You’ll soon be Slicing and Dicing so well, you will be ready for your own show on Food Network!

Keyboard Shortcuts: The Final Chapter

You say you are a Diehard Keyboard Shortcutist, (yes, I just made up the word, “Shortcutist”…), that has mastered most of the typical timesavers, but Still Want More? This post is for You!

Why use Keyboard Shortcuts? Speed, Ergonomics, Professionalism, Coolness; Simple as that!

In Rapid-Fire Mode, Here We Go with 10 Great Tools:

1) Insert Current Time: Ctrl+Shift+;

2) Autosum Selected Cells: Alt + =

3) Hide Current Row: Ctrl+9

4) Hide Current Column: Ctrl+0

5) Select Data in Current Region: Ctrl+Shift+8

6) Format Current Cell with Comma Formats: Ctrl+Shift+1

7) Format a Number as Currency: Ctrl+Shift+4 (Ctrl+$)

8) Go to the Previous Worksheet: Ctrl+Shift+Pg Up

9 ) Go to the Next Worksheet: Ctrl+Shift+Pg Down

10) Insert a Cell Comment: Shift + F2

Keyboard Shortcuts: Far Out, Man!

Protect Your Work!

Well, you probably don’t have a Doberman Pincher to Protect your Excel work, but you have easy-to-access tools that will help you out with this important task. Maybe you are asking “Why is this necessary?” Maybe you have never done this, and never had a problem. Trust me, however, if others are viewing/using your workbooks, there will come a time when they will want to “Experiment” with your formulas and format. You don’t want this to happen!

Let’s take Excel 2007 for a How-To Example:

Protecting and Unprotecting a Worksheet with a Password

1. If there are specific cells that you wish to enable users to modify (such as DropDown Boxes in a report), go to the Review tab and select the Allow Users to Edit Ranges in the Changes group and select the range you wish to keep accessible.

2. Next, click the Protect Sheet button in the same group. Excel opens a dialog box, where you can Assign a Password, and select the Permissions you wish to be available to the users.

3. Click OK

You can easily Unprotect the worksheet with the password anytime you wish to make changes. And, of course, as this can cause a business disaster (people have been fired for losing this), Be Sure to Keep Track of the Password. I know, it barely warrants mentioning, but it does happen.

This is a good habit and certainly a Best Practice for any Excel practitioner. Give it a shot if you haven’t already!

Solving Power!

Microsoft Solver is one of the most Overlooked tools in Excel, and bears occasional review. 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 and Index Revisited

Back in 2010, we took a look at the Power of using a table or database combined with the MATCH and INDEX tools in Excel. Due to the lack of use of these Fabulous Functions, (VLOOKUP is more popular, but less robust), today we will Reexamine how and why you should keep this technique in mind.

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

Using the MATCH and INDEX functions together is truly a Powerful way of extracting data when you need it. Once you try it, you will be Amazed!