Give Me the Green Light!

Let’s face it, Excel worksheets can at times be a Bit Drab. A worksheet can serve as a Decision Tool and showing a Yes, No, or Maybe next to your agenda items can get the job done, but it’s really Ho-Hum, isn’t it? Since the introduction of Excel 2007 you have some very interesting options, however. You can use sets of Graphical Icons with your decision lists, and give your reports some Much Needed Life!

For an example, let’s suppose we have a proposed agenda for an upcoming annual corporate meeting. Possible topics have been submitted from numerous sectors, and you want to use a Semaphore for an icon when make a preliminary worksheet showing a:

• Green Light for a definite inclusion in the program
• Yellow Light for a possible inclusion
• Red Light to exclude the topic from the agenda

Here is How to Accomplish This:

1.  Select the range of cells in which you want a Semaphore to appear
2.  Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
3.  Go back to Conditional Formatting and choose Manage Rules
4.  Click Edit Rule and put a check in the Show Icon Only box and Apply
5.  By default, a “1” will be a Red Light; “2” will be a Yellow Light, and “3” will be a Green Light

Your Decision Lists will instantly take on a New, Engaging Look and will be sure to garner the closer interest of any of the users. And, of course, the cool thing is that it is Easy (Isn’t anything when you know how…).

The Missing Data Entry Form

The highly useful Data Entry Form was a favorite of many Excel users in the past, and for many savvy gurus, it still is.

Besides being an efficient way to enter data into an Excel database, the Form also allows you to:

   • Edit or delete individual records
   • Scroll through each of your data records
   • Locate records containing specific information

The primary use for most Excel aficionados is, of course, Data Entry (ergo the name of the form…).

Back in the day of Excel 2003 (and prior versions), the Form was neatly located in the Data dropdown list on the toolbar. Starting with Excel 2007, however, it was not shown on any of the default ribbons, and many users simply forgot about this great little tool (or thought it was no longer available).

So Where Did It Go?

Good News: It is still available, even on Excel 2013. To add it onto the ribbon of your choice, simply do the following:

   1. Go to Options / Customize Ribbon
   2. Choose Commands Not Shown on the Ribbon
   3. Scroll down to Form and add it to a New Group in the location of your choice

This handy little form will now be readily available whenever you may need it. If you have never used it, give it a try!

Why You Should Use Subtotals

Quick and Easy. Always a good thing. Using Excel’s built-in Subtotal feature can save you an enormous amount of time and give you a remarkably easy way to Organize Your Data with practically no effort. Here are 3 Reasons Why you should put Subtotals in your Excel tool belt:

1. Versatility: As you would expect, Subtotals are able to summarize your data by SUM. Additionally, however, your data can also be reviewed by Count, Average, Max, Standard Deviation, and others.

2. Dynamic: With the ready access to controlling what type of data and which field at your fingertips, you have the ability to slice and dice your information on the fly (Good Stuff!).

3. Sharable: Often what an analyst or manager wants is a “Quick and Dirty” way of reviewing their data in a logical manner. Moreover, however, it is typically desirable to share the Information (“Information” is clearly superior to mere “Data”) with others. Subtotals are a sharp, concise way of doing exactly that!

As with so many marvelous tools in Excel, Subtotals are far too often misunderstood, ignored, or simply not thought of. Give it a try, but here’s an Important Tip: Since you need to initially sort your data according to what you wish to summarize, it might be a good idea to save it to a Practice File before trying this out for the first time.

Subtotals. Another example of Excel Gold.

Quick Stats

Statistics. As Mark Twain once said, there are “Lies, damned lies, and statistics”. Nonetheless, Statistics are integral parts of our everyday lives. We are bombarded by statistics in advertising, news stories, and the popular media. They are, frankly, Essential to our modern way of life.

As Excel users, we are much more likely to use statistics than the Average Joe. A very Quick and Easy way to obtain most of the common stats used in business is to go to the Data Ribbon in Excel and choose Data Analysis (on the far right of the ribbon).

You can then try this out by choosing Regression from the Data Analysis dropdown. Using the data shown above, put the Sales Data in the Input Y Range and the Month Data in the Input X Range, and choose a single cell for the Output Range.

The results will Instantly give you the following Regression Statistics:

• Multiple R
• R Square
• Adjusted R Square
• Standard Error

You will also get grouped ANOVA stats for Regression and Residual.

For those of us who live with statistics, this technique can be a boon for obtaining a Quick Summary of your Stats. 4 out of 5 Excel Gurus Recommend It!

Military Time

As anyone who has been in the service (or watched enough movies) knows, the military operates on the basis of a 24-hour clock, beginning at midnight (0000 hours). So, 1:00 AM is 0100 hours, 2:00 AM is 0200 hours, and so-on up until 11:00 PM which is 2300 hours.

Not only is it used in the military, this system is the most commonly used time notation in the world today, and is the international standard for noting time.

When it comes to Excel, some of us may be faced with translating Military Time to our typical U.S. Standard Time. This isn’t terribly difficult, but a couple of Cool Formulas really help out!

First of all, let’s assume you have Cell A1 populated with military time expressed in the typical 4 digits. Using the TIMEVALUE function, which very neatly converts time represented by a text string into the Decimal Number Excel can work with, we can use the following to perform our quick bit of magic:

=TIMEVALUE(LEFT(A1, 2) & “ : ” & RIGHT(A1,2)

What this formula does is to simply choose the left two digits and put a colon between them and the last two digits. Cool!

Another bit more sophisticated approach is to use an embedded TEXT function to return a formatted string, and then use TIMEVALUE as we did in the previous example:

=TIMEVALUE( TEXT(A1, “00\:00” ))

Whichever method you use, you will, of course, want to be sure and Format your result cell to Time. For instance the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting.

You may not need to do this type of conversion soon, but when you do, You’ll Stand Out from the Rank and File!

Standard Deviation

When it comes to common statistical functions, Standard Deviation is perhaps the most overlooked and underused. This has always struck me as being unfortunate, since with very little imagination, it can be easily used and adopted to a wide range of everyday business purposes.

For those of us who have not been in a math class in a while, Standard Deviation is simply, (and I do mean, “Simply”), is a measure of how widely values are dispersed from the average value.

For instance, let’s say that you oversee a Call Center and you would like to use Excel to monitor a new metric regarding talk times. Suppose that you already track weekly results and know that your company’s representatives can provide your customers good service in a Mean Average of 6-8 minutes. When it comes to Standard Deviation of your Week-over-Week monitoring, you would undoubtedly prefer to see Smaller values versus Larger values (tighter grouping around the average times).

So how can you use Excel to track this useful statistic? If you are still using Excel 2003 or Excel 2007, using the old STDEVP is your Go-To function. This assumes that you are using the Entire Population of data (ergo, the “P”). If you are using just a Sample of your data, then you will want to use STDEV.

This was made more intuitive in Excel 2010 and Excel 2013, where the function for finding standard deviation for the Population is STDEV.P and the Sample is STDEV.S. (Hooray for Microsoft for continuing to make these sorts of refinements.)

Rather obviously, this type of information is Most Effectively Illustrated quite effortlessly with a Line Chart or a Bar Chart. You can then see at a glance whether talk times are under control in this regard.

This is, of course, a mere example of the virtually countless ways of using Standard Deviation in your Excel reports. Just another way that Excel can improve our business lives (which, as Martha Stewart would say, “Is a Good Thing”.)

Calculating Workdays

Do you ever find yourself Counting Workdays until your next vacation, holiday, or other event? If you are like my sister who is retiring in a few weeks, the answer may be a resounding “Yes!”

If you have a set of dates in Excel and you want to calculate the number of Business Days, (excluding weekends and holidays), you can easily do this using the NETWORKDAYS function. The syntax is as follows:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

If you are working with an older version of Excel (prior to Excel 2007), you should note that the NETWORKDAYS was available only after you installed the Analysis ToolPak add-in.

Also, starting with Excel version 2010, if you wish to calculate the workdays using something other than the standard weekend days of Saturday and Sunday, you can use the new NETWORKDAYS.INTL. With this new function, you can customize your weekend days as simply as your holidays.

But let’s suppose you have a start date for Project and you know how many workdays it will (theoretically, at least) take to complete it. How do you calculate the Date that it will be done? Here is where the WORKDAY function comes into play. Using the following syntax, you can easily calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

If you wish to ascertain that the “StartDate” is interpreted correctly by Excel, (this is a recommended Best Practice), you can use the DATE function in its place, and alleviate any concern in this regard.

Calculating Workdays: As with most anything we encounter, when you use the right tools, it’s like being on vacation…