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!

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!

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. 

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:

=PROPER(A2) 

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.