Duplicate Data Revisited

The proper handling of Duplicate Data has been a traditionally troublesome issue for a great many Excel users.  The Duplicates Issue can arise for several reasons:  Combining of similar databases, updating data, entry errors, etc.

While there are ways to Quickly Remove all of you duplicate data, it may be a bit rash to do so without first getting an idea of what is being obliterated.

For example, let's say you are working with Combined and Blended Sales Department data that displays your company’s reps results by individual and month.  Using Conditional Formatting, you can quickly see which records are true duplicates.

Removing Duplicates:

Since the advent of Excel 2007 (and in subsequent versions, of course), it has become laughably easy to remove your duplicate records, and to do so with a bit of Finesse:   

1.  Select the entire database (be careful not to 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 (example below) giving you options as to how you want Excel to identify “Duplicates”
5.  Depending on whether you want to eliminate all duplicates by Month, Rep, and/or Sales (in this example), you will then indicate which Columns to choose with the checkboxes (Note: Choosing all of the columns assures an exact duplicate by all of your data criteria).

As with anything in life, (Excel or otherwise), once you know how to do something, you will likely wonder why it ever was a problem to begin with.  Once you know how, Removing Duplicates is a Snap!  Give it a try…

 

Wildcats, I mean Wildcards!

There are so many cool tricks in Excel.  A person could research and talk about them for years.  The use of functions is, of course, the coolest trick of all in Excel.  To make functions even better, however, you can use Wildcards.

The ability to use Wildcards is a very effective feature in Excel.  Several functions can use these handy critters to better filter the results you are attempting to get.

The following is a list of the more common Excel functions that can make use of Wildcards:

·        AVERAGEIF

      ·        AVERAGEIFS
      ·        COUNTIF
      ·        COUNTIFS
      ·        MATCH
      ·        SUMIF
      ·        SUMIFS
      ·        VLOOKUP

There are 3 different Wildcard Characters in Excel.  Though that may seem to be a paltry few, you may be Amazed at what you can do with them.

The summary below offers an explanation and examples of how these characters can be used in the functions noted above.

Wildcard Character	To
Question Mark (?)	Find any single character (letter, number, etc), such as “B?nk” finds Bank, Bonk, or Bunk
Asterisk (*)	Find any number of characters (once again, letters, numbers, etc), such as “Post*” finds Postage, Postpone, Postcard, Postulate, etc
Tilde (~) with another Character	Insert a literal question mark (?), asterisk (*), or tilde (~) such as “Jameson~?” finds Jameson?, “Jameson~*” finds Jameson*, etc

As you further your mastery of Excel formulas, keep in mind the power of Wildcards.  They can give you a great deal of help as you explore the Wild*!

Sparklines!

Sparklines are amazing little appliances that were first introduced in Excel 2010.  They are miniature charts that fit into a single cell in your worksheet, providing you with a visual representation of your data without having to resort to a full-blown chart.

Interestingly, a Sparkline is a tiny chart that resides in the background of a cell, not unlike formatting.  It can overlay (or underlay) other contents of a cell, but are typically used in a separate cell adjacent to your data.

The advantages of Sparklines are many.  They can Simplify your visual representations of your data, and can be done so with just a couple of clicks!  Effect and Simple (and that’s what we all want…).  They are particularly successful in creating Mini-Trendlines such as those which are desirable in sales analysis.  To reinforce this point, check out the illustration below.  At a glance, it is easy to tell who is doing well, and who could use improvement.

Create Sparklines

1.      Select a group of cells (or single cell) in which you want to insert your Sparklines.

2.      Find the Sparklines group on the Insert ribbon, and click the type of Sparkline that you want to create, (Line, Column, or Win/Loss).

3.      In the Data box, select and insert the range of the cells that contain the data on which you want to create your sparklines.

Badda-Bing!  Simple as that!  Want to add a bit of Illustrative Jazz to your Excel reports?  Think Sparklines!

Buying a Tesla

I am sure there are many of us who would like to own one of the new status-symbol-laden Tesla automobiles (I know I would!).  Although they do not have a built-in Excel application, the all-electric, futuristic Teslas come with a host of high-tech features.

So, how do you know if this is a car you can afford?  Well, if you have your tablet or a laptop along with you while you are shopping, you can calculate the Monthly Payments using a handy-dandy Excel function.

The PMT() Function - Here is How it works:

Coming up with this information in Excel could hardly be easier.  The PMT() function can calculate the monthly repayment using the following syntax.  Note that a minus sign has been put in front of the PMT function so that it returns a positive payment amount (which is more intuitive for most folks…), and since we are looking for Monthly Payments, the first argument is divided by 12.  The rest is a piece of cake:

-PMT(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

To illustrate this, let’s say you traded in your Jaguar (we might as well dream…), and your bottom-line amount of your loan is going to be $51,000.  If your annual interest rate is 3.5%, and you are financing for 60 months, the resulting formula would look like the following:

=-PMT(3.5%/12, 60, $51,000)

Having constructed your formula with very little effort, you hit Enter and, Bamm, you have your monthly payment (In this case, $927.78).   

Too much money?  Don’t have a Jag to trade in?  If you don’t like the results, play around with the interest rate, the length of the loan, or maybe you’d want to check out a Chevy Volt.  The bottom line is that it is all so simple when you use Excel.  Coolness…

To Error is Human…

Mistakes happen in Excel.  There is no sure cure for this.  “To Error” is indeed “Human”.  Excel is designed to let us mere mortals know when you make a mistake by giving us an Error Message.  This can be helpful, but it can also be rather annoying!

What, for instance, do all of these would-be-helpful Error Messages mean?!?  And What can you do about them?  As we did a couple of years ago, let’s look at a list of Error Messages along with some brief explanations:

• #DIV/0! - You divided by 0 or by an empty cell (We all learned in algebra that you can’t do that…)
• #N/A - A return value of the function is not available (Yeah, you probably messed up the formula)
• #NAME? - Well, you might have used an Undefined range or cell name (easy to correct…).
• #NULL! - You specified an intersection of two areas that do not intersect (Weird, eh?).
• #NUM! - There is a problem with a number in a formula (i.e. Is it really a number?).
• #REF! - An invalid cell reference is mentioned in a function (reasonably self-explanatory…).
• #VALUE! - The wrong type of argument or operand is used in the formula (Okay, fine!)

So, the foregoing covers “What do they mean”.  Now let’s look at “What can you do” by examining a few useful Handlers that can be applied in your worksheet:

1.   ISERR - Any error value except #N/A
2.   ISERROR - Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3.   IFERROR – Introduced in Excel 2007, this combines the IF and ISERROR functions

Let’s use the IFERROR in an example. Suppose you have two columns of data which you are simply dividing in a third column. If you Divide by 0 or Divide by a Blank Cell, you will obviously get an Error Message.

 If, however, you want the message to be Clear and Concise to the potential user, you can use IFERROR to define a Custom Response such as “Mistake in Calculation” or “Problem with 2^nd Value”, or something more apropos that you can invent for your own special circumstances. In the example shown, the formula would be:

=IFERROR (Column_1 / Column_2, “Error in Calculation”)

 

By creating Custom error responses, rather than accepting sometimes cryptic stock Error Messages, you can Humanize your worksheets.  To Error, after all, is Human…