Handling Duplicate Data

Handling Duplicate Data is well-known to be a Vexing Problem for many Excel users. 

There are tools within Excel that simply delete all duplicate data, but there are times when it is more advisable to Identify these Duplicate Values prior to eliminating them.

For instance, let's say you are working with Combined and Blended human resources data that lists employees with their home addresses. If the information in the blended data includes Duplicates of employees who have had updates, and shows different addresses for these duplicates, it would be good to have an Expedient Way of doing identifying them before taking action.

Conditional Formatting can offer an excellent solution:

1.   Select the range (e.g. A1:A98).

2.   Choose Format / Conditional Formatting to display the CF dialog box

3.   Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$A$98,A1)>1 in the second box

4.   Click the Format button to bring up the Format Cells dialog box.

5.   Select the Patterns tab, and choose a background color

6.   Click OK twice to return to your worksheet

Bamm! If the range contains any duplicate entries, they will be highlighted with the background color you chose previously!

Removing Duplicates in databases is one of the most infamous topics for many Excel professionals. Being able to do this, enables a user to Combine Databases with repetitive data into a properly structured format.

With the last three versions of Excel (2007, 2010, and 2013), this is Remarkably Easy. Here is all you have to do to complete this simple task:

1.  Select the entire database (be careful not to select any self-generating key fields)

2.  Go to the Data tab in the Data Tools group and click Remove Duplicates

3.  Select the column(s) on which you want to base your removal and click OK

4.  A message will pop up telling you how many (if any) values were found and removed

At first glance Removing Duplicates to maintain the Integrity of your data may seem like a challenging task. As is the case with nearly everything in Excel, however, once you know how to do it, it seems so Comparatively Simple, you may wonder why you ever considered it a problem…

Why to Use AutoCorrect...

“I Haven’t Got Time to Save Time”. You may not have ever heard or said these words, but the fact is that many people conduct their professional lives as if this phrase were the mantra to their philosophy.

Being an Excel Enthusiast, you naturally believe there are easier ways to do things, and that brings to today’s subject, AutoCorrect!

If you routinely need to type out a Company Name or any other so-called “Boilerplate” text in Either Excel or Word, you can Save Time by setting up your AutoCorrect option to Automatically change the text you are writing.

Of course, AutoCorrect is initially set up with a list of typical misspellings and symbols, but this is for the everyday masses, not Excel Gurus such as yourself! Let’s say you work for National Public Radio, and the legal department wants the entire name of your service Spelled Out each time it is entered into a worksheet.

While this may seem like a chore, AutoCorrect can make this is as easy as typing NPR.

Here is How You can Set this Up:

1. Go to FILE and select Options from the bottom of the column

2. Choose Proofing and click on the AutoCorrect Options button

4. In the Replace box, type NPR

5. In the With box, type National Public Radio

6. Click Add and then OK That’s all there is to it! But wait! There is also a World of Opportunities to have a little Fun as well!

Let’s say you have a friend named Gary at work (any name will do). While he is away from his computer, go into AutoCorrect and enter Gary in the Replace box and Geek Boy in the With box.

Do this in Word also, and he will be stunned every time he types his name! This is great adolescent fun, (but it helps if you know Gary has a sense-of-humor…). Just be sure you have him do this in your presence, so he doesn’t unnecessarily embarrass himself (and you).

Using AutoCorrect can better your life. Unless, of course, You don’t have Time to Save Time…

Embedding Excel in Other Programs

We all agree that Excel is the Quintessential Tool for crunching numbers and developing corporate information. There are times, however, when you may want to Upgrade the way you present your findings to the stakeholders of the data.

If, for instance, you are making a presentation with PowerPoint, it is typically much more effective if you insert your Excel information, (a chart, for instance), rather than having to switch programs midstream.

To do this, simply open a PowerPoint presentation (or Word document if that suits your purposes) to add the Excel worksheet, then Insert Object. Choose Microsoft Excel Chart from the Object Type and click OK.

 

You will instantly have a generic Excel chart inserted into your presentation or document. From there, it is equally easy:

• Just right-click the chart and choose Edit Data from the dropdown list. You can import it from a worksheet you have already created or, if you choose, create your own data on the spot.

• Changing the Chart Type can also be accomplished from the same dropdown, as can formatting (Format Chart Area…)

• If you want to save the chart image as a static image, you can choose Save as Picture and save to a file, locking in all that you have done for possible future use.

By using these tools, you can enhance that overt Professionalism in your presentations and documents. Very cool!

Excel Wizardry

As with so many things in life, what seems like “Wizardry” in Excel is really a matter of knowing some Simple, albeit powerful, Tricks.

#1 - Multiple Lines of Text within a Cell
Have you ever wanted to add multiple lines of text to an individual cell? How can this be done, you ask? The answer is so easy, you’ll laugh.

When you want to insert a line break within a cell to break up your text for readability, just hold down Alt and hit Enter. You are also turning on Wrap Text when you do this. Cool!

#2 - Delete Blank Rows
If you have blank rows of cells within your database or table, it can potentially cause issues when deriving information (and who needs that…). Select a column, press F5, click on Special, select the Blanks option, and click OK.

Now that you have the blanks targeted, click over to Excel’s Home tab and go to the Cells group. Select Delete Sheet Rows and Bamm, no more blanks!

#3 – When Numbers are Not Numbers
Excel will occasionally store numbers like a “0” as Text instead of a numeral, (often when importing from another program). This can be problematic, especially if you have a sizeable database. The solution, once again, is so easy you’ll laugh!

  First, simply type a “1” into an empty cell outside of you data. Select and copy it to your clipboard, and then select the range of numbers you wish to fix. Right-click and choose Paste Special, select Multiply, and click OK. Problem Solved!

Simple tricks that look like Wizardry. Everything is simple when you know how!