Counts!

Now, some people have said that I am a bit “Batty”, but that’s another story. As we explored two years ago in this blog, there are several ways to Count (Count/Dracula/Bats, get it?...) your data in Excel.

It is important to know the uses and differences of the major types of Count Functions. In summary, they are:

1. COUNT: Simply counts the number of cells in a range that contain a number

2. COUNTA: Counts the number of non-blank cells in a range (including those with text)

3. COUNTBLANK: Counts the number of empty cells

4. COUNTIF: Flexible counting function (this can be a little tricky, so we will explore with some examples)

The COUNTIF function can be challenging to some users because of the prevalent use of quotation marks within the formulas. The following examples will demonstrate how it works (in each case, the formula uses a range named Data):

Count the number of cells that contain the word "James" (not case sensitive):
=COUNTIF(Data,"James")

Count the number of cells that contain the words "James" and “Suzanne”:
=COUNTIF(Data,"James") + COUNTIF(Data,"Suzanne")

Count the number of cells containing any text (ignoring the numbers):
=COUNTIF(Data,"*") Count the number of 3-letter words: =COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "T":
=COUNTIF(Data, “T*”)

Count the number of cells that contain a value greater than or equal to 15:
=COUNTIF(Data,">=15")

Count the number of cells that contain a value from 5 to 20:
=COUNTIF(Data,">=5")-COUNTIF(data,">20")

This is, I might add, not the best tool to use in many cases. It does, however, come in handy frequently when you analysis requirements are not too demanding. I hope you have had a good week so far, and that you make your weekend, Count…

~ All the best ~

Troubleshooting Excel

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…

Add a Watermark!

There are times when an Excel user may want to include a WATERMARK to indicate a Special Status of an Excel worksheet. You may wish to mark it CONFIDENTIAL or DRAFT, and a watermark can be an ideal solution.

Although this functionality is not built-in to Excel, it is not difficult to place a Picture in your Header to mimic this effect with a few brief steps:

1. Using WordArt, you can easily create an image (such as CONFIDENTIAL) and then upload it into Paint (or other image-handling app) and save it as a .png file.

2. Open the worksheet in which you wish to display your watermark

3. Click on Header & Footer on the Insert tab ribbon

4. Click on Picture on Design tab ribbon and choose your watermark picture

5. Resize your watermark picture by clicking on Format Picture dialog box, select the Options that you want on the Size tab.

It may take a few minutes to do this the first time, but the results are really quite effective. If you ever need a Watermark in your Excel worksheet, give it a try!

Playing Catch-up?

Some of us work for companies that have been a bit slow in adopting newer versions of Excel. Considering the state of the economy these past few years, this is understandable. The fact is, according to a couple of studies, some companies are just now adopting Excel 2007 for their employees.

The change from Excel 2003 to 2007 has been, and is, challenging to many Excel users. If you or someone you know is working through the Learning Curve of transitioning to Excel 2007, there is a nifty little Add-in available from Microsoft that can get a user up-to-speed in very little time.

As Microsoft states on their website, “This add-in adds a Get Started tab to the Excel 2007 Ribbon. Commands on this tab give you easy access to free content on Office Online, such as training courses, video demos, and other Office Online content designed to help you learn Excel 2007 quickly.”

To get this handy little Add-in, you can go to the Microsoft Add-ins site and follow the easy installation instructions: http://office.microsoft.com/en-us/downloads/CD010214099.aspx

Anything that can make a user’s life a little easier is, in the words of Martha Stewart, “A good thing”. Cheers!

What’s Happening NOW?

There are a number of Really Cool ways to use the Time and Date functions in Excel that are both effective and Fun! Let’s take a quick look at them:

The NOW function positions the Current Date and Time into your spreadsheet. Simply enter =NOW() into any cell, and Excel displays the Date and Time in your worksheet. If you want to limit it to just the Date, use the TODAY function, =TODAY().

Using either of the methods above will insert information that will be updated Every Time You Open the Workbook. But what if you want to enter a Static Date that does not update? That would be a Great Way to Date Stamp your work wouldn’t it?

Well, you are in luck! Just select a cell and hold down the Ctrl key and press the Semicolon ( ; ) on your keyboard. Bamm! Instant Time Stamp!

This is one of those Tricks that can be a practical boon to your Excel Life. Have Fun!

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!