Wednesday, January 11, 2017

More on Dates…

You’ll notice that the title of this week’s blog is “More on Dates”, not “Moron Dates” (a phrase I have overheard one of my more colorful colleagues use on an occasion…).

The fact is that what may Look LikeDate, may not “Play Nice in the Sandbox” with other dates that you have in your workbook. 

Example: Let’s say, for instance, that you have inherited an Excel workbook created by some Genius who no longer works in your company (I’m sure that his high IQ has led him to bigger and better things – cough, cough…).  Now you (a true Genius) wants to perform some Analysis that save the company countless hours and expense. The problem that this now-absent bright fellow has not documented his work, and unless you can Rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you Cannot Rely on the efficacy of your results.

So what is a well-meaning Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your upset stomach, relieve that itch on your back that you can’t quite reach, cure that nagging doubt that you are being watched (you are, you know…), and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, maybe I’m being a Snake Oil Salesman with some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert anything that looks-like-a-date into the standard Excel serial number, and you can then format it as you wish.  Formatting is, of course, rudimentary…

How Totally Cool is that! Mismatched dates can cause you a world of grief, but this simple DATEVALUE trick can save the day

One Note of Caution to Apple Users: For you MacBook Users out there, (I use Excel on one occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (don’t ask me why…). 

Tuesday, January 3, 2017

Don’t Be Duped by Duplicates

The old adage, GIGO (Garbage In, Garbage Out) is a common premise to Good Database Management. Along with GIGO, it is also important to minimize or eliminate Duplicate Data in your records, as it can cause havoc when extracting information.

A Duplicates Issue can arise in many instances, and it is almost never intentional: Combining of departmental databases, updating data, common entry errors, and others.  Ever since Excel 2007, there are ways to Quickly Remove all of you duplicate data.

How to Remove Duplicates:
These days, it is amusingly easy to remove your duplicate records, and to do so with a Bit of Discretion:  

1.  Select the entire database (Caution: Do not select any self-generating key fields)
2.  Go to the Data Tools group on the Data tab, and click Remove Duplicates
3.  Select your database or table and click OK
4.  A Remove Duplicate information box will pop up (as shown below) giving you options as to how you want Excel to identify “Duplicates

5.  Using our example as a reference, you can then choose to eliminate all duplicates by Month, Rep, and/or Sales

 When you first consider the task of Removing Duplicates to maintain the Integrity of your data, it may seem quite challenging. As with nearly all things Excel, however, once you know how to do it, it seems so Simple, you may actually Laugh…

Wednesday, December 28, 2016

No IFs About It…

There are no “IFs” about it, the “IF” Functions are some of the most useful and accessible utensils in Excel. In fact, the knowledge of IF functions is so critical to any Excel users set of tools that it bears an occasional review.

There are, of course, several types of IF to choose from in Excel. Each of these powerful gizmos can aid you in making sense of your data. Here are my current 5 Favorite Ifs:

1.  COUNTIF: =COUNTIF(Range, Criteria) 
For the Criteria, you can specify a cell, a simple number such as 10, or use a greater-than or less-than criteria such as “>50”. Be sure to use the quotation marks if doing a great-than or less-than analysis. Alternatively, you can refer to a cell that contains your criteria number (No quotation marks needed with that approach).

2.  SUMIF: =SUMIF(Range, Criteria, SumRange) 
The Criteria is the same as in COUNTIF, but you have the option of applying it to a “SumRange”. These are the actual cells to add if the cells in the Range match the criteria. If SumRange is omitted, the cells in Range are evaluated by criteria and added accordingly.

3.  SUMIFS: =SUMIFS(Sum_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], ...)
First introduced in Excel 2007, but often overlooked, this function is similar to SUMIF, but more powerful as you can add multiple criteria.

4.  IF: =IF(Condition, Value If True, Value If False) 
This function is extremely valuable as a stand-alone or in conjunction with other “nested” functions. You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. IF(A1>70, “Pass”, “Fail”). Just be sure to use quotation marks if inserting actual words. 

5.   IFERROR: =IFERROR(Value, Value If Error) 
Introduced for the first time in Excel 2007, this combines the IF and ISERROR functions, and essentially gives you greater control with potential Error Messages. Rather than just accepting the sometimes cryptic and less-than-helpful default error messages that Excel dishes out, you can Customize your error messages for more clarity.

 No IFs about it, the IF functions are another great set of tools for Excel arsenal. Happy Holidays, All!

Wednesday, December 21, 2016

Keep it Safe!

You may have never done this.  In fact, the chances are very good that you haven’t.  Protecting your Excel work. Really, you say? How boring can a topic be!
You’ve never had a problem and, frankly, you can’t be bothered. Well, that may be just fine if you are the only one using your Excel masterworks, but if are sharing your work (and most of us probably are) with others there will come a time when the Others (note the ominous tone) will want to “Experiment” with your formulas and format. The construction of your workbook may have taken many hours to create, and there is the potential for substantial ruin. This does not need to be the case!

Excel has built-in Protection Tools to help assure that your Excel work is safe.

Let’s take a look at Excel 2016 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 barely warrants mentioning, but it does happen.

One Last Important Note: Protecting your worksheets is Not a way of making them 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, which is for most workplaces, good enough…

Protecting Your Worksheets.  This is a Best Practice for any Excel practitioner, and one worth your time. There will be a time when you will be glad you did this.

Merry Christmas, All!

Tuesday, December 13, 2016

Dates - Have It Your Way!

Dates can be curious animals in Excel, and for most Excel users, they bear a closer look.  We are all familiar with the mm/dd/yyyy date format, but it is not “All Things to All Users”.  It is, in fact, very convenient to use Custom Date Formats in Excel, and it is easy to set up custom formats on your worksheet. Let’s look at some Practical and Useful Date Formats.

The most common Date Formats are, of course, as follows:

  m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
  d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But let’s assume you want to create a Date Format that is Not included in the built-in list in Excel. What do you do then?  Let’s say that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps to Achieve this Format are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example above, the outcome will be a date format in the syntax: yyyy/dd/mmmm.

You may not have a current need to control your Date Formats, but if it ever arises, you now know it is easy in Excel. I am sure we all agree that Better Communication (often lacking in business) is always a worthy goal. 

Wednesday, December 7, 2016

Being Proper

Importing data, and especially names, from outside databases can initially result in less-than-ideal formats in Excel.  Having a list of names imported from a source other than Excel can result in all upper-case, all lower-case, or even a mixture of both!

Now, this is not a big deal when it comes to analyzing the data, (in fact, some nerdy sorts seem to take pride in ignoring Proper capitalization), but it lacks a professional touch that can be easily corrected.

It would be quite absurd to consider changing the capitalization of the names Manually (how ridiculous would that be…). So what do you do when you download 7,000 names, and you want to change them to the Proper case?  The PROPER function comes to the rescue!

To illustrate this, let’s say that you have imported a database of your company’s employee names, and you have put them into a list that runs from A2 to A7002. In cell B2, insert the following simple formula:


Give the handle in the lower-right corner of cell B2 a quick double-click to copy this simple formula down to the bottom of your list, and Bamm! Proper Names! 

One Final Note, if your database list contains names such as McElroy or DeLeon in it, you will probably need to change those manually. And if you have any Really Odd Names like DeLaMartre (my own…), then you will most surely need to make some adjustments by hand.  Ah, well, nothing’s perfect. You could, of course, come up with a sophisticated IF formula to handle these cases, but you may need to weigh the effort versus the results. Ah, well, nothing’s perfect.  

Wednesday, November 30, 2016

Gantt Charts

Gantt Charts have been around for a long time, and there are many software solutions available for companies of all sizes. Along with Microsoft Project, there are several specialty software solutions for producing these charts.  For many, however, good old Excel handles this task quite well.

You say you are not familiar with Gantt Charts?  Here is what they are all about.
First of all, they are a comparatively simple bar charts that illustrate and track the evolution of a project.  They allow the user to Juggle time and resources to obtain desired results. Developed by a management consultant by the name of Henry Gantt back in the early 20th century.  These robust charts clarify the start and finish dates of a project’s elements, and can be easily grasped at a quick glance (the hallmark of an effective chart). Although they were considered revolutionary at the time old Henry developed them, Gantt charts are considered mainstream today.

When used correctly and consistently (some individuals have been known to fudge with the results in the past), Gantt Charts can be invaluable tools to managers, analysts, and employees in the front lines.  Here is how to create one in Excel:

1   1. Open a New workbook in Excel (version 2013 or later is recommended)
2   2. Type in “Gantt” in the Search for online templates box
C   3. Choose the Project Planner template and click Create

The Project Planner Gantt chart template will then open, and you can customize it to suit your business needs.  Note: You can access Manage Rules under Conditional Formatting on the Home ribbon and revise the formatting and range of the chart.  I recommend that you modify the formatting, which can give you some control over the aesthetics.

With these very accessible Excel Gantt Charts, you can quickly see where each activity is according to plan.  Give it a try the next time a Big Project comes by, and enhance your Juggling skills!