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!