MicroGraphs for All!

As many of you may know, “Sparklines” first appeared in Excel 2010.  Sparklines make it quick and easy to create readily-intelligible micrographs within the cells of your Excel spreadsheets that highlight important details of your data.  They have the same utility as traditional graphs and charts, but they have the additional benefit of being incorporated right next to the data which you wish to illustrate.

Well, that’s pretty cool, but what if your company is still using Excel 2007 or even Excel 2003, (a recent survey showed a significant number of companies still using these older versions), and you want to use some Cool Little Micrographs on your spreadsheets?  What can you do?

A very effective way to make these engaging little graphs is to incorporate an unpretentious formula and a touch of conditional formatting.  Let’s look at how this can be done…

Let’s say you have the Producers of your company’s products in Column A as illustrated below, and in Column B you have the Units Sold each producer has sold. Here is the simple formula you should put in Cell C2 (and then copy it to C8):

= REPT( “l” , B2/1,000) 

For each Approximate Count of One Thousand, the formula puts an old-fashioned Hash Mark, (using a bold, simple font works well), in Column C. With a bit of Conditional Formatting, the result is a clean, professional report that integrates a MicroGraph! Using these elementary graphs makes your data Visually Comprehensible and enhances the user’s understanding of what is being said in your Excel reports.

If you are using a pre-2010 version of Excel (or even if you are using a more contemporary version), try it out sometime and see what you think. It’s good stuff!

Happy Thanksgiving All!

Sales Analysis with Coefficient of Determination

The correlation of data can be an extremely worthwhile endeavor for any business analyst. Adding some graphical depiction of your analysis can make it even better.

One of my favorite charts and accompanying functions are Scatterplot (XY) Chart and the Coefficient of Determination function. This quick analysis combo truly should be in your Excel Tool Belt!

Let’s say, for instance, that you want to do some speedy research into the efficacy of your sales advertising. Perhaps you want to know whether it is typically better to spend advertising dollars on Tuesday or Thursday.

First determine your ad effectiveness on Tuesdays by selecting data from those days and making a Scatterplot Chart.  Then do the following:

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 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 example above, the COD value is .74 (or 74%) representing a strong correlation between spending advertising dollars on Tuesdays and increased sales.

Then simply do the same for your Thursday data, and compare the size of the CODs. If you get, for example, a Coefficient of Determination of 74% on Tuesday and 41% on Thursday, Hey, it’s a no-brainer!  Spend your money on Tuesdays!

Try using a Scatterplot and Coefficient of Determination sometime when seeking the strength of a correlation of data sets or multiple sets. It is Fast and Effective. Just don’t tell anyone how Easy it was!

Truly Blank?

Today’s topic may initially seem a bit obscure, but let me assure you, if you are ever faced with some odd results from what appears to be routine data, it is something to consider.  Let’s say that you have what appears to be a number of blank cells in the range on which you are performing a calculation.  If you are getting strange results, you might ask yourself, “Are the blank cells actually blank?”  The truth is that it is Not Always Easy to know whether a cell or cells in Excel are Truly Blank!

The reason it is difficult to immediately know if blank cells are truly blank is due to the fact there are several ways of Hiding Data through:

o   The use of identically-colored fonts

o   Empty-string results of a formula

o   Masking the data with the use of Custom Formatting (three semicolons: ;;; )

It can cause Mayhem with your calculations.

To detect this Invisible Data, there are at least a couple of techniques.  Assuming your cell in question is A1, you can:

1.      Simply insert this Function in an adjacent cell:  =ISBLANK(A1) 

o   If the cell is Blank, it will return True; if it is Not Blank, it will return False

o   Copy the simple formula to include the rest of the range you are investigating

2.      A second technique it the use an IF Statement as follows:  =IF(A1<>"","Not Blank", "Blank")

o   This IF Statement obviously returns Blank or Not Blank

 

o   You can then take the appropriate action with the Not Blank cells

By determining if your cells are Truly Blank, you can help Avoid Quirky Results on your worksheet.  And, as Martha Stewart might say, “That’s a good thing…”

Keeping Things Safe

How many times have you heard or read that you should routinely back up your data on your computers?  Well, protecting your Excel work can be similarly important. 

Perhaps you are asking “Why is this important?” Maybe you have never done this, and never had a problem. That may be just fine if you are the only one using one of your Excel masterpieces, but if are sharing your work (and most of us probably are) with others there will come a time when the others will want to “Experiment” with your formulas and format. Don’t let this happen!  The construction of your workbook may have taken dozens of hours to create, and there is the potential for substantial ruin!

Happily, Excel has built-in Protection Tools to help us all out.

Let’s take a look at Excel 2013 for a How-To Example (other versions are similar):

Protecting and Unprotecting a Worksheet with a Password

1. If there are specific cells that you wish to enable users to modify (such as a Data Entry Range in a dynamic 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. In the example below, cells B5:B14…

2. Next, click the Protect Sheet button in the same dialogue box. Excel in turn opens a Protect Sheet dialog box (see below), 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. This should barely warrant mentioning, but it does happen.

One Last Important Note: Protecting your worksheets is Not making it absolutely Secure.  It is not ample protection to prevent users from accessing confidential or sensitive data, and any backyard hacker can break it.  It is for casual protection.

Protecting Your Worksheets.  Certainly a Best Practice for any Excel practitioner, and one worth your time. Give it a try, and find out how easy it is to add a bit of protection to your hard work.