Texting

Well, perhaps not exactly “Texting” but in this case of our topic today, TEXT. Yes, using Text in Excel.

Obviously, Excel is all about working with numbers. However, there are times when you want to be able to manipulate your data into specially formatted Text. One excellent solution is the TEXT function! By using specially formatted strings, TEXT allows you to specify how you display the numerical value in text with format of your choosing.

The TEXT syntax is: TEXT(value, format_string) 

Let’s look at an observable example: Inserting the format_string $#,##0 as the second part of the TEXT function provides a monetary formatting with a dollar sign and comma providing immediate recognition. 

TEXT can also be used with Concatenation (which is one of my favorite somewhat-obscure functions) to return user-friendly reports. In the illustration below, the following formula (note the space after “is”) was used in the merged cells of A6 and B6:

="The Net Profit is "&TEXT(B4," $#,##0")

 The Bottom-Line Statement in the merged cells is cleanly formatted and immediately understandable to the resulting profit. This ultimately well-defined declaration of the data may not be necessary for the tech-savvy few, but I can assure you that it will be well-received by those non-techy types!

Named Range Shortcuts

As I have noted on several occasions, Naming Your Ranges is most assuredly a Best Practice when creating or maintaining a database in Excel. Employing Named Ranges is not very difficult, but using some Shortcuts is, of course, well, quicker and easier. Let’s look at Five Powerful Shortcuts that will be sure to elevate your reputation as The Excel Guru:

1. Name a Single Range
Select the range, and then click in the Name Box (far left on the formula bar) and type a One-Word Name. Piece of cake…

2. Name All Your Ranges at Once
Select your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. Easy…

3. Navigate to a Named Range
Click F5 (Great shortcut for selecting Named Ranges!)

4. Edit a Named Range
To delete or edit a named range click Ctrl+F3. Couldn’t be simpler…

5. Display the Paste Names Dialog Box for Use in Formulas
Click F3 and use your arrow keypad to select (Extremely helpful when creating complex formulas)

Utilizing Named Ranges is an effortless way to expand your capabilities as an Excel user – Using Shortcuts with Named Ranges makes it easier.

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 Like a Date, 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…). 

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…

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.